Lookup

[Sash]

I want to programmatically look at a field and see if the value exists in a
table. I thought Dlookup was my answer, but it doesn't seem to work.

 

[Douglas J. Steele]

You'll have to provide more details. DLookup is certainly an appropriate
means of determining whether or not a particular value exists in a
particular field in a particular table. What code did you try, and what
happened when you tried running it?

 

[fredg]

I want to programmatically look at a field and see if the value exists in a
table. I thought Dlookup was my answer, but it doesn't seem to work.

Just a helpful tip to help you get good responses when asking
questions in newsgroups.
Words like 'That didn't work' gives any potential reader who might
want to help you absolutely no useful information.
What didn't happen?
What did happen?
What did you expect to happen?
Did you enter your table and field names in place of the generic ones
given in Help.
Where did you place the code?
What is the exact code you wrote (copied directly from your database
and pasted here so we can see if you didn't simply mis-write the
code)?
Answers to those questions would be helpful to us .... to help you!

 

[Sash]

I've changed it a few times. This is my latest version

Dim stInsCheck As String
Dim rsInsCheck As Recordset
stInsCheck = "SELECT * from Elim_Insurance"
Set rsInsCheck = db.OpenRecordset(stInsCheck, dbOpenDynaset, dbSeeChanges)
Dim stIns As String
Dim stLkIns As String
stIns = rs.Fields("INS1")
stLkIns = rsInsCheck.Fields("No_Insurance")
Dim stInsLook As String
stInsLook = DLookup(stLkIns, Elim_Insurance, stIns <> stLkIns)

I appears that I'm only getting the first fow in the Elim_Insurance table.

 

[Douglas J. Steele]

Yes, stIns will contain the value of field INS1 for the first row of the
recordset (and since you don't have an ORDER BY clause on the SQL, that
means it's essentially going to be a random value. Of course, I don't see
where you've instantiated rs anywhere.

Not only that, but you're trying to assign the results of the DLookup to a
string variable. If nothing's found, DLookup returns Null, and string
variables cannot be assigned a value of Null: the only data type that
accepts Null is the variant.

However, the DLookup itself doesn't look right.

The three arguments for Dlookup are supposed to be strings, which means that
you either use a string variable that's been assigned a value, or you put
the value in quotes.

You said you want to look at a field and see whether a value existed in the
table. Assuming you have a particular value, you'd use something like:

If DCount("*", "NameOfTable", "NameOfField = " & _
VariableHoldingValue) = 0 Then
MsgBox VariableHoldingValue & " is not in the table"
Else
MsgBox VariableHoldingValue & " is in the table"
End If

assuming that NameOfField is a numeric field, or

If DCount("*", "NameOfTable", "NameOfField = """ & _
VariableHoldingValue & """") = 0 Then
MsgBox VariableHoldingValue & " is not in the table"
Else
MsgBox VariableHoldingValue & " is in the table"
End If

if it's text.