access vba ?

[clmowers]

Hello,
Have a question. I have a database that is setup for adress updates. What i was wondering is in my search form if i search for a person, and that person is not in the database yet, have it pull up a new user form instead of the search results form. If this makes since. I know there probably a "if" statement somewhere i just dont know how or where. can anyone help Thanks

 

[JerryKlmns]

clmowers,

There is a dedicated MSAccess VBA forum forum705

And now:
How do you search?
If you post the code for that, maybe some1 could add extra logic to do what you need !

 

[clmowers]

Ok Thanks i didnt know that there was a dedicated fourm for access.
I have a query that has the statement

Like "*" & [Forms]![Search]![Social Security] & "*"

in the criteria field. I have this statement on 5 fields

Then i have a search form with unbounded text that has this code attached to the search button, which in end will display the results.

Private Sub SubmitSearchButton_Enter()
On Error GoTo Err_Command11_Click
Forms![Search].Recalc
DoCmd.OpenForm "Find Record", acFormDS
Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub

Does this make since on how i have this set up?

 

[JerryKlmns]

Private Sub SubmitSearchButton_Enter()
On Error GoTo Err_Command11_Click
    Forms![Search].Recalc
    If DCount("*", "TableNameHere", "FieldNameHere = '*" & [Forms]![Search]![Social Security] & "*'")>0 Then
      DoCmd.OpenForm "Find Record", acFormDS
    Else
      MsgBox "Does not exists"
    End If
Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
End Sub

Replace the appropriate table and field names that you use in your query, inside the DCount function - don't delete double quotes.

 

[clmowers]

Well I entered the code and when i try and run the search i get the following
Syntax error (missing operator) in query expression 'social security = '*111-11-1111*'. This is the code that i used

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"
    End If
Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
End Sub

Did i set this up correctly or did i do something wrong?

 

[PHV]

If DCount("*", "Information", "[Social Security] Like '*" & [Forms]![Search]![Social Security] & "*'") > 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[clmowers]

Yes that did work. Thanks all of you for you help.

 

[clmowers]

Ok I more question If i wanted to add one more field to that code where would i put it. I tried a few different option and i get errors or it doesnt do what i want. I would like to add the feild Alt ID to it. So if you input info in either one of these fields, ss or alt id, it will pull the correct form.

 

[JerryKlmns]

Extra field to ... filter?

The third argument of the function works just like the WHERE clause of a query without the WHERE word!

strCriteria = "[Social Security] Like '*" & [Forms]![Search]![Social Security] & "*' AND [Alt ID]=5"

If DCount("*", "Information", strCriteria) > 0 Then

 

[clmowers]

Well i guess what i mean is on my search form in have 2 differnt boxes. One for alt ID and the other for SS. Now depending on when the started with us, we are using there SS or a Alt id. If i search with a SS and that person is not in there then i need i to pull up the new form. If i search using the Alt ID and they are not in there then i need it to pull the new form. so either way i need it to pull the new form when they are not in the system Same with if they are in they system i would need it to pull up the results.

 

[JerryKlmns]

strCriteria1 = "[Social Security] Like '*" & [Forms]![Search]![Social Security] & "*' AND [Alt ID]=" & [Forms]![Search]![combo1].Value
strCriteria2 = "[Social Security] Like '*" & [Forms]![Search]![Social Security] & "*' AND [SS]=" & [Forms]![Search]![combo2].Value

If DCount("*", "Information", strCriteria1) + DCount("*", "Information", strCriteria2) > 0 Then

 

[clmowers]

Well I tried that code and its not working This is what i have

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

i change the Combo 1 to the name of the text boxes.But when i try and do a search i get the error message

Snytax erro (missing operator) in query expression '[social security] like '*111-11-1111*' and [alt id] ='

I have no clue why i cant get this thing to work. it just doesnt make since.