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)

[AccessIdiot]

Broken links? Ahh, fixed, thanks, will check them out.

So here is what I have so far on an unbound form:

three combo boxes - two hidden and stacked that appear based on what the first one calls.

Two buttons: one for apply filter and one for remove filter. I found some great code for filtering a report based on a couple of combo boxes from a form.

The filter code seems to work for one combo box (cboType) but not for the other (cboIsland). I think because its trying to combine the where clause and really they are two separate where clauses but I don't know how to modify it to say "construct the where clause based on which cbo is visible".

The error I'm getting has to do with a data type mismatch but I'm not sure a) why when they are both constructed the same way and one works and the other doesn't and b) how to fix it. The tables that feed the combo boxes are two columns: auto id and text value. The combo boxes are bound to the first column but display the 2nd.

Here is the code:

 
 
Option Compare Database
Private Sub cboFilterBy_AfterUpdate()
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Current()
 
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Load()
     DoCmd.OpenReport "rpt_NestingMain", acViewPreview
End Sub
 
Private Sub cmdApplyFilter_Click()
    Dim strType As String
    Dim strIsland As String
    Dim strFilter As String
 
'If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice
'and then construct the appropriate SQL:
 
    If IsNull(Me.cboType.Value) Then
        strType = "Like '*'"
    Else
        strType = "=" & Me.cboType.Value & ""
    End If
    If IsNull(Me.cboIsland.Value) Then
        strIsland = "Like '*'"
    Else
        strIsland = "='" & Me.cboIsland.Value & "'"
    End If
 
'Combine the criteria to form a WHERE clause for the filter:
    strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland
    With Reports![rpt_NestingMain]
         .Filter = strFilter
         .FilterOn = True
    End With
 
End Sub
 
 
Private Sub cmdRemoveFilter_Click()
     On Error Resume Next
     Reports![rpt_NestingMain].FilterOn = False
     Me.cboFilterBy.Value = ""
     Me.cboType.Visible = False
     Me.cboIsland.Visible = False
End Sub
 

[AccessIdiot]

Okay, I fixed it myself - nevermind! Thanks for the links, they helped me figure out how to correctly phrase for a numeric value in the cbo. Now to filter further on date range!

[jimatqsi]

I think you are missing 2 equal signs on line 55.

strFilter = "[Nest Location]= " & strType & " AND [Island Name]= " & strIsland

About the datatype mismatch, how is Nest Type defined in the table? Is it a string field? You are passing the nest type through strType, which is a string, but your filter does not enclose the type in quotes, so it looks like you are treating it as a numeric value. It has to be all one way or all the other.
Did you mean

strFilter = "[Nest Location]= '" & strType & "' AND [Island Name]= '" & strIsland & "'"

Jim

[AccessIdiot]

Yep you are right, its not working.

This is where I get confused. The table that the combo box is bound to has two columns: the primary key auto id and a text field that has the text I care about. So its that whole thing where you bind to one column but display two, the first with a width of zero. This was common practice in access '03 but I'm not sure if that's still how it's done with '07. It's how I have it set up.

So I'm assuming that that is why I was getting the data type error - because the code I was using was formatted for a text field when really the combo box is bound to the numeric field. This is what my code looks like now (I tried to fix it for the data type) but it is not giving me the results I want.

BTW this code is totally hacked from a couple of different examples so its probably really sloppy.


Oh and I've left in the commented out code for filtering by date because I'm going to tackle that next. :-)

I know part of the problem is the big where clause at the end where I'm including both cboType and cboIsland when really I need to filter on one or the other. I'm just not sure how to tackle that. When one is visible I need the invisible one to not be included in the where statement.

Option Compare Database
Private Sub cboFilterBy_AfterUpdate()
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Current()
 
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Load()
     DoCmd.OpenReport "rpt_NestingMain", acViewPreview
End Sub
 
Private Sub cmdApplyFilter_Click()
'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
 
    Dim strFilter As String                  'The criteria string.
    Dim strType As String
    Dim strIsland As String
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
'If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice
'and then construct the appropriate SQL:
 
    If Me.cboType.Value > "" Then
        strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
    End If
 
    If Me.cboIsland.Value > "" Then
        strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
    End If
 
    'Date field. Use the format string to add the # delimiters and get the right international format.
    'If Not IsNull(Me.txtStartDate) Then
        'strFilter = strFilter & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    'End If
 
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    'If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        'strFilter = strFilter & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    'End If
 
 '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        'MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strFilter = Left$(strFilter, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
    End If
'Combine the criteria to form a WHERE clause for the filter:
    strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland
    With Reports![rpt_NestingMain]
         .Filter = strFilter
         .FilterOn = True
    End With
 
 
End Sub
 
 
Private Sub cmdRemoveFilter_Click()
     On Error Resume Next
     Reports![rpt_NestingMain].FilterOn = False
     Me.cboFilterBy.Value = ""
     Me.cboType.Visible = False
     Me.cboIsland.Visible = False
End Sub
 

[AccessIdiot]

Okay, got it working except for date range filter. If I leave that part blank I get an error. If I put something in it does nothing. I can't figure out the right syntax to include it as part of the final filter statement.

[jimatqsi]

Try wrapping your date criteria in "#", one before and one after the date, like this
'strFilter = strFilter & "([EnteredOn] <# " & Format(Me.txtEndDate + 1, conJetDate) & "#) AND "

Jim

[AccessIdiot]

That didn't do it. I think the conJetDate already formats it with #'s.

I think it has something to do with how the final statement is put together.

Private Sub cmdApplyFilter_Click()
    Dim strFilter As String 'The criteria string.
    Dim strType As String
    Dim strIsland As String
    Dim strDate As String
    Dim lngLen As Long   'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"
 
If Me.cboType.Value > "" Then
  strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
End If
 
If Me.cboIsland.Value > "" Then
  strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
End If
 
'Date field. Use the format string to add the # delimiters and get the right international format.
If Not IsNull (Me.txtStartDate) Then
  strDate = strDate & "([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If
 
If Not IsNull (Me.txtEndDate) Then   'Less than the next day.
  strDate = strDate & "([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
 
lngLen = Len(strDate) - 5
strDate = Left$(strDate, lngLen)
 
'final string
strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland & " AND [Field Obs Date] " & strDate
 
With Reports![rpt_NestingMain]
         .Filter = strFilter
         .FilterOn = True
End With
End Sub
 

Basically I'd like to be able to enter a starting date only OR a range of dates and have it work. I believe the code came from that classic Allen Browne site.

[AccessIdiot]

Okay, got it. That NeoPa guy sure knows what he's talking about. ;-)

[jimatqsi]

:) Yea, a lot of us noticed that.

[NeoPa]

AccessIdiot: Okay, got it. That NeoPa guy sure knows what he's talking about. ;-)

Absolutely!! :D

M. (I won't use full name here & I can't refer to you as AccessIdiot as I know better than that),

Great to see you back again.

I just caught your latest thread and saw that you'd been back for a week or so. I'll pop over there now and see if I can't provide some sort of answer for you.

-NeoPa.

PS. How are the fish getting on up there? All mapped and documented yet?

[AccessIdiot]

@NeoPa
Good memory A. :-)

That fish project sailed long ago. Used the db for 2-3 years and then the guys left the company. Who knows, maybe they are still using it! Not sure if that's a good thing or a bad thing but it did what it was supposed to do, and still impresses the clients that know nothing about Access! :-P

[NeoPa]

AccessIdiot: Good memory A. :-)

Clearly you are no slouch in that arena either.

Good news that the db was able to impress.

Switching across to other thread now ;-)