DLookup and Duplicate Values

[margaret]

I have a form and I would like a message box to appear if there is a
duplicate value using two different controls ... emp and boothdate. This is
what I have:

Private Sub boothdate_BeforeUpdate(Cancel As Integer)

Dim varboothdate As Variant

If IsNull(Me.emp) Or IsNull(Me.boothdate) Then
CheckDuplicates = False

Else

varboothdate = DLookup("emp", "[tblemp booth assignment]", _
"emp = " & Me.emp & " AND boothdate = " & Me.boothdate)

If Not IsNull(varboothdate) Then

MsgBox "Employee is already assigned on that date. Verify Employee and
booth date."

CheckDuplicates = True

Else

CheckDuplicates = False
End If
End If

End Sub

It's not working. I'm not getting an error or anything. Any help would be
appreciated.

 

[Tom van Stiphout]

On Tue, 13 Oct 2009 16:16:03 -0700, margaret

Why not create a Unique Index over those two fields, and have Access
give the error message?
Depending on the exact situation you could then intercept the error in
the Form_Error event and show a prettier message.

If you decide to continue along the lines of your current code:
Checking in <controlname>_BeforeUpdate is not the best place, because
user may fill out your form in a different order. The best place is
the Form_BeforeUpdate. If you find an objectionable condition, you
should set Cancel=True so that the update does not happen.

DLookup and other functions require dates to have #-signs around them,
so you will need to fix that 3rd argument:
.... AND boothdate = #" & Me.boothdate & "#"

-Tom.
Microsoft Access MVP

 

[Dorian]

If boothdate is datetime field you need:

AND boothdate = #" & Me.boothdate & "#")

also, if emp is text, you need enclosed in single quotes:

"emp = '" & Me.emp & "'"

Also, please do not use any embedded blanks in your table or column names!!!
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Klatuu]

Assuming boothdate is a date data type rather than a numeric data type, you
need to change the syntax in this line:

varboothdate = DLookup("emp", "[tblemp booth assignment]", _
"emp = " & Me.emp & " AND boothdate = " & Me.boothdate)

to:
varboothdate = DLookup("emp", "[tblemp booth assignment]", _
"emp = " & Me.emp & " AND boothdate = #" & Me.boothdate & "#")

But, you don't really need the varboothdate variable. It will work just as
well with this:

If IsNull(DLookup("emp", "[tblemp booth assignment]", _
"emp = " & Me.emp & " AND boothdate = #" & Me.boothdate & "#")) Then
MsgBox

 

[Mike Painter]

Also, please do not use any embedded blanks in your table or column
names!!! -- Dorian

It sure seemed like a good idea at the time but I've come to hate it.

Maybe MSFT should follow Sir Tim's example.

"A light has been shone on one of the great mysteries of the internet. What
is the point of the two forward slashes that sit directly infront of the
"www" in every internet website address?
The answer, according to the British scientist who created the world wide
web, is that there isn't one. "

http://technology.timesonline.co.uk/tol/news/tech_and_web/article6872873.ece