Autofilling fields in Access form

[radline75]

I am trying to figure out how to set up a form in Access to automatically fill in 2 fields (Region # and Store Name) based on the Store number which is entered in another field.

So whenever a user types in a store number, the store name and region number fields automatically populate.

If needed, I can send a copy of the database so someone can view it and show me what I need to do. I cannot find any step-by-step instructions anywhere on how to do something like this.

Thank you for any help you can provide.

[HI2Japan]

One way would be to have the store number entered from a combo box.

Have a table of all your store numbers, locations, etc. Make a combo box based off that table and add in all the fields that are needed for the autofilling. For the properties of the combo box, only have it display the column of data for the store number. In the text boxes that you want auto filled on the form, enter this as the control source:

=[Combo Box Name].[Column](1)

or

=[Combo Box Name].[Column](2)

and so on to display the other columns of data from the combo box.

That is how I have done it in the past. Good luck.

[timothybard]

I just wanted to mention that I have used that method as well and it is what I would recommend.

[radline75]

Thank you for your reply! I tried this and everything worked perfectly. Didn't realize how easy it was. Thanks again!

One way would be to have the store number entered from a combo box.

Have a table of all your store numbers, locations, etc. Make a combo box based off that table and add in all the fields that are needed for the autofilling. For the properties of the combo box, only have it display the column of data for the store number. In the text boxes that you want auto filled on the form, enter this as the control source:

=[Combo Box Name].[Column](1)

or

=[Combo Box Name].[Column](2)

and so on to display the other columns of data from the combo box.

That is how I have done it in the past. Good luck.

[radline75]

Hey Thanks! This worked but now I have another problem as a result of this it seems. I got the information I needed to autofill into the form but now, the fields that fill in automatically on the form are not filling in to the appropriate fields in the corresponding table. Do you know how I can fix this? Do I have to do something in the table in design view? Possibly the row source? I'm not sure.

[timothybard]

Can you give more details about the location of the fields you are referring to?

From your original post, I was thinking that you had two forms:
1) Store form - This form allows you to edit the Region # and store name of a store
2) Second form - This form has a record source of a table that contains a field to track the store id. The form also has two additional fields to lookup the region # and store name from the store table.

Are you trying to update the region # and store name from the second field? If so, that won't work directly; some vba would be needed.

Are you trying to update the region # and store name from the store form? If so, then it should work; we will need to investigate further into the situation.

Lastly, if you have the second form open and then update the store info on the store form while the second form is open, then the second form will need see the updates until you close and reopen it unless you requery the form.

[radline75]

I have one data entry form. I followed the steps that were posted by "HI2Japan" by creating a combo box with 3 columns and then setting the control source of the region & store name boxes to fill in automatically from the combo box. This works. However, the information (Region # and Store name) are not getting saved in the table. I may need to merge the table into Excel at times for some additional reports so I need all fields in the table to be filled in. This would probably be easier if I could send you a copy so you could see for yourself. Let me know if that would be possible. Thanks!

Can you give more details about the location of the fields you are referring to?

From your original post, I was thinking that you had two forms:
1) Store form - This form allows you to edit the Region # and store name of a store
2) Second form - This form has a record source of a table that contains a field to track the store id. The form also has two additional fields to lookup the region # and store name from the store table.

Are you trying to update the region # and store name from the second field? If so, that won't work directly; some vba would be needed.

Are you trying to update the region # and store name from the store form? If so, then it should work; we will need to investigate further into the situation.

Lastly, if you have the second form open and then update the store info on the store form while the second form is open, then the second form will need see the updates until you close and reopen it unless you requery the form.

[timothybard]

Since the textboxes have a control source of the combobox, any changes to the textboxes will not be reflected in the table. This is because the textboxes do not have a control source of the fields in table.

Just so I have a better picture of things, can you provide me with the tables and fields that are involved? I know of the store table, which has store name and region number as fields; can you provide the other table and its fields? It will just be easier to refer to and will give me more information about your table structure.

To summarize, if the textboxes have the combobox as the control source, then you won't be able to update the textboxes; that data is viewable only. If you want to update that information, you will need to create an additional form. One option is to allow the user to double-click the textboxes, which will bring up a form to edit the store information.

[radline75]

There is only one table that is used to store all information entered on the data entry form. This table has Region #, Store #, Store Name, and 11 other fields for logging other additional information. We are using it to track reports that we receive from our stores.

So I guess there isn't a way to just change the properties in the table to pull in the region and store name info?

[timothybard]

Since you are using only one table, I didn't understand what you were trying to do.

What is the record source of the form? If you have the record source of the form set to the table, then all you need to do is change the control source of the textboxes to the fields on the table. If you do that, then whenever you change records, then the fields will show the values that are in the tables. Also, when you change the data in the textboxes, the tables will be updated.

Is that what you are trying to do?