Txtbox ctrlsource = query question

[Compressor]

I have a form, on the form a textbox. Through the expression builder I have assigned one value to the control source of that textbox from a query. The query holds three values, all three of which I would like to use in a separate textbox. When I open the form the textbox just displays #Name. I'm guessing since nothing is happening because the query isn't run by command yet. I've used code like this:

Private Sub AppointmentID_Click()
    Forms![ClientsMain]![TechViewSummarySubForm].Form.Requery
    Forms![ClientsMain]![TechViewSummarySubForm].Form.Refresh
End Sub

to get a query to open, run etc. But that one is located in a subform which has the query as a control source. How does that work in the case I have now? Can I use the DoCmd function to run the query and get a value from it for the specific textbox? Or can't I use a (segment of a) query as the controlsource of a textbox at all?

I'm asking this since now I calculate and store certain totals by use of a textbox with =Sum() as control source in the footer of a form, which values I then assign to some fields in a table.

But I would also like to learn the right way, using queries, maybe for a later version of my DB when my skills have improved. I've been struggling with this now for a bit, but without succes. So that's why I wonder if I can assign a query or more specific one value of a couple of values in a query to a textbox in a form which has a totally different control source itself.

 

[boblarson]

See if this post might help you:
http://www.access-programmers.co.uk/forums/showthread.php?t=119465

 

[Compressor]

From that topic:

Boblarson: The controlsource for a control is meant for you to either bind it to an existing field within a table or query, or to set a formula or expression to display when a record is current on the form.

I have a table which hold sold products in a table per ID's (ClientID, SaleID and PPSaleID). Entry is done in a continuous form based on a query. Now I have created a query based on that query to sum up the totals (INVAT, EXVAT, VATAMOUNT). So I created a textbox and used the expression builder to bind it to a value in that query (=UseQueryExperiment![Sum Of TotalInVAT]). Apart from that, I haven't done anyting to it yet and I don't know if I should, but at this point the value displayed at runtime is always #Name?.

If you are using a bound control (wanting it to write to the table by use of the form's recordset) then you can't assign a controlsource other than the field that you are binding it to. If you are not binding the control (unbound control) then you can set the controlsource to be a formula or expression at design time, or you can just set the value of the control at run time.

If my understanding is correct, since that same textbox isn't bound to a field in the table, but to a field in a query, based on a query, based on a table. So that textbox won't write to a table since it isn't directly bound to a field in a table. Correct? So if I wanted to store those values anyway, I'd have to pluck them from that textbox and assign them to another textbox which IS directly linked to a table. By doing that I shoot all holy laws of normalization as I'm aware of ;-) But just to understand....

Anyway... whether I understand correctly or not.... it's not going to fill up the textbox with the value in the query ;-) Up to now I've used code like

Private Sub AppointmentID_Click()
    Forms![ClientsMain]![TechViewSummarySubForm].Form.Requery
    Forms![ClientsMain]![TechViewSummarySubForm].Form.Refresh
End Sub

To run a query that is embedded in a subform, the subform has a query as a control source in that case. But now, the form has a totally different control source. That's why I've used the expression builder to assign a query value to the textbox, since the needed value won't appear in the drop down list at the control source property field of the "new" textbox. I'm still not sure whether that is allowed or not. I guess it probably is, I don't see a reason why it shouldn't be. But now I need to run that query or requery it whenever a record is completed so the totals appear in that textbox everytime they change. What line of code do I use for that? I don't understand how to refer to it as I did when is the case when using forms based on queries.

Me.TextBox1.Requery? That'll requery but when that code is executed it will still not display a value in the textbox. I've used a SUM() in the footer of the continuous form to get the totals up to now. And I've placed me.textbox1.requery in the after update event of the textbox that holds the SUM() value to see if it checks out but still.... no dice.

 

[boblarson]

This excerpt from the help file might help you.

Example
The following example sets the ControlSource property for a text box named AddressPart to a field named City:

Forms!Customers!AddressPart.ControlSource = "City"

The next example sets the ControlSource property for a text box named Expected to the expression =Date() + 7.

Me!Expected.ControlSource = "=Date() + 7"