Need 2nd & 3rd combo box for date & status filter

[Rogue0801]

Hi All,

I have tried different samples of combo box and still unable to finalize my form:

First let me tell you what I got and what I need:

On an unbound form I have created a subform [frmMaster_sub] from MASTERLIST QUERY. This QUERY contains
SNAME, LPODate, QTY, UNIT PRICE, AMOUNT and STATUS.

On the FORM (frmStatus) I Have created an unbound Combo boxe:
COMBO – Created created from value list

The idea is from [COMBO] I can select a particular SUPPLIER and it will list all the the purchases for the specific supplier, then I would like to add another 2 combos for date and status. wherein if I select a particular Date and status from a combo box then it will show all purchase with the same Date and status for the supplier.

So far I'm having the following vb code:

Private Sub Command2_Click()
If (Eval("[Forms]![frmStatus]![Combo] Is Null")) Then
   MsgBox "Please select value first", vbOKOnly, "No Selection"
ElseIf (Eval("[Forms]![frmStatus]![Combo] Is Not Null")) Then
strSQL = "Select * from MASTERLIST where"
strSQL = strSQL & " SNAME=" & "Forms!frmStatus!Combo"
Me!frmMaster_sub.Form.RecordSource = strSQL
End If
End Sub
 
 
Private Sub Command3_Click()
strSQL = "Select * from MASTERLIST"
Me!frmMaster_sub.Form.RecordSource = strSQL
 
End Sub

This works perfectly, where I am stuck now is how do I do the second & third filter. Any guidance would be greatly appreciated.


Thanks,

Rogue

[jimatqsi]

Rogue0801, I often do something like this. What I suggest you do is to make a subroutine (mine is usually called FilterForm()) that you call in the AfterUpdate event of all 3 of these combo boxes. FilterForm will build a SQL string; really it builds a WHERE string to add to the SQL string you show in Command3_Click(), and then plug the SQL into RecordSource as you show above.

This is a very simple strategy for easily adding any number of filters. You can require any combination of the filters to be filled in or not. All the work gets done in FilterForm(), all the filters just call it in the AfterUpdate event.

Jim

[jimatqsi]

Also, Rogue0801, pay attention to your object names. Command2 and Command3 might be meaningful names for your buttons, but there might be a better name you can use for each of those. As the number of forms and objects grow in your database, you'll appreciate if your code talks to you a little more clearly about where a function comes from, what object it represents.

[Rogue0801]

Dear jimatqsi,

Appreciate your kind revert, however if it's not that much to ask, would you please help me establish your suggestion in my database? Apology for that as not that much knowledgeable in VB nor access...I have attached here my sample database for your reference.


Many Thanks,

Rogue :)

[jimatqsi]

I won't write the code for you but I'll give you an example from one of my live applications. This is in an inventory table maintenance program. There are 8 potential filters that may or may not be filled in.
Here's the code

Private Sub FilterForm()
 
   On Error GoTo FilterForm_Error
   If Me.Dirty Then Me.Dirty = False
 
 
strSQL = "SELECT dbo_CI_Item.ItemCode, dbo_CI_Item.PriceCode, dbo_CI_Item.ProductLine, dbo_CI_Item.UDF_STATUS, dbo_CI_Item.UDF_BRAND, " & " dbo_CI_Item.UDF_FREE_FREIGHT_LEVEL,UDF_F2_COST, " & _
        " dbo_CI_Item.UDF_E2_PRICE, dbo_CI_Item.DateUpdated, dbo_CI_Item.UserUpdatedKey, UseInSO, UseInPO , dbo_CI_Item.UDF_CASE_PACK, dbo_CI_Item.UDF_CARTON_PACK, " & _
        " dbo_CI_Item.UDF_MAP, dbo_CI_Item.StandardUnitCost, dbo_CI_Item.StandardUnitPrice " & _
        " FROM dbo_CI_Item " & " where 1=1 " & "ORDER BY dbo_CI_Item.ItemCode "
 
