Create A Form in Access that controls the output of another form

Say you have txtCustName, txtCity and few more on your 1st form.  You may type values in it or fill them by via a combo box.  Later on, you could even do partial searches if you choose to use text boxes.
----------
Open your second form in design view and under [CustName] field, in the criteria box have:

IFF(fnCustName()="<All>",[CustName],fnCustName())

Above code would allow you to enter all four text boxes or leave some blank.  When it is left blank, function fnCustName(0 is to return "<All>" string therefore field value [CustName] will be taken.  What this means, the criteria gets ignored as if it wasn't there at first place.  However, it the value of function fnCustName() was some valid string, it will filter the record source accordingly.

For each text box (criteria) you will have a function.  These functions need to be placed in a Standard module, under module tab.  Please do one criteria at a time.  After you handled 1st one, add your second one.

Public Function fnCustName()As String  'might be different for other criteria
Dim strTemp As String
strTemp = Nz(Forms!Form1!txtCustName,"<All>")
Msgbox strTemp & " <-- remove this message after a test"
fnCustName=strTemp
End Function

mike

For partial search, add a new question later on.

I read somewhere that this:

Dim Val, Val1, Val2, Val3 As String

creates val, val1 and val2 as variants not strings....
only val3 is a string.

so
dim val1 as string
dim val2 as string
dim val as string is required if you want them all to be strings.

in this case i doubt it makes a diference but i am feeling nosey and pedantic today.

Flyster

I tried using the code you recommended, but the event doesn't trigger.

Here is the code I input into the procedure.

Private Sub Combo7_AfterUpdate()
Dim Val, Val1, Val2, Val3 As String
Val = Me.Combo7.Value
Val1 = "Fundraising"
Val2 = "Marketing"
Val3 = "Student Health Center"
Val4 = "Clinical Training"

    Select Case Val    ' Evaluate Number.
      Case Val1 ' Opens first form
        DoCmd.OpenForm "Impact on Fundraising and Marketing", acNormal
     
      Case Val2 ' Opens second form
        DoCmd.OpenForm "Impact on Fundraising and Marketing", acNormal
     
     Case Val3 ' Opens third form
        DoCmd.OpenForm "Impact on Student Health Centers", acNormal
     
     Case Val4    ' Opens forth form
        DoCmd.OpenForm "Impact on Student Health Centers", acNormal
    End Select

End Sub

Please let me know your thoughts. Thanks! David

Dave, the code looks good to me. I tried it on my machine and it worked fine. Are you sure you have the form names entered exactly? Here's something else you can try. Open the form in design view. Do a Alt+F11. Go to the line that has “Private Sub Combo7_AfterUpdate()” and left click in the column just left of “Private”. This should put a dot in the column and highlight the line. Now open your form and make a selection. The visual basic editor should open and the top line will be highlighted yellow. Press the F8 key until the highlight reaches “Select Case Val”. Do a mouseover on “Val” and see what the value is. (A smart tag should open up when you do this) It should match EXACTLY to Val 1,2,3 or 4. If not. Nothing will happen.  Note: When your done, be sure to remove the "dot", or the editor will open every time you select the combo box.

i'd be interested in flyster responding to my coment about multiple declarations as variants until explicit declaration,

but my contribution does not, in my opinion, merit any points here.