Validation of Data

  • Thread starter Thread starter scuba1392
  • Start date Start date
S

scuba1392

I have 2 tables - one is the customer master which contains customer # (the
unique key) and the customer' name and address - the second table collects
customer input data. Users will input the customer number and I want to
ensure it is a valid number (it exists on the customer master). What coding
should I use in the "after update" event on the data entry form?
 
hi,
I have 2 tables - one is the customer master which contains customer # (the
unique key) and the customer' name and address - the second table collects
customer input data. Users will input the customer number and I want to
ensure it is a valid number (it exists on the customer master). What coding
should I use in the "after update" event on the data entry form?
Click to expand...
You don't need explicit validating in the first place.

Define a relationship between these two tables with activated
referential integrity:

http://support.microsoft.com/kb/304466


You may catch any error raised by this relationship using the forms
error event to provide the user a more meaningful message.

Otherwise, you may use the Form Before Insert and the Form Before Update
event to check the existence of the used values, e.g.

Private Sub Form_BeforeUpate(Cancel As Integer)

Cancel = IsNull( _
DLookup("CustomerID", _
"yourMasterTable", _
"CustomerID = " & txtCustomerNumber.Value))
If Cancel Then
MsgBox "Customer # doesn't exist."
End If

End Sub




mfG
--> stefan <--
 
There are over 14,000 unique customer numbers - thought that would be
excessive to put in a list and it would be easier to just take the entered
customer # and read the customer master (not certain how) to see if a match
exists or not.
 
The source of input is coming on a bar code label and we are going to attach
a scanner to the keyboard so there will be no need to key enter the data it
will just be scanned. Once the number is scanned it must be validated
against the master file. I've tried linking the 2 tables as a 1 to many
relationship with referential integrity and when an invalid number is entered
MS Access displays its own pop-up window that I have now found in the
FORMS_ERROR event and can provide my own message to the user - its works ok
BUT I can't make the MA Access pop-up stop appearing (the error number is
3201). Any suggestions ???

JimBurke via AccessMonster.com said:
As long as you have them sorted (just base the combobox on a query that sort
thems by #), they can start to type it in in the combobox and it will 'drill
down' as they type the number.
There are over 14,000 unique customer numbers - thought that would be
excessive to put in a list and it would be easier to just take the entered
customer # and read the customer master (not certain how) to see if a match
exists or not.
It seems to me that you should just create a combobox with the row source set
to the customer IDs from the customer master table. Set the Limit To List
Click to expand...
[quoted text clipped - 7 lines]
ensure it is a valid number (it exists on the customer master). What coding
should I use in the "after update" event on the data entry form?
Click to expand...
Click to expand...
Click to expand...
 
hi,
MS Access displays its own pop-up window that I have now found in the
FORMS_ERROR event and can provide my own message to the user - its works ok
BUT I can't make the MA Access pop-up stop appearing (the error number is
3201). Any suggestions ???
Click to expand...
You need to set the appropriate Response code in the Form Error event.


mfG
--> stefan <--
 
Stefan,
I changed the "response" variable to 0 and it worked just fine.
Thanks for your assistance
 
Back
Top