Opening a Form with a Filter

[spherey]

Hi everyone -

I have a command button on one form which opens another form. But, I want to modify the button's on click event so that it not only opens the form, but it also filters one of the form's fields ("Manager&quot for a specific name, and then opens the form with only the matching records displayed. Does anyone know any good code for this?

Thanks,

Spherey

 

[jdegeorge]

spherey

Do you want to open the 2nd form based on a value of the 1st form? If so, modify the query for the 2nd form where the MANAGER field's criteria is [forms]![1stForm]![Manager]. Replace "1stForm" with the first form's name and "Manager" with the name of the field you want to use as the filter for the 2nd form. Hope this helps.

Jim DeGeorge

 

[spherey]

I don't think it would be opening the 2nd form based on a value of the 1st form, per se. The 1st form would be a switchboard form, and each of its buttons would open the same form (the 2nd form) with a different filter on the "Manager" field. So the "Bill" button on the switchboard form would filter the 2nd form for all entries listing "Bill" in the "Manager" field, whereas the "Ann" field would filter the same field in the same form for a different result, and display only those records with matching fields, ie. "Ann." Any ideas as to how to set this one up?
Thanks for your help,

Spherey

 

[spherey]

Hey again,

This is saying the same thing, but maybe it's a little clearer with an example of the code.
Right now, there's a box on the 1st form for the "Manager" field, and a command button that opens the 2nd form based on the value of that field. And that works, and if it were up to me I'd leave it that way. But what the users want is to have the box listing the manager field not be present on the form, and the command button to instead open the form automatically (this would mean that instead of one field with many choices and one action button, we'd have to have many buttons, each with only one choice) with the specific manager's name.
The code in the command button's on click event right now is:

Private Sub Open_Form_Click()
On Error GoTo Err_Open_Form_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Client Database Management Form"
    
    stLinkCriteria = "[Manager]=" & "'" & Me![Manager] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Form_Click:
    Exit Sub

Err_Open_Form_Click:
    MsgBox Err.Description
    Resume Exit_Open_Form_Click
    
End Sub

And I'm pretty sure that this is the line I'll need to modify:

stLinkCriteria = "[Manager]=" & "'" & Me![Manager] & "'"

How can I code it so that instead of matching what's present in a given field on the 1st form with the same value in the same field on the 2nd form, it uses a predetermined value specified in the code? "Bill" for example, instead of [Manager].

Thanks,

Spherey

 

[jdegeorge]

Spherey

You want a non-fixed number of buttons on the form, each representing a manager from your drop down list rather than having the drop down list on the form. And, upon clicking a manager's button, let's say "Bill," you want to have "Bill's" information loaded to the 2nd form.

First, I would not recommend that you do this because I can't think of a way for you to automate the varying number of buttons. What happens when a manager leaves or a new one is hired? You may have 4 or 5 buttons now, but tomorrow you could have 3 or 6. You'll forever be modifying this database.

Your code is what would need to be behind EVERY button, even if it is very generic. What you're trying to do is apply a filter to your other form's query. I think you have 1 too many commas. Try this:

DoCmd.OpenForm stDocName, , stLinkCriteria

To save coding, maybe you can have radio buttons or check boxes, that way you could possibly use your code once and have it based on the value of the selected radio button. Hope this helps.

Jim DeGeorge

 

[spherey]

I heartily agree. I think that the series of command buttons is a bad idea, too, and the way I have it now is much better and more efficient. Or the radio button idea - that's a better one, too.
Thankfully, after showing the end users an example of what I've built so far (using a combo box and a single command button), I've gotten them to agree with me. Simplicity prevails. Hurrah!

Thanks,

Spherey

 

[jdegeorge]

Spherey

"A picture is worth a thousand words..." is true. Having a user see something makes it easier to explain why something else might not be the right solution. Glad to hear it worked.

Jim DeGeorge