Question Really Quick DLookup Question

[MOTOWN44]

Now then

This is probably seriously easy but i cant for the life of me get it to work properly!

i want a message to appear if a number has already been entered in to the table, i reckon DLookup is the way to go but i could be wrong

My table tblStaff has a field StaffID now when entering a new record in to the form in the txtStaffID field if the staffID is already in the table (ie a duplicate) it brings up a message box with "duplicate blah blah blah" if its not a duplicate it does nothing

If DLookup("[StaffID]", "tblStaff", "StaffID = " & Me.txtStaffID) Then
 
MsgBox "Sorry Duplicate Number, Please Try Again", vbInformation
 
Else
 
Exit Sub
 
End If

StaffID is a numerical value and ive tried almost every combination of ()'s, ""s, 's and []'s but i always get errors or the message box appears everytime regardless of if its a duplicate or not.

also can Dlookup be "nested" like in excel?

eg if i had another field call YearJoin the messagebox would only appear if the StaffID and YearJoin were the same on the same record

Thanks in advance

 

[jzwp22]

The DLookup() has to evaluate to something either a number or Null

If DLookup("[StaffID]", "tblStaff", "StaffID = " & Me.txtStaffID) = NULL Then
 
   Exit Sub
 
Else
  MsgBox "Sorry Duplicate Number, Please Try Again", vbInformation

 
End If

If you want to use 2 criteria in the DLookup() (assuming yearjoin is numeric)

If DLookup("[StaffID]", "tblStaff", "StaffID = " & Me.txtStaffID & " AND YearJoin=" & me.yearjoin) = NULL Then
 
   Exit Sub
 
Else
  MsgBox "Sorry Duplicate Number, Please Try Again", vbInformation

 
End If

 

[MarkK]

If you are checking for the existence of a record satisfying certain criteria I would use DCount().

 

[MOTOWN44]

thanks for getting back to me guys, i cant get Dlookup to work with more than 1 criteria but if you think Dcount is the way to go what would the syntax be ?

If DCount("[StaffID]", "tblStaff", "StaffID = " & Me.txtStaffID) Then ...

returns that its isnt a duplicate no matter what and if i add somthing like = [SBI] or table.tblStaff.SBI it just bugs out

sorry if im being a bit dim, its the first time ive used functions like these

 

[jzwp22]

If your other criteria is not a number (i.e. text or date), the value you are supplying from the form must be enclosed by single apostrophes or # signs respectively.

Here is the previous DLookup() converted to the DCount()

If DCount("*", "tblStaff", "StaffID = " & Me.txtStaffID & " AND YearJoin=" & me.yearjoin) = 0 Then
 
   Exit Sub
 
Else
  MsgBox "Sorry Duplicate Number, Please Try Again", vbInformation

 
End If

If the other criteria field is text, the code would look like this:

If DCount("*", "tblStaff", "StaffID = " & Me.txtStaffID & " AND sometextfield='" & me.somecontrol & "'") = 0 Then
 
   Exit Sub
 
Else
  MsgBox "Sorry Duplicate Number, Please Try Again", vbInformation

 
End If

If the other criteria field is a date, the code would look like this:

If DCount("*", "tblStaff", "StaffID = " & Me.txtStaffID & " AND somedatefield=#" & me.somecontrol & "#") = 0 Then
 
   Exit Sub
 
Else
  MsgBox "Sorry Duplicate Number, Please Try Again", vbInformation

 
End If

 

[Galaxiom]

If DLookup("[StaffID]", "tblStaff", "StaffID = " & Me.txtStaffID) = NULL

That is a meaningless expression. Any expression = NULL will always return false, including:

If Null = Null

In fact the original expression is valid because DLookUp has a boolean compatible return. Any value except zero will be interpreted as True

If DCount("[StaffID]", "tblStaff", "StaffID = " & Me.txtStaffID) Then

If you are checking for the existence of a record satisfying certain criteria I would use DCount().

One advantage of the DLookUp is that it will return a value as soon as a match is found where a DCount will consult every record. However any field subjected to a domain expression should be indexed. The index can return the count as easily as finding the first match so the speed is the same for either expression.

 

[jzwp22]

That is a meaningless expression. Any expression = NULL will always return false, including:

If Null = Null

In fact the original expression is valid because DLookUp has a boolean compatible return. Any value except zero will be interpreted as True

GalaxiomAtHome, I stand corrected, thanks for pointing that out.

MOTOWN44, on what event are you executing your code? Also, if a duplicate is found, you have to cancel the event. I have seen people typically use the before update event of the form to prevent duplicates. Perhaps something along these lines:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DLookup("part_ID", "tblParts", "part_ID=" & Me.PART_ID) Then
    MsgBox DLookup("part_ID", "tblParts", "part_ID=" & Me.PART_ID) & " is a Duplicate Number, Please Try Again", vbInformation
    Cancel = True
    Me.PART_ID = Null
    Me.PART_ID.SetFocus
End If

End Sub