A Little help with filtering navigationsubforms (1 Viewer)

[DoMeister]

Hi All,
I have not been here in a while. Its good to be back

I couple of years back I would have been able to answer this but its amazing how quickly one forgets...

I have a Navigation form 'MainForm' with a Combo20 drop down.
Implanted in the form is a NavigationSubForm with a data set.

I would like to select data in a number of tabs based on my selection in the main form. I will start with one but would like two or three ultimately.

Here is my After update event :banghead:

Private Sub Combo20_AfterUpdate()
[Forms]![MainForm]![NavigationSubform].[Form]![Split_Tag] = "SELECT * FROM [Tag] WHERE [Split_Tag]='" & Forms!MainForm!Combo20 & "'"
End Sub

Thanks for your help... DM

 

[sneuberg]

There is only one form open in the Navigation Subform at a time. So this afterupdate event could only affect the one that happens to be open (selected tab). If you want the content of other forms in the other tab to be base on this combo box you will have to do something in the form open of them.

In any case I don't understand what you are trying to do it the code your posted. Are you trying to set the record source of the form in the subform?

Edit: You could put a reference to the combo box in the record source queries of the forms in the subform. In the criteria of the appropriate field you would have something like:

[Forms]![MainForm]!Combo20

Then just requery the subform in the combo afterupdate with something like:

[Forms]![MainForm]![NavigationSubform].[Form].Requery

Note that this reference doesn't have the form's name in it and so is the same for all of the tabs, i.e., this will requery the form that is open.

But something sounds strange about this as this setup implies that all of the record sources query of the forms in the tabs have a field that is basically the same. I wonder in your tables have normalization problems.

 

[DoMeister]

Hi Steve,
Thanks for your reply.
In essence I have a bunch of small data sets that relate to the same topic and are continuos forms or datasheets, ie differing number of records. I want to see the datasets based on a multiple of two categories, let's say area and type. I had hoped to put my category combo box in the main form header and then page through the tabs to see the views based on these categories. It sounded simple to start with!

I didn't quite understand what you meant by "You could put a reference to the combo box in the record source queries of the forms in the subform" so I'm hoping my explanation clears up your question about what I'm trying achieve before I ask more beginner questions....

Thanks Dom

 

[sneuberg]

Ok let's say the combo box can have either of the values "area" or "type" and lets rename the combo box cboCategory. Let's also say that the record sources of the forms in the tabs all have a field named Category which can have the values of either "area" or "type". Let also give cboCategory a default of "area" so that it's not null when the form loads. Then each of the record source queries of the forms in the tabs could have a reference in the criteria for Category of

[Forms]![MainForm]!cboCategory

with this in place the forms in the tabs would be showing the records for which the Category is "area" when the form loads. If you change the combo box to "type" you would want the records to change in the form showing in the tab which could be done by:

[Forms]![MainForm]![NavigationSubform].[Form].Requery

in the afterupdate of the combo box.

Does this scenario make sense now? Are my assumptions true? If not please tell me where I'm going wrong. If might also help if you upload your database so I can see what you got so far.

 

[DoMeister]

Thanks Steve,
That worked for the most part. I was able to construct 6 tabs that are filtered based on 2 combo boxes which is fantastic.

1 tab did not however work and the difference here is that this tab is based on a crosstab query set in a datasheet. Have you come across this before and is there a known work around. I tried a few things but its returning a blank view when the crosstab query criteria is set by "[Forms]![MainForm]![cboArea]"

Cheers for your help.
Dom

 

[sneuberg]

I believe crosstab queries have special requirements. I remember reading something about the need to declare the parameters, i.e, in the query builder DESIGN tab, Show/Hide section, Parameters. I don't know how they are suppose to be entered there. I suggest googling something like:

"MS Access crosstab query parameters"

 

[DoMeister]

Thanks Steve,
I found this article on crosstab query parameters and the second option worked for me - Specify column headings.
allenbrowne.com/ser-67.html

It works great now as a prototype but if I want to develop it further I will need to learn how to make the column headings dynamic...
access.hookom.net/DynamicMthlyCrosstabRpt.htm

(I can't paste url as I'm only a fledgling!)

Cheers
Dom