469,317 Members | 2,024 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

Form with 7 combo boxes

I have a form with 7 combo boxes to to search various fields in a table in Access 2007. I am trying to construct "dynamic" or conditional SQL for my subroutine, that would only use the values from selected comboboxes for the "where" criteria if a selection has been made, otherwise it will ignore the combo box.

For example if cmb1 is not selected, the where clause should look like:

select * from myTable where myField2= 'value of cmb2' and myField3= 'value of cmb3' and so on

If on the other hand cmb1 is selected, it would be included in the where clause.

How do I create this conditional SQL? or is there a better way to do it?

Thanks.
May 19 '10 #1
12 1510
TheSmileyCoder
2,321 Expert Mod 2GB
This is VBA code that allows you to dynamically createa SQL where clause.
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere as string
  2.  
  3. If not isNull(Me.Cmb1) Then
  4.    if strWhere & "" <>"" then strWhere= strWhere & " AND "
  5.   strWhere=strWhere & " myField2='" & me.Cmb1 & "'"
  6. End If
Just repeat the If statement for Cmb2 and 3 (hopefully you give them more meaningfull names!)

Then you just need to combine the Where clause with the Select clause properly. If you don't know how to do this, I can write some more details on the matter.
May 19 '10 #2
@TheSmileyOne
Thanks for your response. I will try this tonight. Also, please provide the code for combining the "Where" with the "select" clause.

Thank you!
May 19 '10 #3
Jim Doherty
897 Expert 512MB
@rhapsodysolutions
Take a look at this little searchdemo db I did one time for a poster. Albeit done in an earlier version of Access ie not 2007 It has all the ingredients you need in the code behind to give you ideas

http://bytes.com/topic/access/answer...ed-search-form
May 20 '10 #4
@Jim Doherty
Thanks for the demo db. It is an impressive and exhaustive learning resource. I will save it and use it for my future projects.

@TheSmileyOne, your code worked perfectly! Here is what it looks like:

Expand|Select|Wrap|Line Numbers
  1.     Dim strWhere As String
  2.  
  3.     strWhere = " Where 1 = 1"
  4.  
  5.     If Not IsNull(Me.cmb_MediaType) Then
  6.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  7.       strWhere = strWhere & " MediaType='" & Me.cmb_MediaType & "'"
  8.     End If
  9.  
  10.     If Not IsNull(Me.cmb_City) Then
  11.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  12.       strWhere = strWhere & " Industry='" & Me.cmb_City & "'"
  13.     End If
  14.  
  15.     If Not IsNull(Me.cmb_market) Then
  16.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  17.       strWhere = strWhere & " City='" & Me.cmb_market & "'"
  18.     End If
  19.  
  20.     If Not IsNull(Me.CmbMediaName) Then
  21.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  22.       strWhere = strWhere & " MediaName='" & Me.CmbMediaName & "'"
  23.     End If
  24.  
  25.     If Not IsNull(Me.cmb_Client) Then
  26.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  27.       strWhere = strWhere & " Client='" & Me.cmb_Client & "'"
  28.     End If
  29.  
  30.     If Not IsNull(Me.cmb_Lastname) Then
  31.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  32.       strWhere = strWhere & " Lastname='" & Me.cmb_Lastname & "'"
  33.     End If
  34.  
  35.     If Not IsNull(Me.cmb_Title) Then
  36.        If strWhere & "" <> "" Then strWhere = strWhere & " AND "
  37.       strWhere = strWhere & " Title='" & Me.cmb_Title & "'"
  38.     End If
  39.  
  40.  
  41.         strSql = "select * from Master_Media_List" & strWhere & ";"
  42.         Me.RecordSource = strSql
Having tackled this, there are a couple of additional problems:

I also have a "Reset" button on that same form, that resets all comboboxes. However it does not reset the form in its original state- when it was first opened. Please let me know what I am doing wrong:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Reset_Click()
  2.  
  3.     Dim ctl As Control
  4.     For Each ctl In Me.Controls
  5.  
  6.     Select Case ctl.ControlType
  7.  
  8.     Case acComboBox
  9.     ctl.Value = Null
  10.  
  11.     End Select
  12.     Next ctl
  13.  
  14.     Me.Requery
  15.  
  16. End Sub
Secondly, how can I modify the code so that hitting "Enter" on the keyboard would run the query, instead of having to click the search button?

Thanks for all your help!
May 20 '10 #5
Jim Doherty
897 Expert 512MB
@rhapsodysolutions
You,re welcome with the db......The reason I picked up on your last post particularly was the long list of code blocks for each where clause. When you get chance look at the AddToWhere function that is in that db disassemble it, understand it and see how it is being called in the search routine. It cuts out needing to code block for each and every control used as criteria because it deals only with the value that is actually IN the control.

To make your search button just search when you hit enter make its property 'default' button setting = yes in the properties dialog for the button
May 20 '10 #6
@Jim Doherty
Thanks for the tip with the enter button! I will lookup the AddToWhere function as you recommend.

Could you please also check what is wrong with my "reset" code posted in #5 above.

And finally, how do I export the results into Excel? or should I post it in a seperate thread?

Thanks
May 20 '10 #7
Jim Doherty
897 Expert 512MB
@rhapsodysolutions
There is nothing wrong with you combobox clearance code. It does what it programmed to do...clear a combo and any others on screen. It is not programmed to do anything else. What do you expect it to do?

As for your excel output I dont want to keep promoting that db as the panacea to everything but you really have to LOOK at it. It has a full blown export to excel code module synchronised to the results of any forms underlying dataset. It is called by ONE line of code from any form.
May 20 '10 #8
@Jim Doherty
It does clear the combo boxes but does not reset the form in its "initially opened" state, going back to the first record. That is what I need it to do.

Thanks.
May 20 '10 #9
TheSmileyCoder
2,321 Expert Mod 2GB
You have to reset the Recordsource of the form:
Expand|Select|Wrap|Line Numbers
  1. Me.RecordSource="select * from Master_Media_List"
You should post any new question in a seperate thread, but before doing so try to search this forum (or the VBA browser) for Docmd.TransferSpreadSheet.
May 20 '10 #10
Jim Doherty
897 Expert 512MB
@rhapsodysolutions
Not that this makes much difference...but is your form single view or tabular if all you want is to go to the first record then create a command button using the wizard and look at what is offered to you for record navigation ie goto first next last previous. Just create a button and use the code that it generates in your own routine ie: rather obviously pasted into your reset procedure
May 20 '10 #11
TheSmileyOne, and Jim, Thank you for all your help. I have been able to complete the project. The search works fine. The reset button works fine too after resetting the RecordSource. I will go ahead and do some more poking around into Jim's db for the Excel export.
May 20 '10 #12
Jim Doherty
897 Expert 512MB
@rhapsodysolutions
Good luck with your project :)
May 20 '10 #13

Post your reply

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

Similar topics

3 posts views Thread by vgrssrtrs | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.