Open Form Based on Multipule Criteria

[jabrony76]

Hi all,

I am currently working to set up a feature in my database that will allow users to look up doctors based on specific criteria such as "City", "State", "Zip", "Gender", "Age of Patient", etc...

I would like to create a "search form" that will open up a different "results" form based on entries in the search form. On my search form I have unbound text boxed and check boxes. I also have a button which would open the "results" form based on the criteria in the unbound controls. Kick is - I would like to be able to search by a combination of the boxes as well (i.e. search for a female doctor in denver that sees 1-6 year olds)

I have posted the code below that is the event procedure on the command button that only searches by city but I cant figure out how to make it search for any combination of the entries. As you might be able to tell, I'm not that good a code so any help would be greatly appreciated!

Thanks Much,
Andy

-----------------------------------------------------

Private Sub Command53_Click()
On Error GoTo Err_Command53_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPAPResultsForm"

stLinkCriteria = "[City]=" & "'" & Me![City] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command53_Click:
Exit Sub

Err_Command53_Click:
MsgBox Err.Description
Resume Exit_Command53_Click

End Sub

 

[Databaseguy]

I use a parameter query instead. The parameter query populates a sub form in the form.
The top part of the form just has unbound text boxes.


Field1: Like "*" & [forms].[yourform].[fieldname1] & "*" AND Not Like "XXX"
Field2: Like "*" & [forms].[yourform].[fieldname2] & "*"
etc

What's with the "XXX" ??? Well, in my field, I specify an expression: =IIf([YourField] is null, "XXX", [YourField]).This prevents the wildcard from bringing up every record in the universe.

On the on open event of the form, I'll set all the textboxes to an empty string.

Private Sun On_Open()
me.txtText1 = ""
me.txtText2 = ""
(Note, don't specify the .text property, or Access will complain that it can't set the focus)
End Sub

The last step is your command button:

Private Sub cmdFind on_Click()
me.refresh 'simple huh ?
End Sub

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[jabrony76]

Tyrone,
Thanks for the great idea. I am having some problems with it though.
Where do you put the IIF (Field)is null, "XXX",[Field]) expression? Is it in the query somewhere? In the subform?

Also...

Field1: Like "*" & [forms].[yourform].[fieldname1] & "*" AND Not Like "XXX"

Field2: Like "*" & [forms].[yourform].[fieldname2] & "*" AND Not Like "XXX"

do these parameters go in diagonal entry boxes in the parameter query to make them connect with "OR"?

thanks again,
Andy

 

[ZaZa]

Hi, I'm a beginner to access vba myself, but since you want the query to search by more than one criteria, try adding on the criteria to each other like this:

stLinkCriteria = "[City]=" & "'" & Me![City] & "' And [State]= "& "'" & Me![State] & "'And [Zip]= "&" '" & Me![Zip] & "'And ........"

The .......means to continue on like that for [Gender] and [Age].

You could also try using the OR statement instead of AND so as to not restrict your search too much.

Hope it works out for you.

ZaZa