Auto-populate a field on a form based on the value of another field

[mistermetaj]

Hello all,

I am new to this forum and to access. I image my question will be pretty simple to those experienced with access.

I am creating a database to store machinery assessment data. Two of the fields are associated with each other: "space name" and "space location".

The "space name" is unique, but a single "space location" can apply to multiple "space name(s)".

In my form, I have "space name" as a drop down list, and I would like "space location" to auto-populate based on the "space name" selected from the drop down.

Any help would be GREATLY APPRECIATED.

 

[pbaldy]

Does this work for you?

http://www.baldyweb.com/Autofill.htm

 

[John Big Booty]

Welcome to the forum.

Given that space name is being populated by a combo box, so long as you can make space location form part of the Row Source for your Combo you can use the following Schema (as the Control Source) to populate an unbound Text Box;

= Me.YourComboName.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x represents the column in your combo that holds the data you wish to display. Remember that the columns in a combo, or list box for that matter, are numbered from Zero on up. So if your combo has three columns (and all those columns need not be visible the code would look like;

= Me.YourComboName.Column(2)

 

[John Big Booty]

My bad; use;

= YourComboName.Column(2)

Note;
the Me. identifier is not required in this situation

 

[mistermetaj]

My bad; use;

= YourComboName.Column(2)

Note;
the Me. identifier is not required in this situation

Thanks for the help, as well as the above poster who provided the link.

Space name is working perfectly and copying over from my form to my database, however space location won't copy over to the database. How do I tell a text box what field to populate in the database?

Basically I have two databases, one just for Space Name and Space Location which is what I used to make my pull down list, and another database which I'm using the form to populate which contains fields for Space Name and Space Location

 

[pbaldy]

The second section of my link addresses your issue.

 

[mistermetaj]

The second section of my link addresses your issue.

It did address my question, however I am still not getting it to work.

I changed the formula to Me.location=Me.ComboName.Column(2)

However, it is still not saving the location over. I assume I am doing something wrong.

 

[pbaldy]

Did you change to your combo name? Is the column correct (1 would be the second)? You say "formula", but that's VBA code. Where exactly do you have it?

 

[mistermetaj]

Did you change to your combo name? Is the column correct (1 would be the second)? You say "formula", but that's VBA code. Where exactly do you have it?

My combo name is Combo355

In the location text box property sheet in the "Data" tab I have the following:

Control Source: =[location]

In the location text box property sheet in the "Event" tab I have the following:

After Update: Me.[location]=Me.[Combo355].column(1)

Is that all correct? Do I need to add something to the "Before Update"?

Thanks for all the help!

 

[pbaldy]

Doesn't sound like the code is in the right place:

http://www.baldyweb.com/FirstVBA.htm

And the control source should simply be the field name.

 

[valsanch]

Hi PBaldy,

I went ahead and read your link on auto-population in a form. I thought I followed it to a "t" but my field is not being auto-populated. I have several tables, however, just two specific to what I am trying to do. I feel so close to a solution. I have a table called, "tblActivity" and "tblSimulators". I created a form based off of "tblActivity" and I want to add a field called "Fidelity" on that form. That field is housed in "tblSimulators". I have built a relationship between the two tables. The field connecting them is called, "SimulatorType".

Initially, I had "Fidelity" on the "tblActivity" but I erased the column since that data was housed in "tblSimulators". I added a text box to my form and called it "Fidelity". In the combo box for "SimulatorType", I added the field "Fidelity" in the row source query grid. I then went to the properties for the new text box and added, "SimulatorType.[Column(1)]" to the Control Source. When I go to form view and tab through the form, I read, "#Name?" and it does not auto-populate with the corresponding fidelity of the simulator. Any ideas? Thanks!!!

 

[pbaldy]

Do you have it preceded by =?