synchronizing one text field based on the selection of a drop down list

[DBlearner]

Hello folks,

I'm building a nutrition database to keep track of my eating habit. I know there's ton out there but I want to do it on my own you know?

Anyway there's a tiny issue that I cant seem to find the answer anywhere. Google is great but I cant seem to figure out the right key word to use to find the answer. Argh!

Ok, I have a table [food_by_calorie s] that has two columns which are [food] and [calories].

an example would be like this

[Food]..............[Calories]
Hamburger...... 350
Banana......... ...40

Now, I have a look-up drop down menu that goes to the table and I can select any from the first column with no problem. I also have a text field right next to it that I want to automatically display calories depending on which one I select in the drop down menu.

Is there a way to do this?

Thx

[NeoPa]

Certainly. No problem.

Look at the .Column() property of the ComboBox control.

Let us know how you get on :)

[DBlearner]

Certainly. No problem.

Look at the .Column() property of the ComboBox control.

Let us know how you get on :)

I looked at the combo Box property sheet and learned everything about column count and Bound Column. Dont see how it can help me in this situation.

I am trying to figure out how to get the text field to automatically select the corresponding data in the second column depending on the selection in the drop down field.

Example:

I would select Hamburger in the combo box and then the text field would check the table and see that the calories for hamburger is 300 and inserted it in the text field.

[NeoPa]

They can't. It was the .Column() property I was referring to. It is accessed in VBA and gives you control, not only over which column is returned, but also which row if the currently selected row is not what you need.

As I say, I suggest you read up on that as I'm sure it completely fulfills your needs.

[ChipR]

You will need to use the After_Update event of the combo box control to change the text box.

[NeoPa]

If it's a bound control you will also need to run the same code from the Form_Current() event procedure.

That one is often overlooked.

[DBlearner]

VBA? I have zero understanding of VBA. I searched .column in Access help and only presented info on column in property sheet.

Do you know of a website focused on VBA for novices?

[NeoPa]

Try Microsoft Access Tutorial & VBA Tutorial (Excel).

I can't vouch for them particularly, but they seem to be decent places to start at least.

[DBlearner]

I'm back. I got a Access 2007 VBA book and learned a lot. So here what I did! For the most part it worked except for the very last part ;-(

I created a table titled [DoDAAC] that had three field names which are [ID] [CMO_Name] [DoDAAC1].

I also created a query titled [DoDAAC Query] that includes all fields from the DoDAAC table above.

I went into the main table titled [Data Collection] and created two field names which are [CMO Name] and [DoDAAC]. I set [CMO Name] to look up the DODAAC table.

SELECT [DoDAAC Query].ID, [DoDAAC Query].CMO_Name, [DoDAAC Query].DoDAAC_ FROM [DoDAAC Query] ORDER BY [DoDAAC Query].DoDAAC_;

The [DoDAAC1] field is just a text box.

I now created a query [Data Collection Query] which contains all fields from the [Data Collection] table

Now, I also created a CAR form and I added the two fields [CMO Name] and [DoDACC] from the Data Collection Query. The CMO Name field is a combo box and it works beautifully.

The text box [DoDAAC] is supposed to automatically fill its field depending on the Combo box's selection. So I went into the Text box's property sheet and modified the control source

=[CMO Name].column(3)

In the CAR form, the data would be filled into the text box as expected but refuse to store the data in a field which is [DoDAAC] for later use.

I also tried this

[DoDAAC]=[CMO Name].column(3)

So I tried it via VBA and it still wont work.

I am overlooking something and I have been spending more than 10 hours on this. I admit I need some help.

Matt

[DBlearner]

Can anyone point me in the right direction? is there a certain method of doing this that I overlooked?

Any help would be greatly appreciated and thank you in advance.

Matt

[ChipR]

The text box will only be updated based on the control source at certain times, like on load or current record. If you want to update the text box at other times, you will need to put code in to do so. To change the text box when the combo box changes, you would (still) use the AfterUpdate event of the combo box.

[NeoPa]

So I tried it via VBA and it still wont work.

I think Chip is on the right lines here, but you give no details of the VBA you've tried so we cannot be more specific or tell you where you've gone wrong.

You've given us lots of detail elsewhere, but none of the stuff that relates to any VBA code which is where we feel the confusion is.