Hello everyone
Im having an issue with a search form funtion. What im trying to do is i have a form with a few text boxes on it. there are 2 main text boxes i guess is what you can call them that function as the main search feature. One is called Social Security and the other one is called Alt ID. Now what this database is used for is to update and add address. What i would like the form to do is if i put in a social scecurity number and the person is not in the database yet have a error message pop up and say so then bring up a add insured form. Some thing go for the Alt ID. The code i am using now is this
It doesnt work I end up with this error message
Snytax erro (missing operator) in query expression '[social security] like '*111-11-1111*' and [alt id] ='.
Now if i run this code here
It works perfectly. Just only does the SS number. How can I get this to where
if i put in the ss it will work OR the Alt ID it will work. I think the biggest thing that i am having issues with is an OR statement. Basiclly we can never ever have both an alt id and a ss number to the same person so both boxes have to funtion independently. I Hope i explain clearly if not please let me know. Any help with this would be great. This thing has been driving me nut to where i spent close to 120 buck on VBA book and still cant figure it out. Thanks
Im having an issue with a search form funtion. What im trying to do is i have a form with a few text boxes on it. there are 2 main text boxes i guess is what you can call them that function as the main search feature. One is called Social Security and the other one is called Alt ID. Now what this database is used for is to update and add address. What i would like the form to do is if i put in a social scecurity number and the person is not in the database yet have a error message pop up and say so then bring up a add insured form. Some thing go for the Alt ID. The code i am using now is this
Code:
Private Sub SubmitSearchButton_Enter()
On Error GoTo Err_Command11_Click
Forms![Search].Recalc
strCriteria1 = "[Social Security] Like '*" & [Forms]![Search]![Social Security] & "*' AND [Alt ID]=" & [Forms]![Search]![Alt ID].Value
strCriteria2 = "[Social Security] Like '*" & [Forms]![Search]![Social Security] & "*' AND [Social Security]=" & [Forms]![Search]![Social Security].Value
If DCount("*", "Information", strCriteria1) + DCount("*", "Information", strCriteria2) > 0 Then
DoCmd.OpenForm "Find Record", acFormDS
Else
MsgBox "Does not exists"
DoCmd.OpenForm "new"
End If
Exit_Command11_Click:
Exit Sub
Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub
Snytax erro (missing operator) in query expression '[social security] like '*111-11-1111*' and [alt id] ='.
Now if i run this code here
Code:
Private Sub SubmitSearchButton_Enter()
On Error GoTo Err_Command11_Click
Forms![Search].Recalc
If DCount("*", "Information", "Social Security = '*" & [Forms]![Search]![Social Security] & "*'") > 0 Then
DoCmd.OpenForm "Find Record", acFormDS
Else
MsgBox "Does not exists"
docmd.openform "new"
End If
Exit_Command11_Click:
Exit Sub
Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub
if i put in the ss it will work OR the Alt ID it will work. I think the biggest thing that i am having issues with is an OR statement. Basiclly we can never ever have both an alt id and a ss number to the same person so both boxes have to funtion independently. I Hope i explain clearly if not please let me know. Any help with this would be great. This thing has been driving me nut to where i spent close to 120 buck on VBA book and still cant figure it out. Thanks