How to check for existing record?

[crillux]

I have a piece of code that takes the values from a couple of fields in an unbound form and insert them in a table using SQL. It works fine.

However, I need to do a some controls before I insert the values, and I can't figure out how to do it.

This is what I need:

1) A check if the value from the listbox lstproducts exist in tblBookingList. If it does not exist it should be inserted and everything is fine.

2) However, if the value does exist I need to compare the txtOutdate value in the form with the Outdate field in tblBookinglist. If the dates match, the insertion should be stopped. If the dates does not match, the values can be inserted. There can be several occurrencies of the value with different dates, and none of these dates are allowed to match.

Thanks in advance.

 

[vbaInet]

Perform a search on the forum for this topic. You will find those that use a DCount() function.

 

[crillux]

Thanks, it helped, but one issue remains:
How do I get the value of a textbox in the form as criteria for dCount?

Currently the code looks like this:
countRec = DCount("[OrderNo]", "tblTest", "[OrderNo] = frmMgr![OrderNo]")

What I want to do is to use the value in OrderNo in the form frmMgr but I cant get it to work. Any suggestions?

 

[vbaInet]

If the datatype of the field OrderNo is a number type use this:

countRec = DCount("[OrderNo]", "tblTest", "[OrderNo] = " & Nz(Me![OrderNo], 0))

If it's a string then you use this:

countRec = DCount("[OrderNo]", "tblTest", "[OrderNo] = [B][COLOR=Red]'[/COLOR][/B]" & Nz(Me![OrderNo], 0) & "[COLOR=Red][B]'[/B][/COLOR]")

The difference is the value is wrapped in single quotes. Think of it like when you write a criteria in a query, it's normally enclosed in single or double quotes for strings and not for numbers.

 

[crillux]

Thanks again.

And a follow-up:
I realize it would be a good idea to assign the field values to variables and then use the variables as criteria in DCount. Do I need to handle the variables differently as well in the DCount function depending on datatype?

 

[vbaInet]

Yes you would. It's what's on the left of the equal to sign that counts. It must receive a value of its datatype so you wrap the variable. Fyi for dates you use the hash character.

 

[crillux]

I follow up with another one then...

Once we have determined wheter the product exist in the booking list, we need to decide wheter we can book it on the date we need it.

I have used DLookup to get the date into a variable:

wantedOutDate = DLookup("[OutDate]", "tblTest", "[OrderNo] = '" & Nz(Me![OrderNo], 0) & "'")

And then I compare that date with the OutDate in my form. So far it all works fine. But what happens when DLookup returns more than one date, if the product are booked on several occurrencies? How do I handle that?

 

[vbaInet]

The DLookup would only return one value.