Request parameters for report

[Andreas]

I want to create an invoice report based on the input criteria you define.
i.e Invoice number. An ordinary report will print all invoices. How can I
make my report asking me which invoice to use and then generate the report
only for that record?

 

[Allen Browne]

A nice interface is to provide a form where you choose the invoice you want,
and then click a button to print that invoice. Details in:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

If you don't want to do that, you can just create a query using your
invoice table. In the Criteria row under the invoice number, enter something
that is not a field name, e.g.:
[What invoice?]
The query will now ask for the invoice number when you run it. use that
query as the Record Source for your report.

 

[Andreas]

This is a good suggestion but when I try it, it will only open the report
with blank fields and #error where there is a calculation
I used the statement:
Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

and replaced my ID and report name as explained. My value is text so I used
the double quotes.

 

[Rick Brandt]

This is a good suggestion but when I try it, it will only open the
report with blank fields and #error where there is a calculation
I used the statement:
Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

and replaced my ID and report name as explained. My value is text so
I used the double quotes.

If it's text you need...

strWhere = "[ID] = '" & Me.[ID] & "'"
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

 

[Andreas]

Exactly the same result. Blank report.
Maybe my report needs to be based on a query and request the field as input?

This is a good suggestion but when I try it, it will only open the
report with blank fields and #error where there is a calculation
I used the statement:
Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

and replaced my ID and report name as explained. My value is text so
I used the double quotes.

If it's text you need...

strWhere = "[ID] = '" & Me.[ID] & "'"
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

 

[Rick Brandt]

Exactly the same result. Blank report.
Maybe my report needs to be based on a query and request the field as
input?

Break it down.

Does...

DoCmd.OpenReport "MyReport", acViewPreview

....open your report with all records shown?

Does...

DoCmd.OpenReport "MyReport", acViewPreview,,"[ID] = 'SomeValue'"

....open your report showing the proper record? If that's not clear I mean does
it work if you hard code a value instead of referring the the control on the
form.

In the immediate window does...

?Forms!YourFormName![ID] <enter>

....return the value you think it should?

 

[Andreas]

I just noticed that even queries will not return any results with this
field. For example if I select criteria: LIKE "INV1*", I don't get results
but with LIKE "*1001" I get normal results. This means that the text part of
the field cannot be regognized. The only difference with this field is that
I use the INPUT MASK: "INV"#### and the field size is 7.

 

[Rick Brandt]

I just noticed that even queries will not return any results with this
field. For example if I select criteria: LIKE "INV1*", I don't get
results but with LIKE "*1001" I get normal results. This means that
the text part of the field cannot be regognized. The only difference
with this field is that I use the INPUT MASK: "INV"#### and the field
size is 7.

You InputMask is not configured to actually store the INV portion. That can be
changed so that it does, but you will need to use an UPDATE query to correct all
of your existing entries.

If it were me I would leave it as is and just modify the filter so that it only
passes the numeric portion.

 

[Andreas]

Does... DoCmd.OpenReport "MyReport", acViewPreview ...open your report with
all records shown?

YES

Does... DoCmd.OpenReport "MyReport", acViewPreview,,"[ID] = 'SomeValue'"
....open your report showing the proper record?

NO, IT RETURNS ALL RECORDS

?Forms!YourFormName![ID]
returns the value of invoice INV1001 as 1001 due to input mask but I also
tried both ways (1001 and INV1001) and the result is always the same. It
will either open a blank report or a normal execution of the report with
all records.

Personally it makes no sense to me since I don't have a lot of experience
with reports.

Exactly the same result. Blank report.
Maybe my report needs to be based on a query and request the field as
input?

Break it down.

Does...

DoCmd.OpenReport "MyReport", acViewPreview

...open your report with all records shown?

Does...

DoCmd.OpenReport "MyReport", acViewPreview,,"[ID] = 'SomeValue'"

...open your report showing the proper record? If that's not clear I mean
does it work if you hard code a value instead of referring the the control
on the form.

In the immediate window does...

?Forms!YourFormName![ID] <enter>

...return the value you think it should?

 

[Rick Brandt]

Does... DoCmd.OpenReport "MyReport", acViewPreview ...open your
report with all records shown?

YES

