VBA Code to Retrieve Records from Combobox 2

[FrankMars]

I have a continuous form which I filter the results by selections through 2 combo boxes and clicking a button in the header portion of the form. My code on the button is as follows:

Private Sub Command17_Click()
Dim mySearch As String
mySearch = "Select * from qMarconL where (([City] = '" & Me.Combo1 & "') AND ([LastName] = '" & Me.Combo2 & "')) "
Me.qMarconLsubform.Form.RecordSource = mySearch
Me.qMarconLsubform.Form.Requery
End Sub

I get the correct results in the subform in the details section of the continuous form when both combo boxes are filled out but don't get any records if only one combo box is filled out.

I would like to be able to fill out one combo box and get results. How can I get results if only one combo box is filled out? I would also like to get results of all records if neither of the combo boxes are filled out.

 

[MajP]

You can check the combos.

 Dim mySearch As String
 if not isnull(me.combo1) and not isnull(me.combo2) then
   mySearch = "Select * from qMarconL where (([City] = '" & Me.Combo1 & "') AND ([LastName] = '" & Me.Combo2 & "')) "
 elseIf not isNull(me.combo1) then
   mySearch = "Select * from qMarconL where City = '" & Me.Combo1 & "'"
 else if not isNull (me.combo2) then
   "mySearch = "Select * from qMarconL where LastName = '" & Me.Combo2 & "'"
 else
   "mySearch = "Select * from qMarconL"
 end if 
 Me.qMarconLsubform.Form.RecordSource = mySearch
 Me.qMarconLsubform.Form.Requery
 End Sub

Also, I believe this may work by itself

mySearch = "Select * from qMarconL where (([City] = '" & Me.Combo1 & "' or isNull(me.comb01)) AND ([LastName] = '" & Me.Combo2 & "' or isnull(combo2))) "

 

[FrankMars]

MajP- The first code works. I wasn't able to get the second code to work.

Thank you.

 

[FrankMars]

MajP,

I ran into another problem. Your code worked but when adding an additional combo box to filter by, it only works when either all, one or none of the combo boxes are filled out. I plan on adding 10 or so more combo boxes to filter by. Is it possible to fill out any combination of combo boxes (some not filled out) and have the results filter to their values? This is what I have so far:

Private Sub Command2668_Click()

Dim mySearch As String

If Not IsNull(Me.cboxHouseStatusFilter) And Not IsNull(cboxTownFilter) And Not IsNull(cboxBuilderFilter) Then
mySearch = "Select * from asqHouseAll where (([House- Status] = '" & Me.cboxHouseStatusFilter & "') AND (Town = '" & Me.cboxTownFilter & "') AND (Builder = '" & Me.cboxBuilderFilter & "') ) "
ElseIf Not IsNull(Me.cboxHouseStatusFilter) Then
mySearch = "Select * from asqHouseAll where [House- Status] = '" & Me.cboxHouseStatusFilter & "'"
ElseIf Not IsNull(Me.cboxTownFilter) Then
mySearch = "Select * from asqHouseAll where Town = '" & Me.cboxTownFilter & "'"
ElseIf Not IsNull(Me.cboxBuilderFilter) Then
mySearch = "Select * from asqHouseAll where Builder = '" & Me.cboxBuilderFilter & "'"
Else
mySearch = "Select * from asqHouseAll"
End If

Me.asfHouseAllSubform.Form.RecordSource = mySearch
Me.asfHouseAllSubform.Requery

​

End Sub

Thanks in advance.

 

[Andrzejek]

You may try this approach:

Dim mySearch As String

mySearch = "Select * from qMarconL where 1=1 "

If Not IsNull(me.combo1) Then
  mySearch = mySearch & " and City = '" & Me.Combo1 & "' "
End If

If Not IsNull(me.combo2) Then
  mySearch = mySearch & " and LastName = '" & Me.Combo2 & "' "
End If

...

Me.qMarconLsubform.Form.RecordSource = mySearch
Me.qMarconLsubform.Form.Requery
End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[FrankMars]

