Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007 VBA report filter


(!)

Damien89's Avatar
Damien89 Damien89 is offline
Computer Specs
Member with 5 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
16-Apr-2012, 03:01 AM #1
Access 2007 VBA report filter
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
16-Apr-2012, 05:31 AM #2
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's Avatar
Damien89 Damien89 is offline
Computer Specs
Member with 5 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
16-Apr-2012, 06:55 PM #3
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
17-Apr-2012, 06:41 AM #4
I would have thought it should be
strWhere = strWhere & "Account Level = " & Me.cboFilterLevel & "AND"
or
strWhere = strWhere & "Account Level = '" & Me.cboFilterLevel & "'" & "AND"
Damien89's Avatar
Damien89 Damien89 is offline
Computer Specs
Member with 5 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
17-Apr-2012, 07:32 PM #5
No Luck
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's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
17-Apr-2012, 11:43 PM #6
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.
__________________
My no line signature
a
a
Damien89's Avatar
Damien89 Damien89 is offline
Computer Specs
Member with 5 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
18-Apr-2012, 12:39 AM #7
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's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
18-Apr-2012, 12:40 AM #8
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's Avatar
Damien89 Damien89 is offline
Computer Specs
Member with 5 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
18-Apr-2012, 01:50 AM #9
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's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
18-Apr-2012, 08:22 AM #10
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.

Last edited by Rockn; 18-Apr-2012 at 08:59 AM..
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
access, error 438, reprot filter

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