Filtering a subform with a combo box within a Navigation Form page - newbie using macros not VBA

[ScottishFi]

Hi, first time poster here as I'm baffled and getting desperate. It cannot be as complicated as I'm making it.

I have a Navigation Form with a Reports Tab which calls a form called 'PO list with Dept tabs and Subform'.

This form has four combo boxes to filter an open subform 'Purchase Details Extended Subform' which is set to continuous form but could be a datasheet. I need to be able to print the selection so I have that button with the combo boxes on the 'PO list with Dept tabs and Subform' form.

I cannot get this to work and have googled and tried so many different approaches that I'm utterly confused and could cry. Could you give me a clear best way to do it? I don't know VBA except to direct copy it but have a reasonable handle on macros, I'm just lost when it comes to creating the expressions. Logic tells me I should use SetFilter but I don't seem to have the path right for the forms - I know there is the Navigation Subform control in there somewhere but I'm not sure where.

I have table [Dept Codes] for the combo 'cbodepartment' with the bound column being [Dept Code] and no control source. I would assume I just simply SetFilter using a Where Condition == [Dept Code] = (pathway to my combo box), is that the right approach?

Thanks so much for any suggestions before I lose any more days of my life to this. If I've not posted correctly or should give more info please let me know.

Thanks!

[ranman256]

if you have 4 combo boxes, and 1 or all 4 can be applied to the filter, then,
you must check all 4 to determine what filter to apply. (in all 4 afterupdate events)

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

but your filter would be in the subform...
me.subform.form.filter = sWhere
me.subform.form.filterOn=true

sub cboSt_afterupdate
  FilterSub
end sub

sub cboCity_afterupdate
  FilterSub
end sub

sub cboZip_afterupdate
  FilterSub
end sub

'----------------
sub FilterSub()
'----------------
dim sWhere as string 


sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"


If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[ScottishFi]

Thanks for replying, although sorry I'm not totally following. I can see that I put the following in the AfterUpdate for each combo box:

sub cboSt_afterupdate
FilterSub
end sub

but then where do I put the rest of the text? Do I create a standalone macro which the above code calls each time? I'm a VBA novice but can just about work out the logic!

This is actually more than I was intending to build into the form, really I only need to use one combo at a time so maybe OpenForm/Report would be better so the user has to come out and refilter each time.

I'll have a play with this and see if I can get anywhere, thanks again.

Fiona

if you have 4 combo boxes, and 1 or all 4 can be applied to the filter, then,
you must check all 4 to determine what filter to apply. (in all 4 afterupdate events)

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

but your filter would be in the subform...
me.subform.form.filter = sWhere
me.subform.form.filterOn=true

sub cboSt_afterupdate
  FilterSub
end sub

sub cboCity_afterupdate
  FilterSub
end sub

sub cboZip_afterupdate
  FilterSub
end sub

'----------------
sub FilterSub()
'----------------
dim sWhere as string 


sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"


If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[June7]

Sub FilterSub()
...
End Sub

Would be a procedure behind the form. Placed in module just as ranman shows - following the AfterUpdate procedures. However, the dash lines are not necessary.

[ScottishFi]

Great thanks for coming back to me, I'll have a go with this, see if I can get my head round the VBA.

[ScottishFi]

I'm not sure if this will bump back up or if I'll need to repost but here goes. I'm absolutely lost with this, have tried so many options. I'm afraid I really couldn't follow the code I was very kindly given or where to put it. Have posted the DB here in the hope that somebody can give me some help. Ideally I'd love to use the macro builder as I'll have more chance of understanding it and being able to adapt as needed.

Main form - Navigation form
Reports tab points to - PO list with dept tabs and subform
The subform is Purchase Details Extended Subform

The combos are all set up with bound column 1 being the relevant code. They are set up from the wizard using the 'find a record' option but I've tried filters, Where commands and loads of other options. I also need to be able to print the selections so don't really want to have them pop up a new datasheet screen which won't show command buttons.

[June7]

Finally hit me - you are using a Navigation Form, not just form with Tab control.

Navigation Form presents different challenges when referencing subforms. Review https://www.accessforums.net/showthread.php?t=32053

And you add complexity with Navigation Form target objects that have their own subforms.

I don't like and have never used Navigation Form because of frustrations they provoke.

