ControlSource text box form, and query

[Jose]

Hello,
I have 7 queries with the same information but the fields have different names. ( Info comes from different systems and the names of the fields are different).
For instance,
In query1 I have a field called Prices
In query2 I have a field called UPrices
In query3 I have a field called DPrices

I would like to be able to create a form to see the 7 queries ( one at each time)


My form is called Test2 and one of the txtboxes is called Pricestxt. I have a command buttom in another form that opens Test2 in this way.


DoCmd.OpenForm "Test2"
Forms!Test2.RecordSource = "query2"
Forms!Test2!Pricetxt.ControlSource = [Query]![Query4]![Uprice]
End Sub


I would like to pass the field"Uprice" from query2 to Pricestxt. so I can see the info in the form. I could use the same solution to upload the other queries in the form so I just have one form for 7 queries.

At the moment I get an error in the above code.

Can someone help, please?

thanks in advance

J

 

[apr pillai]

There is an Error in the following line:

Forms!Test2!Pricetxt.ControlSource = [Query]![Query4]![Uprice]

You are trying to set the ControlSource property value to the [Uprice] field value like: Forms!Test2!Pricetxt.ControlSource = 123.50 (even though [Query]![Query4]![Uprice] is not a valid reference to the [Uprice] column of the Query).

Change it to:

Forms!Test2!Pricetxt.ControlSource = "Uprice"

The best solution is to send the Query Name & Control Source name as Open Argument to the Docmd

In that case the Form Open statement will look like below:

DoCmd.OpenForm "Test2",acNormal,,,,,"[B]Query2,UPrice[/B]"

The Query name and the Control Source Field name both together passed as Open Argument (separated by a comma as delimiter) to the OpenForm Command. The QueryName and FieldName can be split into two with the Split() Function in the Form Open Event procedure and set the values to the Record Source and Control Source properties as given below:

Private Sub Form_Open(Cancel As Integer) 
Dim x
x = Split(Me.OpenArgs,",")
Forms!Test2.RecordSource = x(0) ' Query name 
Forms!Test2!Pricetxt.ControlSource = x(1) ' UPrice here
End  Sub

The advantage of the above method is that you need to make changes in the DoCmd.OpenForm statement only to pass different Query Name and Control Source Name to the Form_Open() Event Procedure. No need to make any changes in the Form_Open() Event Procedure itself.

 

[Jose]

Thank you very much