Filter and Open Report in Runtime

[ggs]

I have been trying all weekend and now I'm grey.
I have tried to adapt the Allan Browne VBA for filtering in a form "http://allenbrowne.com/ser-62.html" but I want to open a (filtered) report
with the user selecting from a combo box and also a start date and a finish date. I know as much VBA as I do Klingon
so progress is slow but not as dangerous. the line to open the form might be working but not filtering because
somethings missing (apart from my knowledge) Any kind person that could take a look and steer me in the right direction.
The DB has just one form (with subform) plus tabbed form that is for command buttons. The tabbed form Report Options has some notes


and the VBA that I've played with is still there. Thanks in advance Gareth

[June7]

I found a few problems.

The Sub cmdFilter_Click has an error in the DoCmd.OpenReport line. strWhere is misspelled. If you add this line in the header of every code module, errors in variable spellings will be apparent when you compile the code: Option Explicit
It will also require every variable to be declared so you might not want to implement it in this module. It can be very helpful when writing new procedures.

The Sub cmdFilter_Click() has an incomplete strWhere, missing the EndDate of the date range critieria and the date field name is wrong and is not including the depot criteria (the code is set up for a checkbox, not combobox). Try this revised procedure:

Private Sub cmdFilter_Click()
    Dim strWhere
    If Not IsNull(Me.txtDepot) Then
        strWhere = strWhere & "[Depot]='" & Me.txtDepot & "'"
    End If
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & IIf(strWhere = "", "", " AND ") & "[date_inc] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
    End If
    DoCmd.OpenReport "Incident Listing Report", acViewPreview, , strWhere
End Sub

The conJetDate was bugging on me. The procedure to clear the controls also bugged so I went with this:

Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Detail, and show all records again.
    Me.txtDepot = Null
    Me.txtStartDate = Null
    Me.txtEndDate = Null
    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Hope this helps you progress.

[ggs]

Hi June 7 (again)
The tabbed form has 4 buttons and one text control and two date controls
The 1st button opens the current form details to a report OK
The 2nd opens all details to another report OK
The 3rd
Ive looked it over fixed the spelling and done a couple of changes
Private Sub cmdFilter_Click()

If Not IsNull(Me.txtDepot) Then
strWhere = strWhere & "([Depot] = True) AND "
ElseIf Me.txtDepot = 0 Then
strWhere = strWhere & "([Depot] = False) AND "
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([date_inc] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
strWhere = strWhere & "([date_inc] >= " & Format(Me.txtEndDate, conJetDate) & ") "
End If
DoCmd.OpenReport "Incident Listing Report", acViewPreview, , strWhere
End Sub
This opens the report but it is not filtered so I can only guess there is something missing just before the last line.

The fourth (the clear filter button)
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Detail, and show all records again.
Dim ctl As Control

'Clear all the controls in the Detail section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null shows runtime error 3314

End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub
Also there is nothing in there to reset the date ctrls but the AllenBrowne eg does not appear to have anything
Thanks again

[ggs]

The Last Post (sounds like it is terminal!)

sorry but something funny happened there. I read your reply which finished at the second paragraph so I mucked around for a half hour and made my first "reply"
when I posted it, your complete answer appeared before my eyes so I have quickly pasted it in and as they say "VOILA"

So now I'm going to spend poring over your code revisions and try and learn something
People who spend their valuable time helping others are gems
Thankyou again