Validation of the same field between different tables

[notoca]

Hi

I have two tables that make up an invoice system. One "Invoice Details" where most of the data is entered to produce an invoice. The key field here is "Invoice_No" which has a autonumber format.
The second table "Payments" is where the user makes 1 or more payments for a given invoice number. One of the main fields on the table is "Invoice_No". This links to the "Invoice_No" field in the "Invoice Details" table. On the form for the "Payments" table the user must type in the invoice number for which a payment is being made. At this stage I would like to do two thing:

1. First, validate that the invoice number being entered by the user in the "Payments" table actually exists as an invoice in the "Invoice Details" table and bring this to the attention of the user and not allow the input. Access has it's own error message but it is only triggered when the record is saved, which is too late because the user will have completed the other fields in the table by then and wasted time.

2. When the user types the invoice number on the "payments" form can information that is relevant to that invoice number be retrieved from the "Invoice Details" form and be displayed as part of the "Payments" form?

Thanks

Russell

 

[SydneyGeek]

There's a couple of different approaches that could be used.

1. Create a combo on the Payments form that uses Invoice No (from Invoice Details) as its record source. Additional wrinkle: if you have Status in the Invoice table, you can filter for Open invoices so users don't apply payments to Closed... You can set up text boxes on the Payments form that pull through data from the Invoice table, so the user sees all of the relevant invoice details.

2. Create a Payments subform on the Invoice form. Link the form and subform via the Invoice No field. That way, all the user has to do is find the invoice and enter the payment details in the subform.

3. Similar, but have the Payments form pop up when a button is pressed on the main (Invoice) form.

Which one you use depends on constraints of the layout, etc.

HTH
Denis