AutoFill Forms

Question

How can I create a form in Access that automatically populates existing fields for new records based on records contained in a table? I am working on a database that includes information for hundreds of cases, including the subject's name, address, city, and state. Some subjects appear in several cases, which requires me to input their address, city, and state multiple times. I would like to create something that populates these fields automatically after adding the subject name.

Answer 1

For an illustration of how to design a database of this type correctly take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'entering data via a form/subforms' includes an orders form in which a customer is selected in a combo box, and their names and address data are then automatically shown in the form.

Answer 2

As George mentioned, your problem is improper relational database design.

You can approach this in different ways, however. You can work from the Subject table and show the cases for thaty subject, or you can enter a new case, select the Subject and DISPLAY the subject information in a subform.

Answer 3

That is, unfortunately, a result of an inappropriate table schema. You'll want to fix that rather than create a work-around.

You need a "Subject" table with this information ONLY: Firstname, LastName, address, city, and state.

You also need a "Case" table with the case information ONLY.

To link, or relate, the two tables, you need to apply relational database concepts.

The Subject table needs a Primary Key (we usually use the AutoNumber for this). Make sure you designate it as the PK.

The Case table needs its own Primary Key (again, usually the AutoNumber). It will also have a Foreign Key field. In the Foreign Key field, insert the appropriate PK for the subject. NO need to duplicate the demographic subject into again and again and again.... The PK-->FK relationship identifies that for you.

Data entry is done through a main form/sub form design.

The main form is bound to the Subject table. The sub form is bound to the Case table. There is a property for the subform control to Link Master and Child Fields to synchronize them and insert the proper PK values into the Case table records. Use SubjectID for this.

No more duplication.