Autofill form fields

[rbertram]

Is there a way to automatically fill form fields based on the value of another field?

This is what I am trying to do. I have a customers table with the customer ID as the PK and I have a products table with the product ID, revision number, and the customer ID as CKs. The customer ID is part of the key because there are small chances that different customers may have the same part number.

What I am trying to do is in the "add products" form, when a customer ID is entered, the customer name automatically displays on the form. This is just for usability purposes. I know how to add it to a report. I do not want to have it as a field in the products table to eliminate redundant data.

 

[willir]

rbertram

There are several solutions to your problem. I would use a subform with the customer information. But you can also use an unbound field on th emain form too.

For the After Update event when the product number is entered, you need to extract the customer number.

If the product table includes the foreign key for the customer table, then once the product has been selected, the customer info becomes available. You can use a combo box or text field to display this information and then use it to link to the subform.

If the only way to get the foreign key to the customer table is from the product code, then you have to be a little inventive.

I am going to assume the the customer code is the first six characters of the product code, and the product code field is called ProductCode.

Create an unbound text field on the main form; call it CustomerCode, and make it invisible.

For the after update event for the product code, have...

If not isnull(me.ProductCode) then
Me.CustomerCode = LEFT(me.ProductCode, 6)
End if

And the use the extracted customer code to link to the sub form.

Richard