I Have one Table named of "User" with following Column:
ID as PK Autonumber and Name, And a form following with text box
ID and Name All text box is unbound. when ID text box is updated with User's table id value its shows the name against the entered ID in the Unbound Name text box which ID is already exist in “User” table. If entered ID is not valid means not exist in table “User” then the Unbound Name text box Shows “Invalid User or My error message” and cursor setfocus to ID text box.
Example: Data already Exist in User table is
1 Debashish Mahonta
2 Shajib Mahonta
3 Banna Das
When I enter 1 or 2 or 3 in form ID text box and press enter, the unbound text box shows the name "Debashish Mahonta for "Shajib Mahonta for 2" and "Banna Das for 3". But If I enter 0 or 4 or 5 or other value that not exist in user table then the unbound Name text box shows “invalid User” or My custom message and cursor setfocus to ID text box. How can I do it.
You may try the following code in the After_Update() Event Procedure. The Record Source of the Form must be set with the Users Table Name. Keep both Text Boxes on the Form Unbound. The second field Name must be changed to UserName. The word Name is a reserved word in Ms-Access and should not be used as field-name or variable-name, like any other reserved word in Access.
The Code assumes that your Table field names are: ID, UserName
Private Sub ID_AfterUpdate()
Dim lngID As Long, strName As String
Dim rst As Recordset, strMsg As String
strMsg = "Invalid User ID "
'validate for non-zero value
lngID = Nz(Me![ID], 0)
If lngID = 0 Then
Me![UserName] = Null
'define recordset from Record Source of Form
Set rst = Me.RecordsetClone
'Check for ID match in recordset
rst.FindFirst "id=" & lngID
'Record found in table
If Not rst.NoMatch Then
Me![UserName] = rst!UserName
'record not found, move message into the Unbound TextBox
Me![UserName] = strMsg & lngID
Set rst = Nothing