Code to see if a value exists in table

[RipperT]

Hello,

I need a bit of code that will search a table field for a certain value and
return a true or false. I don't need to see the value or record, I just need
to know if it is there so the code can then call up a msgBox if the value is
in the table.
Many thanx.

Rip

 

[Albert D.Kallal]

Sure, you can use:


if dcount("FieldPartNum","tblParts","FieldpartNum = 123") = 0 then
msgbox "part number 123 does not exist"
end if

Do note if the field you are seaching is text, then you must surround the
seach value with quotes.

if dcount("FieldPartNum","tblParts","FieldpartNum = '123' ") = 0 then
msgbox "part number 123 does not exist"
end if

 

[RipperT]

Thanx! What about if the value to be searched for is in a cboBox?

Rip

 

[Guest]

Hi Ripper,

And an alternative to Albert's suggestion is to use the DLookup function

Hardcoded Search Value
================
if IsNull(DLookup("FieldPartNum", "tblParts", "FieldPartNum = 123")) then
Msgbox "Part 123 does not exist.", vbOkOnly
end if

Dynamic Search Value
==============
if IsNull(DLookup("FieldPartNum", "tblParts", "FieldPartNum = " &
txtSearchField) )then
Msgbox "Part 123 does not exist.", vbOkOnly
end if

As Albert also mentioned, fields defined as text will require the single
quote.

Lance

 

[Guest]

Using DCount
=========
if dcount("FieldPartNum","tblParts","FieldpartNum = " & cboBox) = 0 then
msgbox "part number 123 does not exist"
end if

If FieldPartNum is of data type string, then make it "FieldPartNum = '" &
cboBox & "'" (its hard to see but there are single quotes added.

Using DLookup
=========
if IsNull(DLookup("FieldPartNum", "tblParts", "FieldPartNum = " &
cboBox) )then
Msgbox "Part 123 does not exist.", vbOkOnly
end if

The same applies for DLookup if the data type is a string for the field
being searched.

Lance

 

[RipperT]

Thank you for the responses.
I've used your suggestions and it works great

Rip

 

[Albert D.Kallal]

Thanx! What about if the value to be searched for is in a cboBox?

Just use the expression from the combo box.

if dcount("FieldPartNum","tblParts","FieldpartNum = " & me.MyComboBox) = 0
then
msgbox "part number in combo box does not exist"
end if

Do note if the field you are seaching is text, then you must surround the
seach value with quotes.

if dcount("FieldPartNum","tblParts","FieldpartNum = '" & me.MyComboBox &
"'") = 0 then
msgbox "part number 123 does not exist"
end if