Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2015
    Posts
    1

    Exclamation Unanswered: Data Valivdation from a table

    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

    ID Name
    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.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    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

    Code:
    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
       Exit Sub
    End If
    
    '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
    Else
    'record not found, move message into the Unbound TextBox
       Me![UserName] = strMsg & lngID
    End If
    
    Set rst = Nothing
    
    End Sub
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •