By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Not allowing duplicate values

Expert 100+
P: 1,892
I have a form and I would like to check to see if a value already exists in the database before I let the form get submitted. Is this something that can be easily done? BTW NeoPa query bound form working great.

Thanks,
Aric
Jan 3 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
I have a form and I would like to check to see if a value already exists in the database before I let the form get submitted. Is this something that can be easily done? BTW NeoPa query bound form working great.

Thanks,
Aric
1 method is to use the DLookup() Function which returns the value of a
specified Field from a set of Records (Domain). Criteria can also be applied
to this Function. Here is the proper syntax:
DLookup(expr, domain[, criteria])

'In this specific instance, we are checking to see if a value exists in the [LastName] field
of tblEmployee where the [PayrollNumber] equals the value in [txtPayrollNumber].
If [PayrollNumber] was a String then line 2 would be:
strLast = Nz(DLookup("[LastName]", "tblEmployee", "[PayrollNumber] ='" & Me![txtPayrollNumber] & "'"))

Expand|Select|Wrap|Line Numbers
  1. Dim strLast As String
  2. strLast = Nz(DLookup("[LastName]", "tblEmployee", "[PayrollNumber] =" & Me![txtPayrollNumber]))
  3.  
  4. If Len(strLast) > 0 Then
  5.   MsgBox strLast & " already exists in the Database"
  6. Else
  7.   MsgBox strLast & " does not exist"
  8. End If
Jan 3 '07 #2

Expert 100+
P: 1,892
Thanks I'll give that a try.
Jan 3 '07 #3

Expert 100+
P: 1,892
Not sure why but I get the following error:

Data type mismatch in criteria expression.

Here is the code I'm trying to use:
Expand|Select|Wrap|Line Numbers
  1. strSerialNumber = Nz(DLookup("[strSerialNumber]", "TSerialNumbers", "[strSerialNumber] =" & Me![txtSerialNumber]))
  2.  
  3. If Len(strSerialNumber) > 0 Then
  4.   MsgBox strSerialNumber & " already exists in the Database"
  5. Else
  6.   MsgBox strSerialNumber & " does not exist"
  7. End If
  8.  
The table TSerialNumbers has two fields intSerialNumberID (PK) and strSerialNumber. Any ideas?

Thanks,
Aric
Jan 3 '07 #4

Expert 100+
P: 1,892
Nevermind I changed the syntax to:
Expand|Select|Wrap|Line Numbers
  1. strSerialNumber = Nz(DLookup("[strSerialNumber]", "TSerialNumbers", "[strSerialNumber] = '" & Me![txtSerialNumber] & "'"))
  2.  
Works great, thanks ADezii!

Aric
Jan 3 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
I have a form and I would like to check to see if a value already exists in the database before I let the form get submitted. Is this something that can be easily done? BTW NeoPa query bound form working great.

Thanks,
Aric
I'm really pleased to hear that Aric :).
I had this page ready to reply to earlier but ADezii beat me to the punch. His code should work for you just fine.
A little refinement though, as I'm typing anyway, would be to include this in the 'BeforeUpdate' event of the form.
It would have to be modified slightly to reflect a different usage (as below) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim strLast As String
  3.  
  4.     strLast = Nz(DLookup("[LastName]", _
  5.                          "tblEmployee", _
  6.                          "[PayrollNumber]=" & Me.[txtPayrollNumber]), "")
  7.     If strLast = "" Then
  8.         Call MsgBox(strLast & " already exists in the table")
  9.         Cancel = True
  10.     End If
  11. End Sub
Again, if the seached for field is text then it would need to be modified to surround the literal value with single-quotes (').
Jan 3 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
Just caught your later posts - in that case the routine would be :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim strLast As String
  3.  
  4.     strLast = Nz(DLookup("[strSerialNumber]", _
  5.                          "TSerialNumbers", _
  6.                          "[strSerialNumber]='" & Me.[txtSerialNumber]) & "'", "")
  7.     If strLast = "" Then
  8.         Call MsgBox(strLast & " already exists in the table")
  9.         Cancel = True
  10.     End If
  11. End Sub
Jan 3 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.