AbacusInfoTech
asked on
Check for duplicate records before saving with DLOOKUP or something else.
Hi Experts.
I have a form dedicated to entering new data into a table. On this form I have a 'CHECK AND SAVE' button that, once pressed, will check whether the value entered in my 'companyjobid' field is unique in my table 'tbljob'.
If the value IS unique then the record may be saved.
If the value IS NOT unique, then the record will not be saved and a message box will appear.
I just can't figure out the syntax of DLOOKUP, which is what I think I need to use.
I have seen this solution which is very similar to what I want, but I can't figure out the syntax.
https://www.experts-exchange.com/questions/20574943/Check-for-duplicates-before-save-record.html
I already have some code on this button that check all of my required fields for null values. This works a treat, but I need to get this extra code to stop duplicated entries in my 'companyjobid' field.
FYI this field is not the key.
I have a form dedicated to entering new data into a table. On this form I have a 'CHECK AND SAVE' button that, once pressed, will check whether the value entered in my 'companyjobid' field is unique in my table 'tbljob'.
If the value IS unique then the record may be saved.
If the value IS NOT unique, then the record will not be saved and a message box will appear.
I just can't figure out the syntax of DLOOKUP, which is what I think I need to use.
I have seen this solution which is very similar to what I want, but I can't figure out the syntax.
https://www.experts-exchange.com/questions/20574943/Check-for-duplicates-before-save-record.html
I already have some code on this button that check all of my required fields for null values. This works a treat, but I need to get this extra code to stop duplicated entries in my 'companyjobid' field.
FYI this field is not the key.
u need to use the Form_BeforeUpdate function
basically do this
IF NZ(DLOOKUP("field to check","tablename",""),"")
msgbox "Already exists"
cancel = true 'abort save
END IF
basically do this
IF NZ(DLOOKUP("field to check","tablename",""),"")
msgbox "Already exists"
cancel = true 'abort save
END IF
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, this is better....
Private Sub test_Click()
If Not IsNull(Me!companyjobid) Then
If Not IsNull(DLookup("[companyjo
MsgBox "A duplicate exists.", vbInformation, "Note"
Else
MsgBox "No duplicates", vbInformation, "Note"
End If
End If
End Sub
...I got rid of the ampersand and at least I get a 'no duplicates' box now but it's not correctly looking at my table to find what I know to be a duplicate.
Private Sub test_Click()
If Not IsNull(Me!companyjobid) Then
If Not IsNull(DLookup("[companyjo
MsgBox "A duplicate exists.", vbInformation, "Note"
Else
MsgBox "No duplicates", vbInformation, "Note"
End If
End If
End Sub
...I got rid of the ampersand and at least I get a 'no duplicates' box now but it's not correctly looking at my table to find what I know to be a duplicate.
ASKER
Thanks, i'll give these a whirl.
in your last post, the code
If Not IsNull(DLookup("[companyjo
u got the = sign outside the quotes, its got to be inside
i.e.
If Not IsNull(DLookup("[companyjo
doing it outside quotes wont work anyway
If Not IsNull(DLookup("[companyjo
u got the = sign outside the quotes, its got to be inside
i.e.
If Not IsNull(DLookup("[companyjo
doing it outside quotes wont work anyway
ASKER
Thanks rockiroads....!
This is my code that works fine in the beforeupdate of my field:
Dim sID As String
sID = Nz(companyjobid.Value, "")
If sID <> "" Then
If Nz(DLookup("companyjobid",
MsgBox "Dups"
Else
MsgBox "No Dup"
End If
End If
This is my code that works fine in the beforeupdate of my field:
Dim sID As String
sID = Nz(companyjobid.Value, "")
If sID <> "" Then
If Nz(DLookup("companyjobid",
MsgBox "Dups"
Else
MsgBox "No Dup"
End If
End If
ok, cool
that means its a string
remember the difference in using strings/numbers
use of quotes!
that means its a string
remember the difference in using strings/numbers
use of quotes!
ASKER
If Not IsNull(Me!companyjobid) Then
If Not IsNull (DLOOKUP ("[companyjobid]","tbljob"
MsgBox "A duplicate exists.", vbInformation, "Note"
Else
MsgBox "No duplicates", vbInformation, "Note"
End If
End If