Basic Questions about Using Formulas/Queries in Forms

[gasmaskman]

I am creating a database that tracks a baseball team's statistics and was trying to create a form that would act as a team summary showing total statistics and the user would be able to click on a statistic and it would run a query showing the data behind the number. For example, one line would say "14 total team homeruns" and a user could click it and it would run a query showing the name of every player that has hit a homerun and the number that they have hit. I'm able to do the SQL behind the queries, but I have limited experience creating forms and am not sure how to create a label or command button that calculates the total number of homeruns, and I'm also hoping for any general advice that could make it a better experience for the user (for example, should I embed the queried table into the form or just run the query outside of the form?). Any help is greatly appreciated and thank you in advance.

[ItsMe]

I am not sure what embedding a table to a form is. Forms use recordsets. Controls on forms may depend on the form's recordset/recordsource, or they may be unbound, or some can have their own RowSource.

Your question does not have a simple answer and then you add the problem of statistics. Before worrying about a form, get your tables in order with sample data. Once you have that, start creating SELECT queries to test JOINS of your various tables. View your data from query objects via Datasheet View. Then worry about custom queries to get statistical summaries. You can use VBA to create/supplement custom queries.

[gasmaskman]

Thanks for the response ItsMe, you are the main reason that I come to this forum first and foremost. I don't think I'd need to test any JOINS, because my database only has one table with the columns "Player Name", "Player Number", "Hits", "Homeruns" and "At Bats". When clicking "14 total team homerums" it would just link to a query I already created that filters on the "Homeruns" columns being greater than 0. With just one table and no links excel would probably be the better option, but I wanted to take the opportunity to learn access. Thanks again!

[gasmaskman]

Ok, I think I discovered part of my issue. I had created a blank form and a textbox and entered '=Count([TeamStats]![HomeRuns])' into the textbox's control source and it was kicking back an error...I didn't realize that a form control source needed to be set as well. When I say embedded table, I meant that the form would have a control on it that would display the same dataview information as if i just ran the query outside of the form.

[ItsMe]

Use this as an exercise.

Start with one form and one combobox control. You can bind your form to the table. Use SQL in the combo's RowSource to isolate one type of statistic as you practice adjusting the form's RecordSource.

Use the afterupdate event of your combo to adjust your form's RecordSource.

This would be similar to Cascading Dependent Comboboxes. I have a sample DB here that shows using afterupdate events to adjust the dependent combo's rowsource. See if you can build a single form that is dependent on a single combo.
https://www.accessforums.net/sample-...tml#post200624

[June7]

The technical terms are RecordSource of form/report and ControlSource of data controls (textbox, combobox, listbox, checkbox).

To count every record the expression can simply be: =Count(*)

Specifying a field will count only records with data in that field, nulls will be ignored.

[gasmaskman]

Thanks for the replies ItsMe and June7. I've practiced with one table, one combo box, and one form and I'm starting to get the hang of it though it is still a bit confusing figuring out the difference between creating a 'Form' vs a 'Blank Form' vs Binding and I'm also still a little confused about recordsets vs recordsources vs controlsources. At this point, I have one combo box that has the titles of queries ('Hits', 'Home Runs', 'At Bats') and when one is selected it runs an afterupdate line of code: "Me.subForm.SourceObject = "Query." & comboReport.Value" which updates the subform below the combo box returning all the players who have at least one hit or hr or ab. Is this a good route to take? If so, my next step will be adding checkboxes that allow the user to select which positions that they would like to filter on.

[ItsMe]

Why don't you remove sensitive data and upload a copy here? If I don't have a chance right away I will guess June7 is willing to take a look at it.

[June7]

I presume the positions are in a single field. Instead of checkboxes for positions, consider a multi-select listbox. Either way, will need code to construct filter string. Review:

http://allenbrowne.com/ser-50.html

http://allenbrowne.com/ser-62code.html

[gasmaskman]

I added checkboxs to filter data, but if multiple checkboxs are checked then it isn't displaying both positions. Additionally, I started testing out the visible property of the checkboxs for when I create a report where the position doesn't matter and the boxes can be hidden. Any feedback, good or bad, is welcome.

[June7]

Did you review my previous post and the referenced link?

[gasmaskman]

Yes, I saw it after I uploaded the database, but I'm still going through and interpreting the lines of code, one by one.

[gasmaskman]

I'm a little confused about the code. The DB that I uploaded is changing the subform's sourceobject based on the combo dropdown and then filtering. The suggested technique looks as though there is no subform and instead of basic filtering it is creating filters using sql...I could be wrong, but I feel like this would be a big change.

[June7]

Yes, your code is changing the SourceObject to different queries. I have never done this. I also never use tables/queries as SourceObject, only forms/reports.

Except for table DataMacros, can't have code behind tables/queries.

The suggested methods just apply a filter to the SourceObject.

If you want to allow multiple parameters on the same field (Position), will need to use a form and VBA code to construct the filter string. And it is not necessary for the form to be a subform. Place the input controls in form Header section.

[ItsMe]

June is correct and it is important to distinguish the difference between RecordSource and ControlSource.


I will likely be able to look at your DB later.

Like June mentioned, it boils down to where criteria or clause.