Query Decisioning based on Form Input

jamphan

Registered User.
Local time
Today, 06:00
Joined
Dec 28, 2004
Messages
143
I have a database that has several tables, each table has account numbers in
them. I also have queries set for each table to bring in desired
information. Is it possible to set up a form so the user can input an
account number and depending on which table the account number is in, that
query will run?
 
you could use the DlookUp function to check which table the ID is in then run the query.
 
Would the dlookup be put in an event code?
 
yeah probably on a buttons On_Click event.
 
For a new user to dlookup, what would that code look like if I wanted it to look in a form field and look through 3 queries for the result?
 
Okay, let say for example I want to find out if there is a record for Jim Black in qryGuest. With Name being the field Jim Black would be stored in

DlookUp("Name","qryGuest","Name= 'Jim Black'")
 
Got that. Will that run the query automatically when the button is pressed. I would have the query linked to a report so I would want all information on the corresponding report to be displayed when the match is found. Any help on that?
 
No, what are your query names and report names? I will post an example that you can copy to your database.
 
Query names are Test1, Test2, Test3

Report names are Report1, Report2, Report3

(The entire database is in a test mode if you can't tell)
 
Something like below should work when put in the On_Click event of the forms button.

PHP:
If IsNull(DLookup("[FieldName]", "test1", "[FieldName]=" & Forms![FormName]![TextBoxName])) = True Then
    If IsNull(DLookup("[FieldName]", "test2", "[FieldName]=" & Forms![FormName]![TextBoxName])) = True Then
        If IsNull(DLookup("[FieldName]", "test3", "[FieldName]=" & Forms![FormName]![TextBoxName])) = False Then
            DoCmd.OpenReport "Report3", acNormal
        Else
            MsgBox "The Account Number was not found"
        End If
    Else
        DoCmd.OpenReport "Report2", acNormal
    End If
Else
    DoCmd.OpenReport "Report1", acNormal
End If

You will have to replace FormName and TextBoxName with valid values from the form
 
I am getting a run time error 3464, Data Type mismatch in criteria expression. Any ideas on this?
 
Also, when I debug it goes to the first If statement.
 

Users who are viewing this thread

Back
Top Bottom