[Damien89]

Hi All,

I'm having difficult getting a VBA report filter to work.
The code is based data within a form.

I can get the data filter to work properly but I can't get the combo box part to work. I just get "Error 438"
I could really use some help.

Here's the code....


Private Sub Preview_Click()
On Error GoTo Err_Handler

Dim strReport As String
Dim strDateField As String
Dim strFilterLevel As String
Dim strWhere As String
Dim varWhere As Variant
Dim lngView As Long
Const strcJetDate = "\#dd\/mm\/yyyy\#"

strReport = "Members Detailed"
strDateField = "[Member Entered]"
strFilterLevel = "Account Level"
lngView = acViewPreview

If Me.cboFilterLevel > 0 Then
cboFilterLevel = varWhere & "Account Level = " & Me.cboFilterLevel & "AND"
End If

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If

Resume Exit_Handler
End Sub

[OBP]

Damien89, welcome to the Forum.
What is the Error Description?
It would appear that the combo syntax is not correct as you have
strWhere = strWhere
for the other filters but
cboFilterLevel = varWhere
which would be trying to reset the combo if the combo is cboFilterLevel

[Damien89]

The error description is:
Error 438:Object doesn't support this property or method.

The from fields are:

Date from text box name = "txtStartDate"
Date to text box name = "txtEndDate"
Combo box name = "cboFilterLevel

The report fields are:
Account ID
Member Name
Account Level
Member Entered

How should I amend the syntax for the combo box?
Thanks in advance - Damien.

[OBP]

I would have thought it should be
strWhere = strWhere & "Account Level = " & Me.cboFilterLevel & "AND"
or
strWhere = strWhere & "Account Level = '" & Me.cboFilterLevel & "'" & "AND"

[Damien89]

I tried those changes but no luck.

I've uploaded the database to yousendit which might make things a bit easier to diagnose.
Here is the link.

https://www.yousendit.com/download/M...Z2pwaFRWUThUQw

[Rockn]

I have no idea what the VB is supposed to do except build a where clause for opening the report.It seems like a lot of rigamarole for setting the criteria for a date range and a level. cboFilterLevel is also not the name of your combo box, it is Combo18. You also need to declare the combo value as an integer, not a string. Some of the code looks like validation that ends up doing nothing. It also looks like you have a filter hard coded into the report. You will also never get the account level as a filter as there is no LevelID in the accounts table.

[Damien89]

You're right, I've probably overcomplicated it. I'm a complete novice when it comes to visual basic and am trying to figure it out as I go.

I'm trying to use a form to set a filter for a report. I need to filter the report within a date range and filter by Level ID as well.

I have amended the Accounts table to have a 'Level ID' field and have corrected the combo box name.

I can now generate the report and the date range filter applies, but the Level ID filter does not.
Where am I going wrong? I really appreciate your help!

Private Sub Preview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strLevelField As Integer
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#dd\/mm\/yyyy\#"

strReport = "qryMemberAcqDate"
strDateField = "[Member Entered]"
strLevel = "[Level ID]"
lngView = acViewPreview

If IsNumeric(Me.cboFilterLevel) Then
strLevelField = strLevel & "Level ID = " & (Me.cboFilterLevel) & """AND"
End If

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If

Resume Exit_Handler
End Sub

[Rockn]

Try this
Dim strReport As String
Dim strStartDate As String
Dim strEndDate As String
Dim strFilterLevel As String
Dim strWhere As String
Dim lngView As Long
'Const strcJetDate = "\#dd\/mm\/yyyy\#" 'Do NOT change it to match your local settings.

strReport = "Members Detailed"
strStartDate = Me.txtStartDate
strEndDate = Me.txtEndDate
strFilterLevel = Me.Combo18
lngView = acViewPreview

If strFilterLevel > 0 Then
strFilterLevel = "LevelID = " & Me.Combo18
End If
If IsDate(Me.txtStartDate) Then
strWhere = "[Member Entered] >= #" & Me.txtStartDate & "#"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[Member Entered] < #" & Me.txtEndDate & "# AND " & strFilterLevel
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

[Damien89]

No Luck.

I get the following error:

Error 3075: Syntax error (missing operator) in query expression '[Member Entered]>=#1/02/2012# AND [Member Entered]<#18/04/2012# AND Level ID =1'.

[Rockn]

You will need to add a LevelID field in the accounts table so it can be linked to the tblMemberLevel.(manually add the LevelID numbers that correspond to the descriptions) You will also need to modify the query used to create the report to include the LevelID field and also link to the tblMemberLevel table to bring in the Account Level name.