combo boxes - query

[rochak]

Hi, I have a problem regarding combo boxes. I want to add a combo box to a form such that when any item on it is selected the remaining text fields on the form are automically filled from a table depending upon the selected item.

For example, if the combo box has a list of products. When the user selects a particular product and presses go, the text fields on the same form are filled up with the product details (by looking up a table based on the selected product). I am a looking for a solution without the use of subforms.

Thanks

[nagasrikanth]

i think u can use a sql query to get all the required data...
but...
chk the attachment...this is the most easiest way ..I belive...

All the best

[rochak]

Thanks a lot Sri, I got the idea through your attachment. Can you also perhaps tell me how can I use a query for the solution. Thanks

[nagasrikanth]

Private Sub Combo0_AfterUpdate()
Dim sql As String
Dim rst As Recordset
sql = "Select empname from table1 where empno=" & Me.Combo0.Value & ";"
Set rst = CurrentDb.OpenRecordset(sql)
If Not rst.RecordCount = 0 Then
    Me.Text4 = rst!empname
End If
End Sub

chk out this...but..comparitivly this code will take more time than previous one..

[rochak]

thanks sri, you were great

[Ecniv]

Optimisation hint:
If you run a query to fill the list box, then fill it with the other data you want to display on the form, but in hidden columns (column widths of 0).
Then as the user selects the item from the dropdown, you use after update or click events to take the hidden columns data of the selected item and put it into the text box fields on the form. Makes it look faster on seleccting, but a little slower (negligable) on filling the dropdown.