Does... DoCmd.OpenReport "MyReport", acViewPreview,,"[ID] =
'SomeValue'" ...open your report showing the proper record?

NO, IT RETURNS ALL RECORDS

?Forms!YourFormName![ID]
returns the value of invoice INV1001 as 1001 due to input mask but I
also tried both ways (1001 and INV1001) and the result is always the
same. It will either open a blank report or a normal execution of the
report with all records.

Personally it makes no sense to me since I don't have a lot of
experience with reports.

I thought your earlier post indicated that the filter worked if you used "1001"
instead of "INV1001".

 

[Andreas]

Yes that was in the query. The query will properly filter all 1001 records.
This code though will return all records, not only the 1001. The report is
bound on 2 tables not on queries

Does... DoCmd.OpenReport "MyReport", acViewPreview ...open your
report with all records shown?

YES

Does... DoCmd.OpenReport "MyReport", acViewPreview,,"[ID] =
'SomeValue'" ...open your report showing the proper record?

NO, IT RETURNS ALL RECORDS

?Forms!YourFormName![ID]
returns the value of invoice INV1001 as 1001 due to input mask but I
also tried both ways (1001 and INV1001) and the result is always the
same. It will either open a blank report or a normal execution of the
report with all records.

Personally it makes no sense to me since I don't have a lot of
experience with reports.

I thought your earlier post indicated that the filter worked if you used
"1001" instead of "INV1001".

 

[Rick Brandt]

Yes that was in the query. The query will properly filter all 1001
records. This code though will return all records, not only the 1001.
The report is bound on 2 tables not on queries

If the report's RecordSource has a field with the same name then that code WILL
work if you pass a value that matches a value in that field.

Was the field in the query that worked with a criteria of 1001 the same field
name that you refer to in the OpenReport method?

Is 1001 text or a number?

 

[Andreas]

Yes the same field works with 1001 and returns only the 1001 records.
In the report, if I use 1001 as input, it will return all records, If I use
1002, which is the next record, it will return blank report.
The field type is text.

Here is my code:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[InvoiceNo] = """ & Me.[InvoiceNo] & """"

DoCmd.OpenReport "Customer Invoice", acViewPreview, , strWhere

End If
End Sub

 

[Rick Brandt]

Yes the same field works with 1001 and returns only the 1001 records.
In the report, if I use 1001 as input, it will return all records, If
I use 1002, which is the next record, it will return blank report.
The field type is text.

Here is my code:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[InvoiceNo] = """ & Me.[InvoiceNo] & """"

DoCmd.OpenReport "Customer Invoice", acViewPreview, , strWhere

End If
End Sub

Add this line right before the OpenReport line...

Debug.Print strWhere

....then check the debug window after running the code. Does the output look
like it should?

 

[Andreas]

I have already tried that. The value is: [InvoiceNo] = "1001"
It seems that the report doesn't properly use that as input. Shouldn't I
modify the report in anyway to accept this as parameter?

Yes the same field works with 1001 and returns only the 1001 records.
In the report, if I use 1001 as input, it will return all records, If
I use 1002, which is the next record, it will return blank report.
The field type is text.

Here is my code:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[InvoiceNo] = """ & Me.[InvoiceNo] & """"

DoCmd.OpenReport "Customer Invoice", acViewPreview, , strWhere

End If
End Sub

Add this line right before the OpenReport line...

Debug.Print strWhere

...then check the debug window after running the code. Does the output
look like it should?

 

[Rick Brandt]

I have already tried that. The value is: [InvoiceNo] = "1001"
It seems that the report doesn't properly use that as input.
Shouldn't I modify the report in anyway to accept this as parameter?

No, not if the report has in its RecordSource a field named "InvoiceNo". Are
you quite sure that it does?

Are you executing any code in the Report?

 

[Andreas]

You are right! I was using a query to join the 2 tables in that report and
there the [InvoiceNo] was named something else.
Now it works.

Thank you very much for the help and your patience.

I have already tried that. The value is: [InvoiceNo] = "1001"
It seems that the report doesn't properly use that as input.
Shouldn't I modify the report in anyway to accept this as parameter?

No, not if the report has in its RecordSource a field named "InvoiceNo".
Are you quite sure that it does?

Are you executing any code in the Report?