How to get instructions to display in text box when I select the model?

[Louise Forsey]

I'm having a complete headache over how i get something i want to display in a text box to be a result of something i've selected in the combo box???

Very simple database, all it does is:

Select the "Brand" in the first combo box

Which then filters the second combo box to only show the "Product" types for that brand.

Next when the "Product" is selected in the combo box I want it display the "Instructions" for that product in a text box ("Instructions" is on the same line in my table).

I can get the text box to display "ProductID" or same result as the product selected and thats it!!!

Please help!!
This is a favour for some people i used to work with. I'm only used to producing form designs etc. so I'm way out of my deph here.

Here's some of my VB code if that helps:

Private Sub CategoryID_Change()
 
    ProductID.Value = Null
    ProductID.Requery
 
End Sub
 
Private Sub Detail_Click()
 
End Sub
 
Private Sub Form_Current()
 
    ProductID.Requery
 
End Sub
 
Private Sub ProductID_AfterUpdate()
Instructions = DLookup("Instructions", "Products", "ProductID=" & ProductID)
 
End Sub
 
Private Sub Text5_BeforeUpdate(Cancel As Integer)
 
End Sub
 
Private Sub Text9_AfterUpdate()
Instructions = DLookup("Instructions", "Products", "ProductID=" & ProductID)
 
End Sub

Many Thanks
Bunny

[NeoPa]

What you need is to have a multi-column ComboBox, but set it so that only the column indicating the product itself is showing.

When that's set up you want a ComboBox_AfterUpdate() event procedure, which will run any time the selection is changed, to get the hidden value and assign it to the TextBox.

Does that sound like something you could work with? If there are any steps you need further detail on just specify and I'm sure we can help further (though for more detail we'd appreciate the names of the objects we'd have to work with).

[munkee]

I just thought I would comment here. I love the approach NeoPa. In my narrow view I would have just used a select case in vba to update the textbox with a value dependent on what value is selected in the combo. However, your option gives the possibility to upsize very easily without having to change any coding.

Good fix!

[NeoPa]

Thanks Munkee :-)

Here's another very interesting way to do it. The Help system says it's not supported, and when you enter it there is an error message saying the same. Nveretheless it seems to work. Try it.

This example code assumes you have a ComboBox called [ProductID] and it has three columns.

1. The ID itself. Hidden.
2. The product name of some sort. Showing as the only visible column.
3. The Instructions you need displayed in the TextBox. Also hidden.

=[ProductID].[Column](2)