Basic Questions about Using Formulas/Queries in Forms

[ItsMe]

The first thing I see, and June mentioned this, is that you have this
With Me!subForm.Form

The subform container is looking at a Query Object not a Form Object and I would guess you do not get to use "With Me!subForm.Form" to access the properties of a query. However, I tested your DB and it seems to be using the Filter property just fine.

I suggest building a form and place that object's name in the subform container's Source Object property using the property sheet in design view of your form. I do not see a need for VBA to change the Source Object. My earlier suggestion was to practice changing the RecordSource via VBA. Also, maybe a better name for the subform container/control would be subStatsResult or something. "subForm" seems very generic and may be misleading.


Another issue I see is
You are using the option controls to set the filter property autonomously. They do not consider the value of the other option controls or the combo box. Perhaps a fist step would be to use the wizard to create an Option Group. This way you use the option group's .Value property to determine the filter. The tricky part in creating the option group is naming it. I believe the default starts with "Fields". Make sure to select the Group (while in design View by clicking or swooping it) and then check the "Other" tab in the property sheet for the name of your Option Group. The individual options within the group will have their own names and properties, something like opt1 etc.

See if you can get an option group do determine the filter property of your subform and then we can worry about merging that result with your combo.

[gasmaskman]

A lot of this is a bit over my head so I apologize if these questions/problems are silly. When you say, "I suggest building a form and place that object's name in the subform container's Source Object property", I created a blank form called frmTest and added a subform named subStatResults with a source object set to frmTest and it says, "You can't place a form within itself".

I created an options group and added 'Outfield', 'Catcher' and 'Pitcher' as the options and it only allows me to check one of the three at a time. Ideally, I'd be able to check two or three of the options and it would filter using OR.

[June7]

That's true, can't use frmTest as both the main form and the subform.

Is subStatResults the name of the container control?

[gasmaskman]

When I look at the subform/subreport's properties it says that name is subStatResults, would that be the container control name?

[June7]

In Design View, one click on the 'subform' selects the container. If you see the SourceObject property then you know the container is selected. If the SourceObject is a form or report then click again and you are in the container and the object is selected.

[ItsMe]

I created a new query and named it qStats. This query is just like the other queries except it does not have any where clause and I also included the ID field. We can simulate the where clause from the form using the .Filter property. One query object for many different possible WHERE clauses.


I created a new form and named it sfrmMain. I changed the default view from Single to Data Sheet using the property sheet.

In frmMain, I changed the name of the subform container to "subStats" and used the name "sfrmMain" in the Source Object property (referring to the properties of the subform control/container). I also set the default value of your option buttons to = 0.

You mentioned that the option group is not appropriate because you may want to select more than one option. I handle this with VBA. You already have VBA for your option buttons. I modified it a bit and also decided on the After Update event. You were using On Click and also On After Update. No big deal.


What we have here is not complete. See if you can trouble shoot it and find where I left off. What this sample DB is doing is using the filter property of the subform "sfrmMain" to filter results. The approach being used to adjust the filter could easily be changed to use the subform's RecordSource. You do not want to affect the ControlSource but that would work too.

[gasmaskman]

So far I've added code that filters by 'AtBats' or 'Hits', depending on which is picked from the combobox drop down. I added 'AtBats' to sfrmMain and changed 'AtBats' to a number format. I also tweaked some of the code to switch from the AND operator to OR. Not sure if I'm missing any other troubleshooting, but, if not, then the next step would to try and keep strWhere clean, for example, sometimes strWhere doesn't start with "[ID] > 0" so I'd need to add code to reload the form. I also need to figure out the code behind when to use AND and when to use OR, for example, if I already have one position checked then I'd want to use OR but if no position filters exist then I'd want to use AND. For this second part the only way of coding it that I could come up with would be adding in a bolean that says true/false on whether a position is already checked or not. Is this the right track? I don't want to get too ahead of myself into the code if there are parts of the forms or queries that still need work that I missed. As always, thank you for the help ItsMe.

[ItsMe]

a Boolean to choose between AND and OR may be the way to go. Why don't you upload a sample of what you have so far? I will look at the syntax and then see if I can understand better what it is you are after.

[gasmaskman]

Please see attached.

[ItsMe]

That sample looks a lot like the one I uploaded earlier. In other words, it does not seem that you tried to finish what I started. I did not include code for one of the "Position" options.

As for using AND or OR, I am having trouble understanding the purpose of the combobox in conjunction with the radio buttons. Does your DB store stats per player per position? Admittedly, I do not follow baseball but, I thought averages were per player, per season, regardless of the positions they may have played. Perhaps errors are tracked by position but your table shows batting averages.

In other words, the combobox should filter the recordset just fine. I did not include code with the sample with a combo event.

Are you wanting to compare different players based on the postion(s)n they play, using the combo sometimes and other times using the radio buttons; either the combo or the radio buttons?

Perhaps you can try to add functionality to the chkSAS4_Click event to make sure you, at least somewhat, understand how the code currently works.

Also, you can compress the DB file before uploading. Right click and send to compressed file.

[gasmaskman]

Thanks for the reply. I added code to the chkSAS4_afterupdate event, but I'll wait to upload until after I have the and/or boolean part of the code working too.

The combobox is used to select a statistic (Hits, ABs, HRs). For this example lets say that the user selects Hits, then the subform would only display the players name and their number of Hits (with a filter saying hits > 0, which is in the vba code that added for the combobox afterupdate event). If ABs were selected then the Hits column would disappear and the ABs column would appear showing all players with ABs greater than 0. I could be wrong, but I think I'll have to adjust the 'Select' part of the SQL to show only one stat column at a time...or create more than one query?

[ItsMe]

OK then, sounds good. We can take a look at the combo a little later.