validate if entry is in the master table

[ebo2006]

How can I validate the entry in a form, say cutomer number, is in the masterlist table, else do not accept entry?

[OldBirdman]

One very simple way to answer many questions about your tables and entries within the tables is to create a ListBox (Example uses lbxWorkspace) on your form, and set .Visible = False. This becomes your test/workspace. Example is using a TextBox named txtEnter to enter the customer number.

Create a function, say IsValidEntry to use this workspace and validate the entry. Here I assume that the customer number must be numeric, and test for non-numeric entries before the query.

Private Function IsValidEntry(strEntryToValidate As String) As Boolean
    If Trim(strEntryToValidate) = "" Then
        'Entry is Blank[s]
        IsValidEntry = False
    ElseIf Not IsNumeric(strEntryToValidate) Then
        'Entry is not Numeric
        IsValidEntry = False
    Else
        'Entry meets conditions of Customer Number 
        'Create SQL for Workspace
        lbxWorkspace.RowSource = "SELECT Table1.Number " & _
                "FROM Table1 " & _
                "WHERE (((Table1.Number)=" & txtEnter.Text & "));"
        If lbxWorkspace.ListCount = 0 Then
            'ListBox is empty, therefore the Customer Number does not exist
            IsValidEntry = False
        Else
            'ListBox is not empty, therefore the Customer Number is valid
            IsValidEntry = True
        End If
    End If
End Function
 
Private Sub txtEnter_Exit(Cancel As Integer)
    Debug.Print IsValidEntry(Nz(txtEnter, ""))
End Sub
 

For test purposes, I'm using a TextBox named txtEnter to enter the Customer Number , and testing for validity On Exit. Testing On BeforeUpdate would also be appropriate, but if Customer Number doesn't exist, why wait to inform user?
Note that you could use the same workspace to count the number of orders by this customer in the last year, and offer discounts, etc.

I hope this helps.

OldBirdman

[puppydogbuddy]

How can I validate the entry in a form, say cutomer number, is in the masterlist table, else do not accept entry?

One way is to do a Count of records returned on a DLookup as shown below:

If Count(DLookup("[CustomerID]", "tblCustomer", "CustomerID = " & Me![CustomerID]) > 0 then
' customer exists - accept the entry
Else
' customer does not exist on the table - reject the entry
End If

[ebo2006]

Thank you guys for the help.