DLookup Question

[hedgracer]

I am trying to used the DLookup function to compare information between two tables. I need to do the following in order:

1. Generate a string (i.e. 150116)and put it in a string variable (sSampleNum).
2. Compare sSampleNum to a list of sample numbers stored in a table (SampleNumber).
3. If sSampleNum matches any samples numbers stored in SampleNumber regenerate another sample number and repeat 1 and 2; if not continue onward in the VBA code.

This seems like a simple process to use the DLookup function but I have yet to successfully use it. I have looked this function up on MS Access help and on MSDN but still are having trouble. Any help or guidance is appreciated. Thanks.

Dave C.

 

[lupins46]

Sub testdc()
Dim ssamplenum As String
ssamplenum = "12345"

If DCount("*", "SampleNumber", "samplefieldname= " & ssamplenum) > 0 Then
MsgBox "At least one"
Else
' there isnt
End If
End Sub