how to populate a combo box with canned queries and display results

[AccessIdiot]

I have one big fat table with goose nesting data. Things like nest location (name of island in a river), nest type (ground/platform), number of eggs, number of eggs hatched, type of predation, etc.

I have some canned queries that I need to report: total number of eggs, number of eggs by island, number of eggs by type, number of nests, number of nests by island, number of nests by type, etc. So there are a few basic questions (total # nests, total # eggs, total nest success, total nest failure) and then further filters (all of the above filtered by type, location, possibly date range).

I would LOVE to build a dynamic search form but I keep failing (combo box #1 = total # of . . .; combo box #2 = filter by type/location; 3rd filter of date range).

So now I'm trying to just build one combo box that contains all these canned queries and then have a subform or report (whatever is easiest) show the results. The queries are not simple select statements, they are sums and counts. Any ideas? All I've got right now is an unbound form with an unbound combo box that contains a value list of two columns: name of query, description of query. I'm clueless about what to put in the after update part of my combo box.

By the way this is Access 2007. Cheers!

[AccessIdiot]

Okay so now I have a select case running openquery in my after update statement of my combo box. I'd like to have the results displayed in a subform instead of popping open the actual query. How is this accomplished?

[patjones]

In the broadest terms possible, you'll want to use something like:

Select Case combovalue
   Case value1
      Forms![subform name].RecordSource = str_query1_SQL
   Case value2
      Forms![subform name].RecordSource = str_query2_SQL
   . . .
End Select
 
Forms![subform name].Requery

If there is a lot of similarity between the various SQL, something you can do is set the subform recordsource ahead of time in a very general way - say "SELECT * FROM tablename", then simply use the case structure to pick out the appropriate filter (call it "strFilter" ) and set the filter after coming out of the case structure: Forms![subform name].Filter = strFilter. This would be the preferred method, but if there is a lot of variability in these queries you may not be able to do it this way.

Pat

[AccessIdiot]

I apologize for being a total newb (my username says it all). My select case is set up a bit different. Right now it looks like this:

Option Compare Database
 
Private Sub cboSelectQuery_AfterUpdate()
    Select Case Me.cboSelectQuery
    Case 0
    DoCmd.OpenQuery "qry_NestCount"
    Case 1
    DoCmd.OpenQuery "qry_NestCountType"
    Case 2
    DoCmd.OpenQuery "qry_NestCountIsland"
    End Select
 
End Sub

and it just launches the query. I would love to have the result displayed on the form itself, or in a report or something.

Maybe I should just build a report with all the canned queries displayed instead of trying to get fancy with dynamic queries.

Thanks for your help.

[patjones]

Well you have over 400 posts, so I wouldn't call you a total newbie! Anyway, what you have is very close to what I suggested, except you want to show the query results in a subform. Since you have all the queries setup, you could do something like this:

Select Case Me.cboSelectQuery
    Case 0
       Forms![subform name].RecordSource = "qry_NestCount"
    Case 1
       Forms![subform name].RecordSource = "qry_NestCountType"
    Case 2
       Forms![subform name].RecordSource = "qry_NestCountIsland"
End Select
 
Forms![subform name].Requery

Of course, you're going to need to set the subform up with all the proper fields, but that shouldn't be too tough.

Pat

[nico5038]

I would probably create a form with a tab control holding on each tab a subform with the query to show the results.
Finally you can add dynamically a Filter to these subform to limit the results e.g. per measuring period.

Idea ?

Nic;o)

[AccessIdiot]

@zepphead80
400+ posts because I ask a lot of dumb questions. :-)

For example, I'm not even sure how to set up the subform. I mean, one query is doing a count (count Nest_IDs), another is doing a sum (sum Num_Eggs), etc. Really I'd be happy with a report that showed all the data, then had some text boxes that showed the results of the queries. But apparently you can't set an unbound textbox control source to a query? I have to build the function again?

[patjones]

No but you can set the text box Control Source to an aggregate function. For instance:

Me.txt = DSum("order_quantity", "tblOrders", "customer_ID <= 5")

This calculates the sum of the order quantity column in the orders table, but will include customer ID's only up to and including 5. There are other domain aggregate functions like DCount, DMin, DMax, DAvg, etc. and they would work nicely in a report where you want to summarize data.

Pat

[AccessIdiot]

Okay, I'm trying a different approach. Tell me if I'm crazy (or just inefficient :-) ).

I have three combo boxes. The first is unbound, with a value list of "Nest Type" and "Island".

When "Nest Type" is chose I want a 2nd combo box to appear that lists the nest types (bound to a look up table). If "Island" is chosen I want the 2nd combo box to list the Island names (bound to a different look up table).

Then the user hits the "apply filter" button and I have either a subform that shows the filtered records (from the main table) in table view OR launches a filtered report (the latter would be easier to print or export, but right now I just want something that works).

So I'm thinking three combo boxes. The first is the 'filter by' one and the other two are hidden until a choice is made in the first one.

Problem is I'm not sure where to put the code. I know I need something on the after update of the first combo box and probably also something on the form launch? There are so many examples out there I think I'm confusing myself with what to put where. Any help appreciated!

[nico5038]

Check out these insights:

http://bytes.com/topic/access/insigh...filtering-form

http://bytes.com/topic/access/insigh...mbo-list-boxes

Thanks to NeoPa :-)

Nic;o)