Setting text box equal to SQL Query

[Guest]

This should be simple but so far is problematic. I'm coding a form with a
combo box that allows a user to select a value that then opens a list box
with the values. Essentially cboKey1 is set to be the value I want the user
to select. This populates lstRecipe by using a SQL query. All this works
fine. My problem is that I also want to total a single column in lstRecipe
to verify that it adds up to 100. I can do this with combo and list boxes
but how do I set a text box = SQL query? Whenever I use Me.txtTest = "SELECT
Count(*) blah blah blah" it does nothing. I have run into this in the past
and just used combo/list boxes where I can mod the RowSource...but isn't
there a way to do this with text boxes too?

 

[Roger Carlson]

You can use a Domain Aggregate function. Put something like this in the
Control Source:
=DCount("aField", "aTable", "<some criteria>")

Specific example
=DCount("PatientID", "tblPatient", "[DischargeDate] > #1/1/2006#"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J. Steele]

Note that you can use a query rather than a table in DCount, if that helps.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You can use a Domain Aggregate function. Put something like this in the
Control Source:
=DCount("aField", "aTable", "<some criteria>")

Specific example
=DCount("PatientID", "tblPatient", "[DischargeDate] > #1/1/2006#"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This should be simple but so far is problematic. I'm coding a form with
a
combo box that allows a user to select a value that then opens a list box
with the values. Essentially cboKey1 is set to be the value I want the user
to select. This populates lstRecipe by using a SQL query. All this
works
fine. My problem is that I also want to total a single column in lstRecipe
to verify that it adds up to 100. I can do this with combo and list
boxes
but how do I set a text box = SQL query? Whenever I use Me.txtTest = "SELECT
Count(*) blah blah blah" it does nothing. I have run into this in the past
and just used combo/list boxes where I can mod the RowSource...but isn't
there a way to do this with text boxes too?

 

[fredg]

This should be simple but so far is problematic. I'm coding a form with a
combo box that allows a user to select a value that then opens a list box
with the values. Essentially cboKey1 is set to be the value I want the user
to select. This populates lstRecipe by using a SQL query. All this works
fine. My problem is that I also want to total a single column in lstRecipe
to verify that it adds up to 100. I can do this with combo and list boxes
but how do I set a text box = SQL query? Whenever I use Me.txtTest = "SELECT
Count(*) blah blah blah" it does nothing. I have run into this in the past
and just used combo/list boxes where I can mod the RowSource...but isn't
there a way to do this with text boxes too?

you cannot set the control source of a control to a SQL.
You can use DLookUp to look up the result that the query has
calculated.

If the query returns just one record value:
=DLookUp("[FieldName]","QueryName")

If the query returns many records:
=DLookUp("[FieldName]","QueryName","[SomeField] = " & SomeCriteria)

As the syntax for the criteria varies according to the datatype of the
criteria field, look up:
Restrict data to a subset of records
in VBA help.