lookup code

[Bkndbrown]

I am sure this is easy but it is kicking my butt. I have a text box called SearchID I want it to count entries in SponsorDataT, field called LastINILast4SSN. If there are any matches open a record with that record if not open a new record. Sorry if code is messed up had to go computer to phone to here. Stupid firewall.

Private Sub Command13_Click()
If DCount("[LastINILast4SSN]", "[SponsorDataT]", "Forms![OpeningF]![SearchID]") < 0 Then
DoCmd.OpenForm "SponsorDataSF", acNormal, "", "", acAdd, acNormal


DoCmd.GoToRecord , , acNewRec

Else
DoCmd.OpenForm "SponsorDataSF", acNormal, "", "", acEdit, acNormal
DoCmd.SearchForRecord acForm, "SponsorDataSF", acFirst, "LastINILast4SSN = '" & Forms!OpeningF!SearchID & "'"
End If
End Sub

[Bkndbrown]

Sorry forgot when I run it I get the same result whether there is a match or not so it isnt actually counting

[June7]

The filter criteria needs field specified to match against.

If DCount("*", "[SponsorDataT]", "ID=Forms![OpeningF]![SearchID]") < 0 Then

[Bkndbrown]

That caused a run time error 2471

[June7]

What is the exact error message?

Did you use your actual field name for ID?

Why <0? Why not =0?

[Bkndbrown]

Just realized I fat fingered a letter. It was =0 I changed it trying to figure what answer I was getting. Changed it back after posting. Yhanks

[knarfreppep]

I am sure this is easy but it is kicking my butt. I have a text box called SearchID I want it to count entries in SponsorDataT, field called LastINILast4SSN. If there are any matches open a record with that record if not open a new record. Sorry if code is messed up had to go computer to phone to here. Stupid firewall.

Private Sub Command13_Click()
If DCount("[LastINILast4SSN]", "[SponsorDataT]", "Forms![OpeningF]![SearchID]") < 0 Then
DoCmd.OpenForm "SponsorDataSF", acNormal, "", "", acAdd, acNormal
DoCmd.GoToRecord , , acNewRec

Else
DoCmd.OpenForm "SponsorDataSF", acNormal, "", "", acEdit, acNormal
DoCmd.SearchForRecord acForm, "SponsorDataSF", acFirst, "LastINILast4SSN = '" & Forms!OpeningF!SearchID & "'"
End If
End Sub

If DCount("[LastINILast4SSN]", "[SponsorDataT]", "LastINILast4SSN = '" & Forms!OpeningF!SearchID & "'") = 0 Then

[June7]

This expression should work either way because Access will be able to evaluate the control reference using the Forms collection prefix even if it is within quote marks. I was quite surprised the first time I encountered this. Now if you use Me. alias instead, that would require concatenation.