Help. Search form VBA coding issue

clmowers

New member
Local time
Today, 03:03
Joined
Apr 20, 2006
Messages
4
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
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
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
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
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
 

Users who are viewing this thread

Back
Top Bottom