If Len(Nz(Me.SKU_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and ItemCode like '[itemcode]%'")
    strSQL = Replace(strSQL, "[itemcode]", Me.SKU_filter)
End If
 
If Len(Nz(Me.PriceCode_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and PriceCode='[PriceCode]'")
    strSQL = Replace(strSQL, "[PriceCode]", Me.PriceCode_filter)
End If
 
If Len(Nz(Me.ProductLine_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and ProductLine='[ProductLine]'")
    strSQL = Replace(strSQL, "[ProductLine]", Me.ProductLine_filter)
End If
 
If Len(Nz(Me.Brand_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and UDF_BRAND like '[UDF_BRAND]%'")
    strSQL = Replace(strSQL, "[UDF_BRAND]", Me.Brand_filter)
End If
 
If Len(Nz(Me.Status_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and UDF_STATUS='[UDF_STATUS]'")
    strSQL = Replace(strSQL, "[UDF_STATUS]", Me.Status_filter)
End If
 
If Len(Nz(Me.FreeFreight_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and UDF_FREE_FREIGHT_LEVEL='[UDF_FREE_FREIGHT_LEVEL]'")
    strSQL = Replace(strSQL, "[UDF_FREE_FREIGHT_LEVEL]", Me.FreeFreight_filter)
End If
 
If Len(Nz(Me.UseInSO_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and UseInSO='[UseInSO]'")
    strSQL = Replace(strSQL, "[UseInSO]", Me.UseInSO_filter)
End If
 
If Len(Nz(Me.UseInPO_filter)) > 0 Then
    strSQL = Replace(strSQL, "1=1", "1=1 and UseInPO='[UseInPO]'")
    strSQL = Replace(strSQL, "[UseInPO]", Me.UseInPO_filter)
End If
 
    Me.RecordSource = strSQL
 
   On Error GoTo 0
   Exit Sub
 
FilterForm_Error:
 
    cswLogError Application.CurrentObjectName, "Error_ReportSelection_lb_AfterUpdate", Now, Err.Number, Err.Description
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FilterForm of VBA Document Form_Inventory_MassEdit_frm"
     Resume Next
 
End Sub

All of the text boxes that are filters have this code

Private Sub SKU_filter_AfterUpdate()
    FilterForm
End Sub

It's pretty simple. Watch out for the difference between strings and numerics. The string values you test against in the Where clause must have the quotes around them.

Jim

[Rogue0801]

Hi Jim,

Sorry for responding too late, but as I go through the codes you provided I really feel confuse. In this case, may I request you to attach a sample database for this code, that might help me understand it better.

I would really appreciate that from you.


Thanks,

Rogue

[zmbd]

Please read thru the following articles in our "insights" section.
One of these should be a good fit for what you're after and the others should provide some points for thought for other forms you might need.
The coding is at a moderate level; however, there is a lot of reading so if you happen to get stuck, please don't hesitate to post back with any specific questions you might have. Remember, the more detail you give the better we can help!

-filtering-

• Microsoft Access / VBA Insights Sitemap
• 45.How to select Records in an Access Subform from a Combo Box
  
• 46.Cascaded Form Filtering
• 55.Multiple Combobox Filter with Clear Option
• 168.Example Filtering on a Form.

[Rogue0801]

Dear zmbd,

Thanks for your intent to help, however you are really right I will get stuck to this readings and actually I have read some of this before and thru this I got inspired to apply it in my database. And I got one which suits my requirement, but the problem is I dont know how I put additional filters on it especially for date.

I would really appreciate if you could help me with this, I already got a combo wherein I can select the supplier, what I want now is to add 2 new combos wherien I could select the status and specific date range, as I mentioned in my original post.

[jimatqsi]

Here's an example I modified quickly from a working installation. Enter a value in any green box(es) to filter the screen content.

Jim

[zmbd]

I just want to make sure that I understand what you are after:
cbox_1 = somevalue_1
cbox_2 = somevalue_2
cbox_3 = somevalue_3

You want to be able to filter the data in the form based upon one or more of the combobox values
air code:

(provided that none of the cbox_* are null or "")
SELECT *
FROM tbl_exmple
WHERE ([field_1] = cbox_1
   AND [field_2] = cbox_2
   AND [field_3] = cbox_3

The first thing I would do is change your form.
Open up the header section of the bound form.
Place your combo boxes in the header section.
This is from a production database where I can lookup my equipment inventory (there's more to this form, I just show the top section):

You'll have to forgive the redacted areas of the picture. It has some sensitive information :).

The form is bound to my inventory table.

Now you'll note that my combo boxes are "unbound" that is because they don't directly reference anything on this form. The first has a query for the row source that pulls the human readable manufacture name (the id is the bound column) the second cascades from the first by showing only the products from the selected manufacture. If you will read thru the article on cascading comboboxes you'll get an idea as to what I've done.

The form detail is blank when loaded.
This is because the form filter is set so that no records are returned because this is a HUGE table and I don't need to return 10,000 records on open.

On first open, only the controls in the bottom, the show all button, and this first "start here" is visible.
The first CBO is set, the afterupdate forces the second CBO to requery the products table and then makes the second cbo visable. The second CBO is set, the afterupdate event then does:

Private Sub z_ctrl_cbo_filteronproduct_AfterUpdate()
Dim zsql As String
Dim zprod As Integer
'
zprod = Nz(Me.z_ctrl_cbo_filteronproduct.value, 0)
If zprod > 0 Then
    zsql = "[inventory_fk_products] = " & zprod
    With Me
        .Filter = zsql
        .FilterOn = True
        .z_ctrl_cmd_clearfilter.Visible = True
    End With
Else
    'Clear the details, inventory will never have a 0 or lesss product FK.
    zsql = "[inventory_fk_products] = 0"
 
    With Me
        .Filter = zsql
        .FilterOn = True
        .z_ctrl_cbo_filtermanufacture.SetFocus
        .z_ctrl_cmd_clearfilter.Visible = False
        .z_ctrl_cbo_filteronproduct.Visible = False
    End With
End If
End Sub

So, in my case, I use a cascading combobox and I wait until the user has done some choosing and then do the final filter on the bound form. This works because I have a table with manufactures and another table with products with a relationship back to manufacture. The product table is then has a relationship to an inventory table. The form is bound to the inventory table.

In your case, you may want to do something different; however, the key to this is writing that form filter.

[Rogue0801]

Hi zmdb & jims,

Both of your assistance are really helpful and to what I need and I am really thankful of that, but I still have one concern. Hope you still didn't fed up on me, as I really dont have that much understanding in terms of VB. To begin with I have attached my sample dbase here for your reference...I am not sure were is my mistake but error message displays "Runtime error 3131" syntax error in from clause.

[zmbd]

Rogue0801
Nope, not upset with you at all - I learn as much each time I help as you do. No better way to learn than to teach IMHO.

Please go here and follow the trouble shooting steps:
> Before Posting (VBA or SQL) Code

Once you clear the compiler errors (you may have to do this several times as the compiler stops on the first error found)

Then run your code.... if you have error trapping (look for something like this
On Error Goto ErrorHandelingCode) in your code, just for now, comment it out by placing a single quote, " ' ", in front of it.

Now run your code, when you get the error message, click on the "DEBUG" button. The VBE should open and the erroring line should be highlighted. Make note of this line!
Now in the VBE press the stop button (or goto the MenuBar>Run>Reset) to stop the execution.

Remember that error line? Good ;-)
Copy and paste that line and maybe the a few lines before it... especially any line that includes a defining string used in that from statement. More than likely you have a malformed string and we need to figure out why and it will be easier to do if we have the same information....
Others may want to guess at the solution, I personally find that frustrating when on the receiving end.


As for the attachemnt.
At the risk of making you upset with me - I don't normally d/l un-requested files. Please understand that many of us cannot or will not d/l un-requested attachments. This has absolutely nothing to do with who you are... In my case, such d/l are prohibited by my ITSec staff. For many others, it is part of "safe computing/best practices" not to d/l such files.