CODEWITHSUNDEEP
vbams-access
soldier2gud4me
soldier2gud4me

Reputation: 79

Validating a textbox entry if duplicate display error message microsoft access

i have searched around and cant find an answer, so i am using Microsoft Access Office 2019 and cant seem to validate my textbox for a duplicate entry. The user will add a record and enter a short text primary key (which in this case is the new employee's ID) Field - [EMPID] in the [EMPDETAILS] table. The below code has worked for validating my Autonumber primary key but doesnt seem to work for a custom short text primary key and i am getting this error

"The expression you entered as a query parameter produced this error : 'VS123'

<= this is the Employee ID which is a custom short text primary key :

Private Sub unqidd_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
'PartNum is the name of a textbox that contains the primary key, the rest are text to display.
strMessage = "Employee ID" & Me!unqidd & " already exists."

' confirm that part number doesn't already exist.
If (DLookup("[empid]", "[empdetails]", "[empID] = " & Forms![driverdetails]![unqidd])) Then
    MsgBox strMessage, vbInformation, "Invalid Operation"
    Cancel = True
End If
Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub

Upvotes: 2

Views: 825

Answers (2)

soldier2gud4me
soldier2gud4me

Reputation: 79

The solution is as below :

1) Create an unbound textbox (Text1616) and set its control source as 

=DLookUp("[empid]","[empdetails]","[empID] = '" & [Forms]![Driverdetails]![unqidd] & "'")

2) The textbox (Unqidd) that will need to be validated, on its beforeupdate property set the event procedure code as below :

Private Sub unqidd_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

Dim strMessage As String
'PartNum is the name of a textbox that contains the primary key, the rest are text to 
display.
strMessage = "Employee ID" & Me!unqidd & " already exists."

' confirm that part number doesn't already exist.
If Me.unqidd.Value = Me.Text1616 Then
    MsgBox strMessage, vbInformation, "Invalid Operation"
    Cancel = True
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub


'References :
'Table Name        : empdetails
'Table primary key : empid (Short text)
'Form Name         : Driverdetails
'Textbox 1         : unqidd (no formats, control source : empid)
'Textbox 2         : Text1616 (Unbound Textbox)

Upvotes: 0

braX
braX

Reputation: 11755

Since you are trying to add a string into [empID] I am going to assume it is a string field, in which case you need to include quotes:

If (DLookup("[empid]", "[empdetails]", "[empID] = '" & Forms![driverdetails]![unqidd] & "'")) Then

Upvotes: 1

Related Questions