Text box to have its value based on combo box value using a query

Question

I have a single form on which there exists several combo boxes and 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 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 do think to make use of VBA, 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.

Can someone help on this. Can this works as I descriped.

Thanks in advance.

Mohamed

Further to my question above, I have tried the following solution:

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. Mohamed

Top 1 can return more that one record when there are matches in the order. For this to work properly, you cannot have more than one person on the system, otherwise Person B could put in a new transaction while Person A is working. I suspect there may be a design problem. — Fionnuala

Matt Donnan Matt Donnan · Accepted Answer · 2012-08-23T07:58:18

The problem is that text boxes cannot have their control source set like a combo box or a list box, it needs to be passed the value e.g:

Private Sub Well_ID_Change()

Dim rs As Recordset    

Set rs = CurrentDb.OpenRecordset("SELECT TOP1 [New_Ref] FROM" & _
" [BSW_Transactions] WHERE [BSW_Transactions].[New_Ref] Is Not Null" & _
" AND [BSW_Transactions].[Well_ID] = " & Me.Well_ID & _
" ORDER BY [BSW_Transactions].[Sample_Date] DESC", dbOpenSnapShot)

rs.MoveFirst
Last_Ref = rs![New_Ref]
rs.Close
Set rs = Nothing

End Sub

I've also dropped the .Value again from your combo box as its not required

Text box to have its value based on combo box value using a query

1 Answers