Andy - thanks, works well. Now I'm having another problem. I added additional combo boxes and I'm attempting to get recordset results which are in the range between the 2 values in those combo boxes. This is the code I have so far which is not working due to the last If Not statement:

Dim mySearch As String

mySearch = "Select * from qMarconL where 1=1 "

If Not IsNull(me.combo1) Then
mySearch = mySearch & " and City = '" & Me.Combo1 & "' "
End If

If Not IsNull(me.combo2) Then
mySearch = mySearch & " and LastName = '" & Me.Combo2 & "' "
End If

If Not IsNull(me.combo3) Then
mySearch = mySearch & " and Section = '" & Me.Combo3 & "' "
End If

[highlight #FCE94F][highlight #FCE94F]If Not IsNull(me.combo4) Then
mySearch = mySearch & " and Size Between (('" & Me.combo4 & "') and ('" & Me.combo5 & "')) "
[/highlight]End If[/highlight]

Me.qMarconLsubform.Form.RecordSource = mySearch
Me.qMarconLsubform.Form.Requery
End Sub

Thanks again in advance.

 

[Andrzejek]

Please use TGML tags to show your code.

Also, use more meaningful names for your controls

If Not IsNull(me.cboFrom) And Not IsNull(me.cboTo) Then
 mySearch = mySearch & " and Size Between '" & Me.cboFrom & "' and '" & Me.cboTo & "'"
End If

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I would add some code to debug the mySearch variable and get rid of some ()s. I agree with renaming the controls to make the vba more understandable.

If Not IsNull(me.combo4) Then
   mySearch = mySearch & " and Size Between '" & Me.combo4 & "' and '" & Me.combo5 & "' "
End If
Debug.Print "mySearch: " & mySearch 
Me.qMarconLsubform.Form.RecordSource = mySearch
Me.qMarconLsubform.Form.Requery

I doubt you need to use Requery after changing the subform's record source but I could be wrong.

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Andy/Duane- I changed the code as suggested and I'm getting "Run-time error '2001' - You canceled the previous operation". ????. It seems to be a problem with the last If Not statement but I cant find it. The error does not come up if nothing is in the from/to fields. My code is as follows:

Private Sub Filter_Click()

Dim mySearch As String

mySearch = "Select * from asqHouseAll where 1=1 "

If Not IsNull(Me.cboxHouseStatusFilter) Then
  mySearch = mySearch & " and [House- Status] = '" & Me.cboxHouseStatusFilter & "' "
End If

If Not IsNull(Me.cboxTownFilter) Then
  mySearch = mySearch & " and Town = '" & Me.cboxTownFilter & "' "
End If

If Not IsNull(Me.cboxBuilderFilter) Then
  mySearch = mySearch & " and Builder = '" & Me.cboxBuilderFilter & "' "
End If

If Not IsNull(Me.cboxSectionFilter) Then
  mySearch = mySearch & " and Section = '" & Me.cboxSectionFilter & "' "
End If

If Not IsNull(Me.cboxNSFilter) Then
  mySearch = mySearch & " and [North or South] = '" & Me.cboxNSFilter & "' "
End If

[highlight #FCE94F]If Not IsNull(Me.cboxLandSF1Filter) And Not IsNull(Me.cboxLandSF2Filter) Then
  mySearch = mySearch & " and [Land- SF] Between '" & Me.cboxLandSF1Filter & "' and '" & Me.cboxLandSF2Filter & "'"
End If[/highlight]

Debug.Print "mySearch: " & mySearch
Me.asfHouseAllSubform.Form.RecordSource = mySearch
Me.asfHouseAllSubform.Form.Requery

End Sub

 

[Andrzejek]

Which line of code creates your error?

Your field [tt][Land- SF][/tt] - how is it declared? As Text? As Number?
Is if is a Number, do not use single quotes in your mySearch for that " and [Land- SF] Between " part.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[FrankMars]

Andy - my field is declared as a number. I removed all single quotes from that line as suggested and it worked. Thank you very much!

 

[dhookom]

FrankMars,
If it worked based on assistance from Andy, you should click the "Great post? Star it!" link to suggest the thread is closed and credit Andy.

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Duane - will do.