Filtering reports using forms

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

domibud - 25 Apr 2008 03:00 GMT

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?

Reply to this Message

Duane Hookom - 25 Apr 2008 03:35 GMT

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

Signature

Duane Hookom
Microsoft Access MVP

> Hi... I'm trying to make a database for employees transport/taxi expenses.
> This is the first time I'm making a database and I'm new to VBA language.
[quoted text clipped - 49 lines]
>
> Can anyone help me to the right direction?

Reply to this Message

domibud - 28 Apr 2008 03:22 GMT

Thanks for your help.
These last few days, I tried your advice, but I can't get it to work.
I can only open the report, without any record on it.
In the filter form, the user will input their name and date range. From your
code, I implied that it will filter the date range only.

I tried using query for the report, and it work just fine. But the user will
have to enter their criteria with 3 different step.
If I use form, they just have to do it in one step.

Any other advice on how to filter the user name and the date range for the
record?

> You can't change a property of the report that isn't open.
>
[quoted text clipped - 64 lines]
> >
> > Can anyone help me to the right direction?

Reply to this Message

Duane Hookom - 28 Apr 2008 05:05 GMT

Have you created the form with the controls for entering the dates and name?
What are the names of the controls and what are the names and data types of
the corresponding fields?

Did you remove the criteria from the report's record source?

Signature

Duane Hookom
Microsoft Access MVP

> Thanks for your help.
> These last few days, I tried your advice, but I can't get it to work.
[quoted text clipped - 77 lines]
> > >
> > > Can anyone help me to the right direction?

Reply to this Message

domibud - 28 Apr 2008 07:51 GMT

The form controls are:
1. StartDate
2. EndDate
3. EmployeeName

and the corresponding fields are:
1. Name with data types text
2. DateUse with data type date

Ya, I did remove the criteria from the record source.

For problems like this, which is more appropriate, Query or Table as the
report's record source?
Since at the beginning I design the report using query as the recordsource,
and the form as the user interface to pass on the criteria to the query.

Thanks for your time.

Regards,

domibud

> Have you created the form with the controls for entering the dates and name?
> What are the names of the controls and what are the names and data types of
[quoted text clipped - 83 lines]
> > > >
> > > > Can anyone help me to the right direction?

Reply to this Message

Duane Hookom - 28 Apr 2008 14:23 GMT

I would expect the code would look something like:

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
strWhere = "1=1 "
If Not IsNull(Me.StartDate) Then
strWhere = strWhere & " AND [DateUse] >=#" & _
Me.StartDate & "# "
End If
If Not IsNull(Me.EndDate) Then
strWhere = strWhere & " AND [DateUse] <=#" & _
Me.EndDate & "# "
End If
If Not IsNull(Me.EmployeeName) Then
strWhere = strWhere & " AND [Name] =""" & _
Me.EmployeeName & """ "
End If
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

BTW: Name is a reserved word and should be avoided as the name of a field.

Signature

Duane Hookom
Microsoft Access MVP

> The form controls are:
> 1. StartDate
[quoted text clipped - 105 lines]
> > > > >
> > > > > Can anyone help me to the right direction?

Reply to this Message

domibud - 29 Apr 2008 04:53 GMT

Thanks a lot for your help.
Now the filter process works.

> I would expect the code would look something like:
>
[quoted text clipped - 127 lines]
> > > > > >
> > > > > > Can anyone help me to the right direction?

Reply to this Message