Self populating fields

[giocarrillo]

I am creating a customer data entry form. I would like to enter the zip code in the form and have the city and state field self populate based on a table with all the zip code data.

[Arineckaig]

I would like to enter the zip code in the form and have the city and state field self populate

Welcome to the forum, and I am sorry you have not had an earlier reply to your post.

A form and sub-form should serve your purpose, but avoid using a grid/table for either form as it can greatly complicate synchronizing the layout. The main form should have your customer table as its data-source and that table should have a zip code field that serves as the foreign key related to the other table holding all the zip code data where I presume the zip code is unique and thus can act as that table's primary key. There is no need for the customer table to have fields/columns for either the city or state, and in a relational database management system, such as Base, it is generally preferable to follow the normalization rule that in a table all columns should relate only to the primary key of the table. As you know a query can always be used to display data from more than one related table.

The sub-form should have the zip code data table as its data source and the link between the two forms should be their respective zip code fields. The sub-form need only display the city and state fields and to avoid errors these can be read only. There is no need to have a duplicate display of the zip code field in the sub-form even though that field is the link with the main form: all changes or additions will be to fields in the main form only.

The lay-out of forms and sub-forms in Base is quite flexible and it is perfectly possible for the fields from one to appear within the area of fields from the other, PROVIDED that you are not using a table/grid layout for either.

On data entry of the zip code in the main form, the sub-form will not immediately reflect the change until it has been refreshed. The simplest method is to add a "Refresh" button to the sub-form whose Action property under its General tab is set to "Refresh form". This method does require an extra action by the user, but if you wish it to be completely automatic you will need all the complications of a macro to be triggered by an updating event of the zip code field in the main form.

Later Edit: I should have mentioned that it would help entry of the zip code if it used a Combo Box in the main form. I have attached a simple example to show how the options for this Comb Box are filled from the zip code table: