By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,465 Members | 1,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,465 IT Pros & Developers. It's quick & easy.

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

AccessIdiot
100+
P: 493
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!
Jul 8 '10 #1
Share this Question
Share on Google+
21 Replies


AccessIdiot
100+
P: 493
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?
Jul 8 '10 #2

patjones
Expert 100+
P: 931
In the broadest terms possible, you'll want to use something like:

Expand|Select|Wrap|Line Numbers
  1. Select Case combovalue
  2.    Case value1
  3.       Forms![subform name].RecordSource = str_query1_SQL
  4.    Case value2
  5.       Forms![subform name].RecordSource = str_query2_SQL
  6.    . . .
  7. End Select
  8.  
  9. 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
Jul 8 '10 #3

AccessIdiot
100+
P: 493
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub cboSelectQuery_AfterUpdate()
  4.     Select Case Me.cboSelectQuery
  5.     Case 0
  6.     DoCmd.OpenQuery "qry_NestCount"
  7.     Case 1
  8.     DoCmd.OpenQuery "qry_NestCountType"
  9.     Case 2
  10.     DoCmd.OpenQuery "qry_NestCountIsland"
  11.     End Select
  12.  
  13. 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.
Jul 8 '10 #4

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Select Case Me.cboSelectQuery
  2.     Case 0
  3.        Forms![subform name].RecordSource = "qry_NestCount"
  4.     Case 1
  5.        Forms![subform name].RecordSource = "qry_NestCountType"
  6.     Case 2
  7.        Forms![subform name].RecordSource = "qry_NestCountIsland"
  8. End Select
  9.  
  10. 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
Jul 8 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 8 '10 #6

AccessIdiot
100+
P: 493
@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?
Jul 8 '10 #7

patjones
Expert 100+
P: 931
No but you can set the text box Control Source to an aggregate function. For instance:

Expand|Select|Wrap|Line Numbers
  1. 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
Jul 8 '10 #8

AccessIdiot
100+
P: 493
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!
Jul 12 '10 #9

nico5038
Expert 2.5K+
P: 3,072

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Option Compare Database
  4. Private Sub cboFilterBy_AfterUpdate()
  5. If Me.cboFilterBy.Value = "Nest Type" Then
  6.     Me.cboType.Visible = True
  7.     Me.cboIsland.Visible = False
  8. ElseIf Me.cboFilterBy.Value = "Island" Then
  9.     Me.cboIsland.Visible = True
  10.     Me.cboType.Visible = False
  11. Else
  12.     Me.cboType.Visible = False
  13.     Me.cboIsland.Visible = False
  14. End If
  15. End Sub
  16.  
  17. Private Sub Form_Current()
  18.  
  19. If Me.cboFilterBy.Value = "Nest Type" Then
  20.     Me.cboType.Visible = True
  21.     Me.cboIsland.Visible = False
  22. ElseIf Me.cboFilterBy.Value = "Island" Then
  23.     Me.cboIsland.Visible = True
  24.     Me.cboType.Visible = False
  25. Else
  26.     Me.cboType.Visible = False
  27.     Me.cboIsland.Visible = False
  28. End If
  29. End Sub
  30.  
  31. Private Sub Form_Load()
  32.      DoCmd.OpenReport "rpt_NestingMain", acViewPreview
  33. End Sub
  34.  
  35. Private Sub cmdApplyFilter_Click()
  36.     Dim strType As String
  37.     Dim strIsland As String
  38.     Dim strFilter As String
  39.  
  40. '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
  41. 'and then construct the appropriate SQL:
  42.  
  43.     If IsNull(Me.cboType.Value) Then
  44.         strType = "Like '*'"
  45.     Else
  46.         strType = "=" & Me.cboType.Value & ""
  47.     End If
  48.     If IsNull(Me.cboIsland.Value) Then
  49.         strIsland = "Like '*'"
  50.     Else
  51.         strIsland = "='" & Me.cboIsland.Value & "'"
  52.     End If
  53.  
  54. 'Combine the criteria to form a WHERE clause for the filter:
  55.     strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland
  56.     With Reports![rpt_NestingMain]
  57.          .Filter = strFilter
  58.          .FilterOn = True
  59.     End With
  60.  
  61. End Sub
  62.  
  63.  
  64. Private Sub cmdRemoveFilter_Click()
  65.      On Error Resume Next
  66.      Reports![rpt_NestingMain].FilterOn = False
  67.      Me.cboFilterBy.Value = ""
  68.      Me.cboType.Visible = False
  69.      Me.cboIsland.Visible = False
  70. End Sub
  71.  
Jul 12 '10 #11

AccessIdiot
100+
P: 493
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!
Jul 12 '10 #12

Expert 100+
P: 1,240
I think you are missing 2 equal signs on line 55.

Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. strFilter = "[Nest Location]= '" & strType & "' AND [Island Name]= '" & strIsland & "'"
Jim
Jul 12 '10 #13

