Open query if condition is true

[jamphan]

I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.

 

[WayneRyan]

jam,

This is the simplest method. At least it should get us started.

If DCount("[AccountNumber]", "Table1", "[AccountNumber] = " & Me.AccountNumber) > 0 Then
   DoCmd.OpenQuery "Table1Query"
End If
'
If DCount("[AccountNumber]", "Table2", "[AccountNumber] = " & Me.AccountNumber) > 0 Then
   DoCmd.OpenQuery "Table2Query"
End If
'
If DCount("[AccountNumber]", "Table3", "[AccountNumber] = " & Me.AccountNumber) > 0 Then
   DoCmd.OpenQuery "Table3Query"
End If

Wayne

 

[jamphan]

That is almost working. How can I include a message box that says account can't be found if it doesn't meet any criteria?

 

[CraigDolphin]

Maybe try this...

If DCount("[AccountNumber]", "Table1", "[AccountNumber] = Me.AccountNumber) > 0 Then
   DoCmd.OpenQuery "Table1Query"
Else
     If DCount("[AccountNumber]", "Table2", "[AccountNumber] = Me.AccountNumber) > 0 Then
     DoCmd.OpenQuery "Table2Query"
     Else     
          If DCount("[AccountNumber]", "Table3", "[AccountNumber] = Me.AccountNumber) > 0 Then
          DoCmd.OpenQuery "Table3Query"
          Else
          MsgBox "Account Not Found", vbOkOnly
          End If
     End If
End If
'

 

[jamphan]

Ok, well your code didn't exactly work so I made some modifications and combined yours with mine. It keeps going to the message box but the account I am using should come up. Any ideas?

Private Sub Command2_Click()

If DLookup("[Acct]", "qryTest", "[Acct]= '& Forms![Form]![Account]'") = True Then
DoCmd.OpenQuery "qryTest"
Else
If DLookup("[Acct]", "qryTest2", "[Acct]=' & Forms![Form]![Account]'") = True Then
DoCmd.OpenQuery "qryTest2"
Else
MsgBox "The Account Number was not found"
End If
End If

End Sub

 

[KeithG]

If DLookup("[Acct]", "qryTest", "[Acct]= '& Forms![Form]![Account]'") = True Then
DoCmd.OpenQuery "qryTest"
Else
If DLookup("[Acct]", "qryTest2", "[Acct]=' & Forms![Form]![Account]'") = True Then
DoCmd.OpenQuery "qryTest2"
Else
MsgBox "The Account Number was not found"
End If
End If

Use this instead

If isnull(DLookup("[Acct]", "qryTest", "[Acct]= '& Forms![Form]![Account]'")) = false Then
DoCmd.OpenQuery "qryTest"
Else
If DLookup("[Acct]", "qryTest2", "[Acct]=' & Forms![Form]![Account]'") = True Then
DoCmd.OpenQuery "qryTest2"
Else
MsgBox "The Account Number was not found"
End If
End If

 

[jamphan]

Still just gives the message box.

 

[KeithG]

You are correct, I took another look and found another error

If DLookup("[Acct]", "qryTest2", "[Acct]=' & Forms![Form]![Account]'") = True

try using

If DLookup("[Acct]", "qryTest2", "[Acct]='" & Forms![Form]![Account] & "'") = True