Validate a text box with a field in a Table

[vatechman3]

Hey all,
I came across an issue today when I was creating a text box on a form. I want users to enter in their own private id (like a social security number) in a text box. I wanted the text box to only allow entries that I have already listed in a table. This information would then be used to open a form that only displays records that had the private id. Hopefully you can see why I do not want to use a combo box (private info). I figured I could use the Validation Rule but was not sure how to do this. I assume "Like [TableName]![TableFieldName]"
is not going to work. Thanks in Advance

 

[nanscombe]

How about something like the attached?

Private Sub txtSsn_BeforeUpdate(Cancel As Integer)
Dim txtSsn As String

' Look up the value in txtSSN in the table
txtSsn = Nz(DLookup("SSN", "tblSSN", "lcase(SSN) = '" & LCase(Nz(Me.txtSsn, "")) & "'"), "")

' If no value is returned then Cancel is true and the cursor won't leave the TextBox
Cancel = (txtSsn = "")

Me.cmdOpenForm.Enabled = Not Cancel

If Cancel Then
    Me.lblSsnResult.Caption = "SSN not found"
    MsgBox "SSN was not found", vbCritical + vbOKOnly
Else
    Me.lblSsnResult.Caption = "SSN found"
End If
End Sub

The code uses the DLookup() function to check tblSSN for a matching entry in the field SSN. If it is found it will return a value else it will return an empty string which can be tested.

 

[vatechman3]

Thanks nanscombe, Trying it out now.

 

[vatechman3]

Thanks again nanscombe, This worked out perfect.
I really appreciate you sending the example as well. I am not a VBA user, I have managed to get by almost exclusively on expressions. Thank you very much. How do I give you proper Kudos on here?

I did remove "Me.cmdOpenForm.Enabled = Not Cancel"

because I was receiving errors, but I am sure that was my fault.

Thanks

 

[nanscombe]

That's fine. I'm glad it helped.

 

[txczshooter]

I realize this is an extremely old thread, but very relevant to my situation. However, I need to take it a step further.

I have a form frmTimeClock and table tblEmployee. Table tblEmployee has columns EmpID (unique), EmpName, and Password. On frmTimeclock, EmpName is selected from a combobox. Source of combobox is tblEmployee. Once that EmpName is selected, the user enters a Password. On exiting the field either by tab or click, the password needs to be validated in tblEmployee against the associated username selected . Currently combobox only displays name, but if it must display 2 columns in combobox to show EmpID as well, that's fine also. The other condition is that tblEmployee must be a reference table only and not changed/appended.

Any help is appreciated.