How do I create an autofill section of MS Access 2007?

Question

When I choose a customer's name from a drop down menu it pulls it from the "Customer" table to enter it into the existing form. I want the other information related to that customer from the "Customer" table to automatically be entered into the new form as well. Also, how do I enable it to let new customers be added in the form and update the original "Customer" table as well?

Thanks for your help.

Answer 1

Create a query with 2 fields: the primary key CustomerNo and customer LastName: call it qrySelectCustomer;

Rename the textbox with the Primary key txtCustomerNo

In your form add a combo box : call it cboSelectCustomer: use the query qrySelectCustomer as its Row Source

Now create a macro, save As mcrSelectCustomer

Select Action GoToControl and enter txtCustomerNo into the Control Name argument field

Select Action FindRecord and enter =[cboSelectCustomer] into the Find What argument field.

Back to the combo box: change the following properties

Column Count: 1

Column Widths 0,2

AfterUpdate event: select mcrSelectCustomer

======================================...

To update the query when you add a new record, and also to synchronise the combo box with the current record, create a new macro, call it mcrOnCurrent:

Select Action Requery and enter cboSelectCustomer in the Control Name argument field.

Select Action  Setvalue and enter cboSelectCustomer in the Item field and

=[txtCustomerNo] in the expression field

Save and assign this macro to the OnCurrent event of the Form.