Combobox with all option (1 Viewer)

[mmaybe]

Hello,
I made a combobox with value list (All,Paid,Unpaid) to filter a subform
and put a VBA code in After Update Event, it works for Paid and Unpaid because it's mach the same choices in my query. I want help to make (All)option display all paid and unpaid record

Code is:

Private Sub cboStatus_AfterUpdate()
Dim cstatus As String
cstatus = " select * from Client_Invoice_Status where ([Company]= '" & Me.cboCompany & "') And ([Status]= '" & Me.cboStatus & "') And ([PolicyType]= '" & Me.cboPolicyType & "' )"
Me.Client_Status.Form.RecordSource = cstatus
Me.Client_Status.Form.Requery
End Sub

Thank you

 

[plog]

What you do is compile your WHERE statement dynamically in the VBA. First test to see if the value is 'All', if it is you don't do anything because there's no need to apply criteria. If the value is not 'All' you add it to your WHERE:

pseudo code:


strWhere="(1=1)"
if (cbo1<>"All) Then strWhere = " AND (Field1 = "' & cbo1 & "')"
if (cbo2<>"All) Then strWhere = " AND (Field2 = "' & cbo2 & "')"
...
Me.Client_Status.Form.RecordSource = cstatus & strWhere

 

[Micron]

There seems to be only one combo, so why not just apply a filter using the selected item as long as it's not "All"?
EDIT
Sorry, was going by the explanation rather than the code. I suppose one could simply use the WHERE part of that select statement as a filter though.

 

[Dreamweaver]

SELECT "*" As SaveName,"All" As SaveType FROM "same table as in other side of union" UNION SELECT Your sql FROM your tableWHERE your criteria



You will need to edit it but will do what you want

 

[theDBguy]

Hi mmaybe. If I understand it correctly, you could also try the following:

cstatus = " select * from Client_Invoice_Status where ([Company]= '" & Me.cboCompany & "') And ([Status]= '" & Me.cboStatus & "' OR 'All'='" & Me.cboStatus & "') And ([PolicyType]= '" & Me.cboPolicyType & "' )"

 

[mmaybe]

Hi mmaybe. If I understand it correctly, you could also try the following:

cstatus = " select * from Client_Invoice_Status where ([Company]= '" & Me.cboCompany & "') And ([Status]= '" & Me.cboStatus & "' OR 'All'='" & Me.cboStatus & "') And ([PolicyType]= '" & Me.cboPolicyType & "' )"

I'm grateful to all of you but this simply what I need.
Thanks theDBguy :]

 

[Dreamweaver]

SELECT "*" As SaveName,"All" As SaveType FROM "same table as in other side of union" UNION SELECT Your sql FROM your tableWHERE your criteria



You will need to edit it but will do what you want

I'm not sure if this is what you really want after reading your post again as this will require code in the afterupdate event like


If Me![Combo] = "*" Then
Do something

Else
Do Something
End If

 

[theDBguy]

I'm grateful to all of you but this simply what I need.
Thanks theDBguy :]

Hi. You're welcome! We were all happy to assist. Good luck with your project.

 

[Micron]

Nice trick (at least that's what I'd call it).
Given that there's only one AND in the first part, maybe not problematic, but never having used that technique I'd like to ask if there are multiple AND's wouldn't it be safer to wrap the segments in parentheses to ensure correct evaluation? As in "(SELECT...WHERE...AND...AND...) OR ( ... ). Thinking that you could end up with just a single (last?) AND getting paired with the OR. This is based on my experience with poorly paired logical operators when I don't use grouping.