Dlookup or FindFirst

[poporacer]

For some reason I am not able to figure this out....I have been looking all over for the answer to no avail. I have a form (frmAddInmate) that has a textbox (txtCDCNum) on it. I have a table (tblInmates) that has several fields (ID, CDCNum, and other data) what I want to do is when someone enters a string in txtCDCNum and upon exiting the textbox the code checks to see if the string exists in the table in field CDCNum. If the string exists, then inform the user and return to the textbox.
I have tried several versions of FindFirst and checking if it is null and several versions of Dlookup. In researching this it seems like I am coding it correctly but it doesn't work. I have the identical thing on another form and it works perfectly.

Here is the code I am trying and the errors:
(This is the code that works on another form, the only differrence is that the other form the criteria is in a listbox)

Private Sub txtCDCNum_AfterUpdate()
Dim reply As String
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

txtCDCNum = StrConv(txtCDCNum, vbUpperCase)
rs.FindFirst "[ID] = " & Str(Nz(Me![txtCDCNum], 0))
If rs.EOF Then
Exit Sub
Else
reply = MsgBox("This CDC Number already exists", vbOKOnly, "Duplicate CDC numbers")
txtCDCNum.SetFocus

End If

End Sub

** the error I get is Type mismatch in the FindFirst code.

The Dlookup I tried is:

Private Sub txtCDCNum_AfterUpdate()
Dim reply As String, NumTest As String
txtCDCNum = StrConv(txtCDCNum, vbUpperCase)

If IsNull(NumTest = DLookup("[CDCNum]", "tblInmates", "[CDCNum]= '[me!txtCDCNum]'")) Then
Exit Sub
Else
reply = MsgBox("This CDC Number already exists", vbOKOnly, "Duplicate CDC numbers")
txtCDCNum.SetFocus

End If

End Sub

**This is always Null

Any suggestions?

 

[RuralGuy]

FYI, unless you have made special conditions, Access is *not* case sensitive. Is the tblInmates table the RecordSource of your form and is the txtCDCNum control bound to the [CDCNum] field? If the answer to the two questions is yes then your code should be in the BeforeUpdate event rather than the AfterUpdate event of the control. You then only need set Cancel = True to hold the focus in the control.

 

[pbaldy]

Here's a good reference on the syntax for DLookup():

http://www.mvps.org/access/general/gen0018.htm

I note that you're looking at 2 different fields in the 2 tests (ID & CDCNum).

 

[poporacer]

Thanks guys, I figured it out...I had the [ in the wrong place.
[me!txtCDCNum] should have been me![txtCDCNum].

You guys are awsome

 

[vandy05]

I need some help.
table 1 - site name and info
table 2 - site personnel info (each site can have more than one personnel so it is tied to table 1 with an ID)
table 3 - phone contact details (in the form, everything fills out automatically for the site info but i need to be able to enter the person contacted manually)

I want to be able to have this person contacted field verify that the name exists in the database in table 2 under name otherwise it needs to prompt the user to enter the data.

How do i do this?

 

[RuralGuy]

I need some help.
table 1 - site name and info
table 2 - site personnel info (each site can have more than one personnel so it is tied to table 1 with an ID)
table 3 - phone contact details (in the form, everything fills out automatically for the site info but i need to be able to enter the person contacted manually)

I want to be able to have this person contacted field verify that the name exists in the database in table 2 under name otherwise it needs to prompt the user to enter the data.

How do i do this?

Do yourself a favor and start a new thread with this post instead of tagging on to an existing old thread.