Query Criteria based on Navigation Button

[neo651]

I have a form with a series of navigation buttons. Each button has the same form loaded into it. The form is based on a query who's criteria I would like to change based on which Navigation button is selected but I haven't the foggiest idea how to accomplish this. Any ideas?



Thank you.

[June7]

What do you mean by 'Navigation button' - this a Navigation form? Why duplicates of the same form? Why not just filter one form?

[neo651]

I know nothing about filtering. My idea was to create a Navigation form and figure out a way to change the criteria of a query based on which tab was selected.

[June7]

Maybe this will give you ideas http://datapigtechnologies.com/flash...tomfilter.html

[neo651]

I'm familiar with that method described there, but I don't know how to use those concepts in a navigation form. A combobox can be bound to a field that passes a UID into the query. But if reference a Navigation button in a query that button doesn't contain any data to pass into the query to act as a criteria.

[June7]

And I have never made use of a Navigation form. There must be some way to apply filter criteria to subform.

I still don't understand why you would duplicate subform. Maybe seeing will help. If you want to provide db for analysis. Follow instructions at bottom of my post.

[neo651]

Marketers' Weekly Activity Report.zip

Primary Data is in tblPanelData. All forms (except frmMain and frmCombined) are based on qryPanelData, they pull the appropriate columns out of qryPanelData. I will then put all those forms into frmCombined, which I place into each tab of the Navigation form frmMain. Please excuse the poor table structure, this needs to be a quick and dirty project. As far as the forms.....I honestly don't know what I'm doing there, lol.

Thank you.

[June7]

Okay, I see it and I think I understand what you want. You want to offer an easy way for user to view records associated with each office location. To accomplish this you have a tab for each location. User would click tab and view that location records. I expect code would have to set the RecordSource or Filter property of the subforms. This is a complicated structure I would not use and have never seen from anyone else. Also, if you add a new location, have to modify form design.

I don't understand the subforms. What is the significance of the names? As you said, they all have the same data source and if you want the records on each tab to display only that location's records, why are the subform names not the locations? And why are there two subforms on each tab?

Why have you chosen this approach? The example in the link I referenced is more conventional solution.

I would do a form/subform setup http://office.microsoft.com/en-us/ac...010098674.aspx

The main form would be bound to tblOffices (actually a parameterized query of that table) and subform would be bound to tblPanelData. Then method demonstrated in first tutorial would filter the main form to desired location.

You want 'quick' then you are going down the wrong path.

[neo651]

This database is going to be replacing an Excel sheet for 9-12 months until we purchase a Customer Relationship Management package. For the time being I'm supposed to make the interface of the database mimic the way the Excel sheet was setup. In the Excel sheet, each location had a tab, each tab was the same layout and was divided into sections. The way I see it, each section of the Excel sheet needs to be translated into its own subform. The significance of the names of the subforms is they are the titles of the sections from the Excel sheet. There's only 2 subforms in each tab for now just because I haven't finished yet, I'm just trying to figure out if my concept is feasible before I go through with the grunt work of adding every subform.

[June7]

AFAIK, easiest way to replicate the Excel and virtually eliminate code and get this done quick would be either:

1. a separate table for each location (which is not optimized structure for a relational database)
2. a single table that is filtered for the desired location

and then for either of the above, a form for each location, not one form used multiple times, but multiple forms, each with its own SQL statement as RecordSource, copy/paste form then change the RecordSource - and in the case of 2 above, include a static filter parameter