Change query field based on a form

[Greyowlsl]

Hi,

I need a query's field to change to what is selected in a combo box.

Example,

I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.

I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.

Thanks for your time.

 

[Galaxiom]

An object such a the field or table cannot be treated as a parameter in a query. You will need to constuct the query in VBA.

 

[Greyowlsl]

So i found this piece of code on a forum, was wondering if someone could help explain how to use it.

Private Sub Command1_Click()
Dim strSQL As String

strSQL = "UPDATE " & Me("MyTextFieldWithTableName").Value & " SET MyFieldNames = MyNewValue"

DoCmd.RunSQL strSQL
End Sub

Table name: Table1
Form name: Form1
Combo box name: Combo1

When i select a value in combo1, the sql query creates a field in the query based on the value, not the name (eg, Name1: Field1).

Thanks for your help.

 

[Galaxiom]

strSQL = "SELECT " & Me.FieldName & " AS SomeStaticName FROM tablename"
Me.RecordSource = strSQL

Me.FieldName is the unbound control on the form used to select the fieldname to be displayed.

SomeStaticName is an alias for the field. The control that displays the values on the form uses it as a ControlSource. Otherwise the code would also have to change the ControlSource property of the control that will display the values.