filtering Report on the Fly

[gsbatch1]

RESOLVED - filtering Report on the Fly

I have a form with 5 fields. The 5 fields are choices from the columns (20) in a table and the person can select which fields they want in the report. If the person selects "Supervisor" as a field in any of the 5 combo boxes, then I want another combo box (Not visible) to become visible and allow for filtering on a specific Supervisor in the report on the fly.

My problem is it will not filter on the Supervisor choice.

Sub MakeReport()
On Error GoTo Err_MakeReport

'Open report in design view to write properties to objects
If cboSupName.Visible = True Then
DoCmd.OpenReport "rptCustom", acDesign, , [Supervisor] = [Forms]![frmChooseFields]![cboSupName]
Else
DoCmd.OpenReport "rptCustom", acDesign
End If

How do I make this happen only if "Supervisor" is selected as a choice in one of the 5 fields?

 

[CBrighton]

Try this:

Sub MakeReport()
 
dim strWhere as string
 
On Error GoTo Err_MakeReport
 
'Open report in design view to write properties to objects
If cboSupName.Visible = True Then
     strWhere = "[Supervisor] = '" & forms!frmChooseFields!cboSupName & "'"
     DoCmd.OpenReport "rptCustom", acDesign, , strWhere
Else
     DoCmd.OpenReport "rptCustom", acDesign
End If

:edit:

The above is based on the assumption that the [supervisor] field in the table is a text type.

 

[gsbatch1]

Thanks cBrighton, that gives me more information about how it should work. I tried it and still I am getting the entire report.
It will not filter on Supervisor.

I have attached a copy of my DB for review

 

[vbaInet]

I've not looked at your db but why are you opening the report in design view? Change acDesign to acViewPreview.

 

[CBrighton]

vbaInet is right.

I only looked at the piece of code I was going to change, didn't even notice it was opening in design mode.

I can have a look at your db if you like, but only if you convert a copy to 2003 format.

 

[gsbatch1]

The report is opening in acDesign so that it can create the report on the fly based on the fields chosen in the form. It only does this in the background and never actually shows the report in design view.

I have attached a 2003 copy.

 

[CBrighton]

I have updated the attached, it works fine on 2003.

Note: I had to change

DoCmd.OpenReport "rptCustom", [B]acViewReport[/B]

to

DoCmd.OpenReport "rptCustom", [B]acPreview[/B]

If you need them set to acViewReport then please alter the VBA before using.

 

[gsbatch1]

Thanks CBrighton.

I see it works great now, what had to be changed?

 

[CBrighton]

Not too much to be honest.

I added in the supervisor combobox as it was missing in your example database. Then I added AfterUpdate events to your existing comboboxes to display the field and finally I copied the code I provided earlier in the thread into the MakeReport sub (changing the supervisor combobox name as I used a slightly different one).

The other difference is I used my code at the end of MakeReport so it was the acPreview/acViewReport which was being filtered by the If statement rather than the acDesign at the beginning of the sub (didn't have all the code before you posted the database).

 

[gsbatch1]

Yeah I must have deleted the cmbSupervisor box when I was copied over my DB and deleted info. My bad.

Also, one last thing.

Is there a way to make the cboSupervisor disappear and reappear upon selection in any of the fields?

Right now, If I select Supervisor in field 2, and go on to field 3 and then change field 2 to anything other than Supervisor, the cboSupervisor box still shows. It should go invisible again.

 

[CBrighton]

Yes, but it'll be slightly longer code.

You can't simply hide it when anything other than supervisor is selected, it needs to specifically check all 5 fields. Otherwise it would hide it as soon as you choose your next field.

For example:

Private Sub cboField5_AfterUpdate()
If cboField5 = "supervisor" Then
    cboSupervisor.Visible = True
End If
End Sub

Becomes

Private Sub cboField5_AfterUpdate()
If cboField5 = "supervisor" Then
    cboSupervisor.Visible = True
End If
If cboField1 <> "supervisor" AND cbofield2 <> "supervisor" AND cbofield3 <> "supervisor" AND cbofield4 <> "supervisor" AND cbofield5 <> "supervisor" then
    cboSupervisor.visible = false
End If
End Sub

This would need to be repeated on all 5 field AfterUpdate events.

This code was written directly on this forum and has not been tested, it should work but if not you get the idea. Each field has to be checked and if none contain "supervisor" then the field can be hidden.

 

[vbaInet]

I think we need an ELSE criteria there. Maybe this (restructured):

Private Sub cboField5_AfterUpdate()
    If cboField1 <> "supervisor" AND cbofield2 <> "supervisor" AND cbofield3 <> "supervisor" AND cbofield4 <> "supervisor" AND cbofield5 <> "supervisor" then
        cboSupervisor.visible = false
    Else If cboField5 = "supervisor" Then
        cboSupervisor.Visible = True
    Else
        cboSupervisor.Visible = True
    End If
End Sub

I guess CBritghton knows better because he's looked at your db and I haven't.

Also to mention that you will need the code in the Current event of the form and you will also need to wrap the control values in Nz() so it doesn't throw an error when/if Null.

 

[CBrighton]

Not sure why an Else would be needed.

There are three outcomes. If the first If criteria is met it unhides the control, if the second is met it hides the control, if neither are met it leaves the visibility as it is. The two If statements will never both meet their criteria as they are mutually exclusive.

The multiple If's can be replaced by an If&ElseIf, the difference being Access won't take the time to check the second criteria if the first is met. Technically this should save time but as all it's doing is checking 5 control values so it doesn't really matter which you use in this case.


:edit:

Using NZ() may be a good idea, didn't test with null values.

Shouldn't need anything on the Current event though, it's an unbound form with 5 comboboxes displaying field names. On load the supervisor combobox will always be hidden and it will only change when you update the values of the comboboxes.

 

[vbaInet]

There are three outcomes. If the first If criteria is met it unhides the control, if the second is met it hides the control, if neither are met it leaves the visibility as it is. The two If statements will never both meet their criteria as they are mutually exclusive.

I see, so that's the requirement! You're correct then.

 

[gsbatch1]

Thank you both. It works like a Gem, as it was intended.
Ill be back at some point, as they are constantly requesting new stuff.



Thanks