Form with 7 combo boxes

[rhapsodysolutions]

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.

[TheSmileyCoder]

This is VBA code that allows you to dynamically createa SQL where clause.

Dim strWhere as string
 
If not isNull(Me.Cmb1) Then
   if strWhere & "" <>"" then strWhere= strWhere & " AND "
  strWhere=strWhere & " myField2='" & me.Cmb1 & "'"
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.

[rhapsodysolutions]

@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!

[Jim Doherty]

@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

[rhapsodysolutions]

@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:

    Dim strWhere As String
 
    strWhere = " Where 1 = 1"
 
    If Not IsNull(Me.cmb_MediaType) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " MediaType='" & Me.cmb_MediaType & "'"
    End If
 
    If Not IsNull(Me.cmb_City) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " Industry='" & Me.cmb_City & "'"
    End If
 
    If Not IsNull(Me.cmb_market) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " City='" & Me.cmb_market & "'"
    End If
 
    If Not IsNull(Me.CmbMediaName) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " MediaName='" & Me.CmbMediaName & "'"
    End If
 
    If Not IsNull(Me.cmb_Client) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " Client='" & Me.cmb_Client & "'"
    End If
 
    If Not IsNull(Me.cmb_Lastname) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " Lastname='" & Me.cmb_Lastname & "'"
    End If
 
    If Not IsNull(Me.cmb_Title) Then
       If strWhere & "" <> "" Then strWhere = strWhere & " AND "
      strWhere = strWhere & " Title='" & Me.cmb_Title & "'"
    End If
 
 
        strSql = "select * from Master_Media_List" & strWhere & ";"
        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:

Private Sub btn_Reset_Click()
 
    Dim ctl As Control
    For Each ctl In Me.Controls
 
    Select Case ctl.ControlType
 
    Case acComboBox
    ctl.Value = Null
 
    End Select
    Next ctl
 
    Me.Requery
 
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!

[Jim Doherty]

@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

[rhapsodysolutions]

@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

[Jim Doherty]

@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.

[rhapsodysolutions]

@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.

[TheSmileyCoder]

You have to reset the Recordsource of the form:

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.

[Jim Doherty]

@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

[rhapsodysolutions]

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.

[Jim Doherty]

@rhapsodysolutions
Good luck with your project :)