But, the best part is that the data population is instantaneous and it required no coding at all. When you get to this point, you can decide to either allow the users to modify the customer detail fields, or set the Enabled property to False which will make those fields read-only.

Of course, I modified it slightly to make it more readable and included two buttons on the form. Microsoft Access automatically creates all of the fields from the query and gives me a fairly nice design.

Clicking the Form button in the Forms group.

Select the new query in the Navigation Pane.

I can do this very easily by using these steps: The final step is to build a form using my new query as the data source. This tells me that it’s an Autofill query and I can set the properties to hide it from my users. In my case, I named the query ‘qryAutofillCustomerOrder’. When you save the query, I recommend you give it a meaningful name preferably using the ‘qry’ prefix. If you select a different Customer ID while viewing the dataset, Access will automatically populate the associated fields with the associated data. The reason I selected the Customer ID from the Orders table is because my underlying table has already determined that the field is a Lookup and the combo box is already created and populated for each record. When you execute the query, the dataset will show all of the orders along with the Customer detail. You can make changes in the original table and the linked table that will create changes in both Access databases. This capability can increase your efficiency and easily spread information that is required for multiple departments or reports. Once complete, I will return to the Orders table and select the remaining fields I wish to display on my form. Microsoft Access allows tables and databases to connect with each other. This is where you can select as much (or as little) from the Customers table as you wish to display. Then, I want to include the Customer data to go along with the selected CustomerID. Since my form is going to be used to create a new Order, I will first include the Order ID and CustomerID from the Orders table. I want the form to automatically populate all of the contact information from the Customers table when the user selects the customer in the combo box. In this example, I will use the Customers and Orders tables. When designing your multi-table Select query, you must plan the fields you wish to include in the resulting dataset. Of course there are certain exceptions to this rule, but for the majority of Select queries, this principle applies.įurthermore, this is why queries and tables cannot have the same name. Once the change is posted, the underlying data in the source table is updated. After a select query is executed, the user can modify the results as if it were a table. Basically, the results of a Select query are treated no differently than a Table.

To make this technique work, you must first understand how Access treats the results of a Select query. Binding the form to a query instead of a table.To successfully complete this project, we will use two fundamental features built into Microsoft Access:

Ms access linked tables