Not allowing duplicate values

[AricC]

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

[ADezii]

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 [txtPayrollNumbe r].
If [PayrollNumber] was a String then line 2 would be:
strLast = Nz(DLookup("[LastName]", "tblEmploye e", "[PayrollNumber] ='" & Me![txtPayrollNumbe r] & "'"))

Dim strLast As String
strLast = Nz(DLookup("[LastName]", "tblEmployee", "[PayrollNumber] =" & Me![txtPayrollNumber]))

If Len(strLast) > 0 Then
  MsgBox strLast & " already exists in the Database"
Else
  MsgBox strLast & " does not exist"
End If

[AricC]

Thanks I'll give that a try.

[AricC]

Not sure why but I get the following error:

Data type mismatch in criteria expression.

Here is the code I'm trying to use:

strSerialNumber = Nz(DLookup("[strSerialNumber]", "TSerialNumbers", "[strSerialNumber] =" & Me![txtSerialNumber]))

If Len(strSerialNumber) > 0 Then
  MsgBox strSerialNumber & " already exists in the Database"
Else
  MsgBox strSerialNumber & " does not exist"
End If

The table TSerialNumbers has two fields intSerialNumber ID (PK) and strSerialNumber . Any ideas?

Thanks,
Aric

[AricC]

Nevermind I changed the syntax to:

strSerialNumber = Nz(DLookup("[strSerialNumber]", "TSerialNumbers", "[strSerialNumber] = '" & Me![txtSerialNumber] & "'"))

Works great, thanks ADezii!

Aric

[NeoPa]

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) :

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strLast As String

    strLast = Nz(DLookup("[LastName]", _
                         "tblEmployee", _
                         "[PayrollNumber]=" & Me.[txtPayrollNumber]), "")
    If strLast = "" Then
        Call MsgBox(strLast & " already exists in the table")
        Cancel = True
    End If
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 (').

[NeoPa]

Just caught your later posts - in that case the routine would be :

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strLast As String

    strLast = Nz(DLookup("[strSerialNumber]", _
                         "TSerialNumbers", _
                         "[strSerialNumber]='" & Me.[txtSerialNumber]) & "'", "")
    If strLast = "" Then
        Call MsgBox(strLast & " already exists in the table")
        Cancel = True
    End If
End Sub