By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

validate if entry is in the master table

P: 30
How can I validate the entry in a form, say cutomer number, is in the masterlist table, else do not accept entry?
Dec 5 '07 #1
Share this Question
Share on Google+
3 Replies


100+
P: 675
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.
Expand|Select|Wrap|Line Numbers
  1. Private Function IsValidEntry(strEntryToValidate As String) As Boolean
  2.     If Trim(strEntryToValidate) = "" Then
  3.         'Entry is Blank[s]
  4.         IsValidEntry = False
  5.     ElseIf Not IsNumeric(strEntryToValidate) Then
  6.         'Entry is not Numeric
  7.         IsValidEntry = False
  8.     Else
  9.         'Entry meets conditions of Customer Number 
  10.         'Create SQL for Workspace
  11.         lbxWorkspace.RowSource = "SELECT Table1.Number " & _
  12.                 "FROM Table1 " & _
  13.                 "WHERE (((Table1.Number)=" & txtEnter.Text & "));"
  14.         If lbxWorkspace.ListCount = 0 Then
  15.             'ListBox is empty, therefore the Customer Number does not exist
  16.             IsValidEntry = False
  17.         Else
  18.             'ListBox is not empty, therefore the Customer Number is valid
  19.             IsValidEntry = True
  20.         End If
  21.     End If
  22. End Function
  23.  
  24. Private Sub txtEnter_Exit(Cancel As Integer)
  25.     Debug.Print IsValidEntry(Nz(txtEnter, ""))
  26. End Sub
  27.  
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
Dec 6 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
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
Dec 6 '07 #3

P: 30
Thank you guys for the help.
Dec 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.