Control Source = to Query Value

[Guest]

I am trying to set the control source property for a text box to equal the
value from a query. The way it works is if I select a value in my form, the
query finds the Max(Total) for rows containing that value selected. After I
select that value I want the Max value to appear in a text box in the same
form. When I set the Control Source to equal the query value, all I get is
#Name?. Any idea how to fix this problem?

Thanks,
Matt

 

[Ken Snell [MVP]]

Try using the DLookup function to get the desired value from the query.

 

[Todd Shillam]

Matt,

First, don't change the control source--this should be set to your appropriate table and field whereas this is where the data will be stored. Start by creating a query, then bound your query to the form.

To bound your query to a form, open your chosen form. With the form open (loaded), open your chosen query in design mode. Then find the query field(s) you want to bind to the form objects--right click the 'Criteria' box and click the 'Build' option in the context menu--this will open the Expression Builder window.

In the left pane of the Expression Builder window, expand the 'Forms' object, then expand the 'Loaded Forms' object, then click the form you want to bound your query too, click your field in the middle pane, followed by the field name in the right pane. Here's a Flash video example:

http://www.download.com/Notepad-/3000-2352_4-10328008.html?tag=lst-2-22

You can use DLookup to fetch values from the query. For example, if you have a textbox that you want to fetch a value from a query, using the 'OnGotFocus' event procedure:


Dim vTemp As String 'Declaring variable here

vTemp = DLookup("FieldNameHere","qryNameHere") '

Me![MyTextBox] = vTemp

Note: The DLookup method will fetch the first value in the query if there is a record found; thus, the more fields you bound to the query, the more the search results are filtered based on your form. Also, be sure to address sorting, ascending and descending order in your query fields. For example, if you had a Total field you wanted to fetch the greatest value, sort the query field in descending order.

--
Best regards,

Todd Shillam
Information Technology Consultant
Shillam Technology
http://shillamtechnology.point2this.com


I am trying to set the control source property for a text box to equal the
value from a query. The way it works is if I select a value in my form, the
query finds the Max(Total) for rows containing that value selected. After I
select that value I want the Max value to appear in a text box in the same
form. When I set the Control Source to equal the query value, all I get is
#Name?. Any idea how to fix this problem?

Thanks,
Matt

 

[Todd Shillam]

Opps--wrong link! I am just waking up. Here's the correct link for the Flash example:

http://sthosting.zapto.org/Technology/BoundQuery.html

--
Best regards,

Todd

Matt,

First, don't change the control source--this should be set to your appropriate table and field whereas this is where the data will be stored. Start by creating a query, then bound your query to the form.

To bound your query to a form, open your chosen form. With the form open (loaded), open your chosen query in design mode. Then find the query field(s) you want to bind to the form objects--right click the 'Criteria' box and click the 'Build' option in the context menu--this will open the Expression Builder window.

In the left pane of the Expression Builder window, expand the 'Forms' object, then expand the 'Loaded Forms' object, then click the form you want to bound your query too, click your field in the middle pane, followed by the field name in the right pane. Here's a Flash video example:

http://www.download.com/Notepad-/3000-2352_4-10328008.html?tag=lst-2-22

You can use DLookup to fetch values from the query. For example, if you have a textbox that you want to fetch a value from a query, using the 'OnGotFocus' event procedure:


Dim vTemp As String 'Declaring variable here

vTemp = DLookup("FieldNameHere","qryNameHere") '

Me![MyTextBox] = vTemp

Note: The DLookup method will fetch the first value in the query if there is a record found; thus, the more fields you bound to the query, the more the search results are filtered based on your form. Also, be sure to address sorting, ascending and descending order in your query fields. For example, if you had a Total field you wanted to fetch the greatest value, sort the query field in descending order.

--
Best regards,

Todd Shillam
Information Technology Consultant
Shillam Technology
http://shillamtechnology.point2this.com

<snip>