Data Validation

[mondeoman]

I have an data input form where the user selects an "Employer" from a combo box cboEmployer that looks up a value in the table tblEmployers. This table has four fields - 1. EmployerID, 2. Employer 3. EmployerAddress 4. EmployerCode. The user then in the next combo box cboEmployerAddress selects the Employer's address derived from the same table tblEmployers. It is possible for the user to accidentally select the wrong address. What I want is for the form to check that the address relates to the correct employer and then throws up an error message if the wrong address is selected - ie. on selecting the address the form looks at the Employer's name and confirms that the address is good.

How can I do this please.

 

[Remou]

It is possible to limit the list in the second combo to hold only addresses that relate to the employer selected (

http://www.tek-tips.com/faqs.cfm?fid=4289),

or if only one address is available per employer, to skip the second combo altogether and to update a textbox in the after update event of the first combo, if you wish it to be editable:

Me!SomeTextbox=Me.FirstCombo.Column(2)

Or simply set a textbox's Control Source to

=FirstCombo.Column(2)

If the information is available in the employer table, there is no need to store it again.

Numbering of columns starts from zero, so two, because address is the third column.

So, do you still wish to take the time to look up the table?

http://lessthandot.com

 

[mondeoman]

No I recognise now that I don't need to update the table as I can access the data which is already there. It was sort of the comfort factor of redundancy that kept me thinking that way. but clearly it makes sense to follow the normalisation rules. Thanks