Data From a Table

[eTom]

Here's what I'm sure is a stupid question:

I can't seem to pull data from a table for a form. I have a combobox that when a user selects an item, I want it to populate three or four text boxes based on data from a table that the user selects.

They select an item called "Product123" from the combobox. This combobox is populated from a table called tblProducts, set up to use two columns, ItemNumber and ItemDescription.

I've set up an OnChange event that triggers when the user picks an item. To test it out, I tried:

Me.TextBox1.Value = "11111"

This verified that the code and event are working properly. Now what I want to do, ideally, is have TextBox1 populated with the value for that specific record from the table, from a field called ProductSpecs1. How do I reference the table (tblProducts) in my line of code?

When pulling data from another form or subform, I've always used Forms!Formname.Field.Value and this has worked fine... but I just can't figure out how to pull the value from a field from a record from a table. ><

Thanks in advance,

eTom

 

[ions]

Etom,

Probably the easiest way to do it, would be to include the fields you want in the combo's row source query. Hence create a relationship from the products table to the second table that has the fields you want and add the desired fields in the query's design.

I am not sure why you are using the OnChange Event. I usually use the AfterUpdate event.

In the afterupdate event you can get the desired values by using this syntax:

Me.TextBox1.Value = Me.Mycombo.Column(2) 'Make Column 2 in your combo's row source be ProductSpecs1

Note that Column(0) in your combo's row source is ItemNumber and Column (1) is ItemDescription.

 

[eTom]

Etom,

I am not sure why you are using the OnChange Event. I usually use the AfterUpdate event.

In the afterupdate event you can get the desired values by using this syntax:

Me.TextBox1.Value = Me.Mycombo.Column(2) 'Make Column 2 in your combo's row source be ProductSpecs1

Absolutely brilliant, thanks! I'm using an OnChange because it seemed like the best choice. I tend to pick the wrong product, or type it in to avoid using the mouse. OnChange will update the fields every time it's changed to a different product, right?

 

[ions]

OnChange fires too often. It fires everytime you type a Character.

Afterupdate will fire when you exit the combo box.