Check if record already exists

[tractorvix]

I know this has a really simple solution, but my brain just seems to have gone on holiday this afternoon. I've built a form which, through the use of combo boxes, builds up a product code. Before I add this new product to the list I would like to check if it already exists. I've set up a query, which shows if there is a record match but I'm not sure how to put this into the form so I can run the appropriate if statement. Ideally I would like something that does something like:

recordmatch = "SELECT [tbl-ProdID].[Product full Name]"&_
"FROM [tbl-ProdID]"&_
"WHERE ((([tbl-ProdID].[Product full Name])=[Forms]![frm-addproduct]![shortname]));"


if count of recordmatch is 0 then
add new record
else
msgbox "already exists"
end if


the product shortname is built based on the input from the combo boxes selected on the form.
Any help would be amazing!

Thanks

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tractorvix]

thanks for the response PHV, I was trying to avoid a dlookup as it seems from other threads that they can be quite timeconsuming and resource hungry, but have given it a go and am still having problems.

with the following syntax
varX = DLookup("productfullname", "tbl-prodid", "productfullname" = Me.shortname)

VaxX remains null, even when I know that there is a record match (I've checked using the original query) I suspect this is because of the location of the equals sign, but with the following syntax:
varX = DLookup("[productfullname]", "tbl-prodid", "[productfullname] = " & Me.shortname)

I get an error '3705'
syntax error (missing operator) in query expression '[productfullname] = group1 type1 A'

any ideas???

Vicky

 

[PHV]

varX = DLookup("[productfullname]", "tbl-prodid", "[productfullname] = [!]'[/!]" & Me.shortname & "[!]'[/!]")


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tractorvix]

You absolute star PHV!

Thank you so much for your help with this.