However, the form filtering you want to do is fairly simple and not really impacted by the use of Navigation form as none of the code requires referencing any subform behind other tabs.

Not seeing any VBA filtering code. Comboboxes and button are set to use [Embedded Macro]. I never use macros. Suggest you switch to [Event Procedure] and build VBA as per example already provided. Here's an example of a search form using VBA http://allenbrowne.com/ser-62.html.

Here's what I did to make the filtering work:

1. add [Department Code], [Finance Code], [Supplier ID], [Submitted By], [Send To] fields in query [Purchase details extended query]

2. change report [New POreport] RecordSource to [Purchase details extended query] (two-fold purpose: 1. no need for another query object and 2. eliminate the input popup prompt)

3. rename comboboxes to cboDep, cboBud, cboSup

4. change [Embedded Macro] to [Event Procedure] in the event property for comboboxes and button

5. rename subform container control [Purchase details extended subform] to ctrPurchases

6. VBA code behind [PO list with dept tabs and subform]:

Option Compare Database
Option Explicit

Sub cboDep_AfterUpdate()
    FilterSub
End Sub

Sub cboBud_AfterUpdate()
    FilterSub
End Sub

Sub cboSup_AfterUpdate()
    FilterSub
End Sub
 
Private Sub Print_selection_Click()
DoCmd.OpenReport "New POreport", acViewPreview, , "[Purchase Order ID]=" & Me.ctrPurchases![Purchase Order ID]
End Sub

Sub FilterSub()
Dim sWhere As String
sWhere = "1=1"
If Not IsNull(cboDep) Then sWhere = sWhere & " and [Department Code]=" & cboDep
If Not IsNull(cboBud) Then sWhere = sWhere & " and [Finance Code]=" & cboBud
If Not IsNull(cboSup) Then sWhere = sWhere & " and [Supplier ID]=" & cboSup
If sWhere = "1=1" Then
    Me.ctrPurchases.Form.FilterOn = False
Else
    Me.ctrPurchases.Form.Filter = sWhere
    Me.ctrPurchases.Form.FilterOn = True
End If
End Sub

Recommend not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be DeptCode or Dept_Code.

[ScottishFi]

Hi thanks so much for this. I've come to realise that Nav forms are a bit of a nightmare, it's the first time I've used one and was distracted by it's sleek and shiny looks! Definitely going back to switchboards in future.

I've followed your changes and copied in the code (I put it in 'on current' for the form, was that right?) and cboDep is working perfectly but the other two combos both bring up the parameter dialog box when I click on them, then if I enter the correct ID it doesn't filter. I've double checked the properties for all the combos and they look exactly the same. This happens whether or not I have selected a department first or not. Have I missed something or does there need to be something else in the procedure? When I hit Debug it queries this line

Me.ctrPurchases.Form.FilterOn = True


Sorry to be a pest, I really appreciate the assistance.
Thanks again

Fiona

[June7]

IMHO, the Switchboard Manager form construct is more of a headache to understand and manage. Never used it either. There is a reason MS removed it from the default ribbon setup and you have to go find it if you want to use.

None of the code I posted uses OnCurrent event.

No idea why the other two combos aren't working. Would have to review your modified db.

What is the exact error message? Are you sure you named the subform container control as instructed? Again, would have to review your modified db.

If you truly did the edits I described then everything would work.

[ScottishFi]

Thanks for responding

I'm even more baffled now. I did the changes the first time in my full DB not the nonames one. I've just repeated them with the nonames database to see if it was my error and now two combo boxes are working (department and supplier) but not budget.

I didn't know where to put the code you said goes 'behind' the form, that's why I used On Current. That must be my mistake. I did rename the container, I hope I've done it correctly. It's so nearly there, I just don't have the VBA knowledge to know how to tweak it.

I've attached the updated DB, if you have time to review it I'd be hugely grateful.

Thanks

[June7]

Field [Finance Code] is not included in the form RecordSource query [Purchase details extended query].

Should not allow edit of Department, Description, Company fields on this form. Set those textboxes as Locked Yes and TabStop No.

Don't need PK fields from the lookup tables in this query. Pull [Department Code], [Finance Code], [Supplier ID] from [Purchase Orders].

[ScottishFi]

Got it, it's working! Thank you sooo much, couldn't see the problem for looking at it. You're a star, I cannot express how happy I am.