Text box to lookup its value form the bound table based SQL statement

[mhegazy]

I have a single form on which there exists several combo boxes and a text box. one combo box value (for the Wells) will be filled independently, then I needs the text box to have its value based on the value of the wells combo box value. I have created a query that solved the propblem partially, it requires parameter which is the wells combo box value. If I ran with query a part from the form, it wroks good and asks for the parameter and it is OK. I made use of VBA & SQL, adding a code which process a SELECT statement (of the query mentioned above), then to tell it to take its parameter from the wells combo value which will ready on the form.

Private Sub Well_ID_Change()  
Last_Ref.ControlSource = " SELECT TOP1 New_Ref FROM" & _  
" BSW_Transactions WHERE BSW_Transactions.New_Ref Is Not Null AND BSW_Transactions.Well_ID = " & Me.Well_ID.Value & _  
" ORDER BY BSW_Transactions.Sample_Date DESC"  
End Sub

the Last_Ref is the text box I want to fill in with result of the embedded SELECT statement in the code. The Well_ID is the combo box which value will be the parameter of the SELECT statement. The Well_ID is number field and it displays the well_name and stores the associated ID value in the table. Upon running the form after saving changes, the Last_Ref text box showed (#Name?). I guessed that the text box (is a number field) found a text in the combo box Well_ID, so I added ".Value" to the above syntax at the criteria Me.Well_ID. However the problem still exists.

May I mistaken in the syntax, would someone help on this. Can this works fine?

Thanks in advance.
mhegazy

[TheSmileyCoder]

Not sure I understand the entire logic behind what your trying to achieve, but for starters, I think you need to use a different event.

I would suggest using the AfterUpdate event of your combobox. Change is something you should use when you want to see and react to each individual keystroke of the user.

[twinnyfo]

Also, in line 2, it should read:

SELECT TOP 1

You forgot a space after the "TOP"

[jonnycakes]

Two ways of doing this to my knowledge, but there's probably other options..

IMHO The best way is to use the after update event of the combobox. If possible your combobox query should include all of the values you want populated in your textboxes. Then your combobox after update will read something like this:

Textbox1.value = Me!ComboboxName.columns(N)

Where the N references the column number in your query that contains the value you want to return. Typically the first column is 0. You'll need to update the combobox column property to reflect the number columns in your query.

On the other hand a much less efficient way of doing this would be to use the dlookup function in the combobox's after update.

Hope this helps.