AccessIdiot
100+
P: 493
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub cboFilterBy_AfterUpdate()
  3. If Me.cboFilterBy.Value = "Nest Type" Then
  4.     Me.cboType.Visible = True
  5.     Me.cboIsland.Visible = False
  6. ElseIf Me.cboFilterBy.Value = "Island" Then
  7.     Me.cboIsland.Visible = True
  8.     Me.cboType.Visible = False
  9. Else
  10.     Me.cboType.Visible = False
  11.     Me.cboIsland.Visible = False
  12. End If
  13. End Sub
  14.  
  15. Private Sub Form_Current()
  16.  
  17. If Me.cboFilterBy.Value = "Nest Type" Then
  18.     Me.cboType.Visible = True
  19.     Me.cboIsland.Visible = False
  20. ElseIf Me.cboFilterBy.Value = "Island" Then
  21.     Me.cboIsland.Visible = True
  22.     Me.cboType.Visible = False
  23. Else
  24.     Me.cboType.Visible = False
  25.     Me.cboIsland.Visible = False
  26. End If
  27. End Sub
  28.  
  29. Private Sub Form_Load()
  30.      DoCmd.OpenReport "rpt_NestingMain", acViewPreview
  31. End Sub
  32.  
  33. Private Sub cmdApplyFilter_Click()
  34. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  35.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  36.                         we remove the trailing " AND " at the end.
  37.     '           2. The date range works like this: _
  38.                         Both dates      = only dates between (both inclusive. _
  39.                         Start date only = all dates from this one onwards; _
  40.                         End date only   = all dates up to (and including this one).
  41.  
  42.     Dim strFilter As String                  'The criteria string.
  43.     Dim strType As String
  44.     Dim strIsland As String
  45.     Dim lngLen As Long                      'Length of the criteria string to append to.
  46.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  47. '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
  48. 'and then construct the appropriate SQL:
  49.  
  50.     If Me.cboType.Value > "" Then
  51.         strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
  52.     End If
  53.  
  54.     If Me.cboIsland.Value > "" Then
  55.         strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
  56.     End If
  57.  
  58.     'Date field. Use the format string to add the # delimiters and get the right international format.
  59.     'If Not IsNull(Me.txtStartDate) Then
  60.         'strFilter = strFilter & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
  61.     'End If
  62.  
  63.     'Another date field example. Use "less than the next day" since this field has times as well as dates.
  64.     'If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
  65.         'strFilter = strFilter & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
  66.     'End If
  67.  
  68.  '***********************************************************************
  69.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  70.     '***********************************************************************
  71.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  72.     lngLen = Len(strWhere) - 5
  73.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  74.         'MsgBox "No criteria", vbInformation, "Nothing to do."
  75.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  76.         strFilter = Left$(strFilter, lngLen)
  77.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  78.         'Debug.Print strWhere
  79.     End If
  80. 'Combine the criteria to form a WHERE clause for the filter:
  81.     strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland
  82.     With Reports![rpt_NestingMain]
  83.          .Filter = strFilter
  84.          .FilterOn = True
  85.     End With
  86.  
  87.  
  88. End Sub
  89.  
  90.  
  91. Private Sub cmdRemoveFilter_Click()
  92.      On Error Resume Next
  93.      Reports![rpt_NestingMain].FilterOn = False
  94.      Me.cboFilterBy.Value = ""
  95.      Me.cboType.Visible = False
  96.      Me.cboIsland.Visible = False
  97. End Sub
  98.  
Jul 12 '10 #14

AccessIdiot
100+
P: 493
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.
Jul 12 '10 #15

Expert 100+
P: 1,240
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
Jul 13 '10 #16

AccessIdiot
100+
P: 493
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdApplyFilter_Click()
  2.     Dim strFilter As String 'The criteria string.
  3.     Dim strType As String
  4.     Dim strIsland As String
  5.     Dim strDate As String
  6.     Dim lngLen As Long   'Length of the criteria string to append to.
  7.     Const conJetDate = "\#mm\/dd\/yyyy\#"
  8.  
  9. If Me.cboType.Value > "" Then
  10.   strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
  11. End If
  12.  
  13. If Me.cboIsland.Value > "" Then
  14.   strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
  15. End If
  16.  
  17. 'Date field. Use the format string to add the # delimiters and get the right international format.
  18. If Not IsNull (Me.txtStartDate) Then
  19.   strDate = strDate & "([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
  20. End If
  21.  
  22. If Not IsNull (Me.txtEndDate) Then   'Less than the next day.
  23.   strDate = strDate & "([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
  24. End If
  25.  
  26. lngLen = Len(strDate) - 5
  27. strDate = Left$(strDate, lngLen)
  28.  
  29. 'final string
  30. strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland & " AND [Field Obs Date] " & strDate
  31.  
  32. With Reports![rpt_NestingMain]
  33.          .Filter = strFilter
  34.          .FilterOn = True
  35. End With
  36. End Sub
  37.  
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.
Jul 13 '10 #17

AccessIdiot
100+
P: 493
Okay, got it. That NeoPa guy sure knows what he's talking about. ;-)
Jul 13 '10 #18

Expert 100+
P: 1,240
:) Yea, a lot of us noticed that.
Jul 13 '10 #19

NeoPa
Expert Mod 15k+
P: 31,768
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?
Jul 13 '10 #20

AccessIdiot
100+
P: 493
@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
Jul 14 '10 #21

NeoPa
Expert Mod 15k+
P: 31,768
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 ;-)
Jul 14 '10 #22

Post your reply

Sign in to post your reply or Sign up for a free account.