Auto-Population Of Controls On A Form.

This database opens a form that demonstrates 3 ways to auto-populate controls on a Form. Since help with auto-populating controls is often requested this database is meant to provide examples of how this can be done.  

Thexe examples show how to display information from another table in controls on a Form.  Except for the PostalCodeID combo box, the other controls to be populated are for information purposes only and not for entering data.  These controls are locked so data cannot be entered in them manually.  

These auto-population examples depend on the PostalCodeID which is a Foreign Key in the tblCustomers table.  There is a one-to-many Relationship with tblPostalCode on the "one" side of the Relationship and tblCustomers on the "many" side.  Referential Integrety is used. 

The PostalCode ID instead of the PostalCode itself is used as the primary key in that table because a Postal Code can include more than one city or town.


AUTO-POPULATION BY QUERY
This example uses the Form's RecordSource query to populate the controls.  In this example, the controls to be populated are bound, meaning the control source is set to a field supplied by the query.

Care must be used in designing the RecordSource query so that it will be updatable.  If the Auto-population by Query technique is used both the table relationships and the Query design are crucial.  The example should be carefully followed as a guide.

The PostalCodeID combo box is used to select the Foreign Key to be saved in the tblCustomers table.  Access will use the Form's RecordSource Query to automatically fill in the information in the controls. 


AUTO-POPULATION BY PUSH
This example fills the controls by code which gets data from the PostalCodeID Combo box Columns.  The controls are unbound except for the Postal Code Combo box.  They are populated by code in the Postal Code combo box's AfterUpdate Event and the Form's Current Event.  The code should be carefully studied to understand this teqnique. 

This Combo box is used to add the PostalCodeID to the tblCustomers via the form.  The first Column's Width Property is set to 0". The column's value still exists, but it cannot be seen. 

Combo box column numbers are 0 based so the first column is Column(0), the second is Column(1), etc.  The number of columns and their values are determined by the combo box RowSource Query.  


AUTO-POPULATION BY PULL
This example also fills the controls by using the data from the PostalCodeID Combo box Columns.  However, no code is used.  The ControlSource of each control "pulls" the data from the Combo box.  For example, the txtCity Textbox RecordSource is "=[cbPostalCodeID].[Column](2)" without the quotes.  Since Combo box Columns are zero based this control is pulling the data from the third column.

Feedback on how to improve this database is welcome.  

Patrick Wood
pwood57@gmail.com
http://gainingaccess.net
