Code to see if a value exists in table

  • Thread starter Thread starter RipperT
  • Start date Start date
R

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
 
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
 
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
 
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 @comcast.net> said:
Thanx! What about if the value to be searched for is in a cboBox?
Click to expand...

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
 
Back
Top