Microsoft Access question

Question

I have a combo box which has 3 pieces of information

COMBO BOX 210: Materials ID, Name of Product, Cost, Description

After I update the combo box the cost figure is stored in another field in the table, so far so good, so in selecting "Apples" the cost "$1" stores.

Now, what I want to is have a different field where the description of the apple (the text) is stored yet I only update the combo box once, in other words after updating the combo box Field 1 stores the price and Field 2 the description of the apple.

HansUp HansUp · Accepted Answer · 2011-06-09T16:37:56

I will offer you a different example which I hope is similar to what you're asking.

My form includes a combo named cboNames. This is the query for its row source:

SELECT f.id, f.fname, f.date_added
FROM food_test AS f
ORDER BY f.fname, f.date_added;

In the combo's After Update event I can access the values of those 3 columns in the selected combo row by referring to the Column index. Notice the column index numbering starts with zero.

Private Sub cboNames_AfterUpdate()
    Debug.Print Me.cboNames.Column(0)
    Debug.Print Me.cboNames.Column(1)
    Debug.Print Me.cboNames.Column(2)
End Sub

So if I wanted to put the date_added value into another data control, I could add this to the combo's After Update event.

Me.SomeOtherControl = Me.cboNames.Colummn(2)

However I wouldn't actually store both id and date_added in a row of another table. In my food table, each id is associated with a unique combination of fname and date_added. So I would store only the id, and use a SELECT with a JOIN to the food table to look up the associated fname and date_added values whenever I needed them.

Microsoft Access question

1 Answers