Displaying results of SQL query on form

[philvalko]

I searched the archives and Access Help and couldn't quite find the answer to this question, which is no doubt simple.

I have a form showing Projects with an embedded subform showing all the Buildings associated with a given project. In the form footer section of the subform, I would like to display summary information about the buildings in the project being displayed, such as count of buildings, count of residential buildings, count of commercial buildings, and so on, and I would like these summary values to update as users add or change building information.

I know how to write the SQL query to pull this information, but do I use a text box or some other vehicle to do this? Using a text box as a calculated control, I was able to produce the count of buildings using:

= COUNT([UnitID])

To get the count of residential buildings, I need to be able to specify "WHERE bldgtypeID = 'R'"...I can't get the text box to display results of an SQL statement. Can text boxes handle SQL statements?

I really appreciate any suggestions you have.

 

[mhartman]

Hello:

Yes: Text boxes handle SQL statements. You want to place the statement in the "ControlSource" property of your text box.

Regards
Mark

 

[philvalko]

That is what I have been attempting with no luck. Here is exacty what I have entered in the ControlSource of the text box:
=(SELECT Count([tblAllUnits].[UnitID]) FROM tblAllUnits WHERE [tblAllUnits].[UnitTypeID]="R")

If I create an independent query with this select statement, it returns a count of ALL the units in our portfolio. Perhaps the issue is that the text box is in a subform. Is there a way to include a clause in the SQL statement such as: WHERE tblAllUnits.ProjectID = (look up the current project being displayed)?

Thanks for your help

 

[mhartman]

Hello:
Below would be an example using a criteria that is on a form:
SELECT tblStaff.StaffID, tblStaff.FirstName, tblStaff.LastName, tblStaff.Address, tblStaff.City, tblStaff.State
FROM tblStaff
WHERE (((tblStaff.State)=[Forms]![frmForm1]![txtState]));

Please see my example db enclosed below. The form must be open first, then run the query. Notice that only records with a State of "CA" display.

Regards
Mark

 

[philvalko]

Thanks for the reply.

Unfortunately, I am still stumped. I cannot get Access to display the result in a text box on a form.

I've written SQL queries and run them as independent queries with complete success, but when I put the SQL text in the Control Source dialog of a text box, the text box simply displays "#Name?"

Ultimately, what I want to accomplish is to display summary information on the form so that users can verify if the total # of buildings is correct for a project. Eventually users will be able to enter, alter, and delete records. As they do so, I'd like the summery information to update, as well. See the attached screen shot for an image of the form as it stands. The section that says "Project Overview" will contain this summary information.

Any idea why the results of the SQL queries won't display for me in text boxes? Ideas for workarounds?

Thanks

 

[pbaldy]

There is either a misunderstanding here or a semantics problem. A textbox can not have a control source of an SQL statement. Try this:

=DCount("*", "tblAllUnits", "UnitTypeID = 'R'")

 

[philvalko]

follow-up

Thanks to you both, Paul and Mark.

DCount worked like a charm. I have a follow-up question regarding how to have the DCount function update as the user enters new data on the form. My guess is that this will be slightly complicated in that the users will be entering data in a subform within the form. I've attached a screen shot of the form for reference.

I found an article through fontstuff.com that recommended putting the DCount function in the OnCurrent event of the main form. With this, when I enter a new record in the sub-form, it does update, BUT ONLY when I navigate away and then back to the specific project (record). Any suggestions for how I can get the summary info (all the text boxes at the bottom of the form) to update as soon as a new record is added in the subform?

 

[philvalko]

Attached screenshot

Forgot to attach the screenshot of the form.

 

[Jonathan Kok]

Put it in the 'After Update' event of the subform (referenced via Forms!formname!fieldname.ReQuery)

Wow. That's a lot of fields. And a lot of typing.
Try using a 'with' statement to make it easier, ie, instead of

forms!formname!fieldname1.requery
forms!formname!fieldname2.requery
...

Use

With forms!formname
	!fieldname1.Requery
	!fieldname2.Requery
	...
End With

 

[pbaldy]

Are those all DCount's? That may get slow if there's a lot of data. You may want to consider some sort of subform based on a totals query that pulls all that up for you.

 

[philvalko]

They are all DCount's. Fortunately the dataset is relatively small and will not be growing by leaps and bounds, so there is only a fraction of a second calculation delay.

Jonathan, the requery statement worked perfectly. Thank you!