CODEWITHSUNDEEP
vbams-access
LOZ
LOZ

Reputation: 1177

VB-MS Access Validation for textbox

I have the following vb code setup on one of my forms. I setup a text box where a user can search by a CN number but I am running into an issue. If the user does not enter anything in the textbox and they click search, they are prompted to enter a CN# (Which is what I want). If the user enters a CN #, my query will find that CN# with its detail (Which is what I want). Where my issue lies is if the user types in a incorrect CN#, the query will still pop up, but the query table is empty. I am trying to setup a validation field that restricts what can be typed in or lets the user know that the CN# they typed is incorrect.

Private Sub cmdSearch_Click()
    If Nz(txtCN, "") <> "" Then
         DoCmd.OpenQuery "querySearchCN_CE", acViewNormal, acReadOnly
        'DoCmd.OpenQuery "query_CO_CE", acViewNormal, acReadOnly
        'DoCmd.OpenQuery "querySearchCN_2010_2015_CE", acViewNormal, acReadOnly
        'DoCmd.OpenQuery "querySearchCN_2016_CE", acViewNormal, acReadOnly
    Else
       'If Nz(txtReportDate, "") = "" Then
        MsgBox "NOTICE! Please enter a CN #"
        Exit Sub
    End If
End Sub

Upvotes: 0

Views: 408

Answers (1)

Steve W
Steve W

Reputation: 476

You can add a DCOUNT function to test the input text before proceeding with your code. If the count returns 0 then you can assume there are no records that meet the criteria entered.

Private Sub cmdSearch_Click()
    If Nz(txtCN, "") <> "" Then

        If DCount("fldField", "tblTable", "fldField = " & txtCN) > 0 Then
             DoCmd.OpenQuery "querySearchCN_CE", acViewNormal, acReadOnly
            'DoCmd.OpenQuery "query_CO_CE", acViewNormal, acReadOnly
            'DoCmd.OpenQuery "querySearchCN_2010_2015_CE", acViewNormal, acReadOnly
            'DoCmd.OpenQuery "querySearchCN_2016_CE", acViewNormal, acReadOnly
        Else
            MsgBox "You have entered an invalid CN #"
        End If
    Else
       'If Nz(txtReportDate, "") = "" Then
        MsgBox "NOTICE! Please enter a CN #"
        Exit Sub
    End If
End Sub

Upvotes: 0

Related Questions