adding parameter or code to select current record

Hi All

I have a report that has been created from a form. Once we have finished inputting any data on this form and i would then like to be able to run the report for that specific record only..

eg. If we have inputed all the details for autonumber 4 can i then tell the report to only run the details for no 4 instead of it running record 1, 2 & 3 as well.
Is it possible to do this.

thanks as always

Carolm

Sure!

You will need to add a Where Condition to the OpenReport method so that the
report shows only the information for the record displayed on your form. For
example, if your field "ProblemID" uniquely identifies the record, you
could use it to determine the where condition as follows:

Dim strCriteria As String

' This works when your ProblemID field is text
strCriteria = "[ProblemID] = " & Chr(34) & Me!ProblemID & Chr(34)
DoCmd.OpenReport "MyReport", acViewNormal, , strCriteria

' This works when your ProblemID field is a number
strCriteria = "[ProblemID] = " & Me!ProblemID
DoCmd.OpenReport "MyReport", acViewNormal, , strCriteria


Of course, change the "ProblemID" to whatever the name of your field is.

hth,



--

Cheryl Fischer, MVP Microsoft Access



"carolm" wrote in message
...
Hi All

I have a report that has been created from a form. Once we have finished
inputting any data on this form and i would then like to be able to run the
report for that specific record only..

eg. If we have inputed all the details for autonumber 4 can i then tell
the report to only run the details for no 4 instead of it running record 1,
2 & 3 as well.
Is it possible to do this.

thanks as always

Carolm

Hi Cheryl

I think im doing something wrong....again!!!
Here's what ive done..
I have a macro and i have clicked on the OpenReport. At the bottom of the page there is a Where condition box...go into the ... below is exactly what ive written

Dim strCriteria As String
strCriteria = "[No] = " & Me!No
DoCmd.OpenReport "rptCHQREQ", acViewNormal, , strCriteria

It is saying microsoft access cannot parse the expression.

I apologise in advance if i have done something really dumb...im just learning and finding it all a bit confusing!!!

Thanks

Carol,

In your initial post, I did not see any mention of a macro. The code I
provided should be used in the On Click event of a command button. In the
event that you have not done much VBA coding of events, here are the steps:

1. Right click on the desired Command Button (let's call it:
CommandRunReport) control and select Properties.

2. When the Properties sheet opens, click the tab labeled Event.

3. In the grid below, locate the row labeled, On Click. It should be blank.
Click anywhere in the white space to the right of the label and you will see
a downward-pointing arrow appear, indicating that this is also a ComboBox.
Click the arrow and select "Event Procedure".

4. Then, notice that there is an ellipsis or three little dots (...) to the
right of the ComboBox. Click the ellipsis and you will open a code window.
You will see that Access has given you a space for entering some code in
this event - it will look something like the following:

Private Sub Private Sub CommandRunReport_Click()

End Sub

5. After the "Private Sub Private Sub CommandRunReport_Click()" line,
insert the code that I provided in my earlier response.

6. Click the Save icon and close the Microsoft Visual Basic code window.


--

Cheryl Fischer, MVP Microsoft Access



"carolm" wrote in message
...
Hi Cheryl

I think im doing something wrong....again!!!
Here's what ive done..
I have a macro and i have clicked on the OpenReport. At the bottom of the
page there is a Where condition box...go into the ... below is exactly
what ive written

Dim strCriteria As String
strCriteria = "[No] = " & Me!No
DoCmd.OpenReport "rptCHQREQ", acViewNormal, , strCriteria

It is saying microsoft access cannot parse the expression.

I apologise in advance if i have done something really dumb...im just
learning and finding it all a bit confusing!!!

Thanks

Hi Cheryl

Thank you for taking the time to explain it step by step for me. I am new to all this and find it hard to take in..

I have added the command button and it works brilliantly. I was wondering though whether it is also possible to run the report and mail it to a recipient rather than print it.

I have worked out how to mail it using an the "onchange" and writing a macro that sends it when you change any info in the field but it includes all the records and not a record of my choice.

Thank you for all your help it is certainly appreciated.

CarolM

Carol,

Trying to do this from your form that has multiple records in its
RecordSource will not get the same results as our OpenReport statement,
because the OpenReport statement applies a temporary filter to the Report,
not the Form

With the code we have in place now, the easiest (although not automated)
approach would be to do this from the Report:

- In design view of your form, right click on the command button that
prints the report, then select Build Event. This will open the code window
for the OpenReport code I sent you. Change the statement: acViewNormal
to acViewPreview

- Save the changes.

- Open your form and select the report. You will see it open in Preview
Mode. At that point, you can choose whether to print it or, if you want to
email it, you can select File|Send To


Let us know if this works for you.
--

Cheryl Fischer, MVP Microsoft Access



"carolm" wrote in message
...
Hi Cheryl

Thank you for taking the time to explain it step by step for me. I am new
to all this and find it hard to take in..

I have added the command button and it works brilliantly. I was wondering
though whether it is also possible to run the report and mail it to a
recipient rather than print it.

I have worked out how to mail it using an the "onchange" and writing a
macro that sends it when you change any info in the field but it includes
all the records and not a record of my choice.

Thank you for all your help it is certainly appreciated.

CarolM

thanks cheryl, works perfectly

thanks you for your time and patience.

carol

You're welcome and good luck with your project!

--

Cheryl Fischer, MVP Microsoft Access



"carolm" wrote in message
...
thanks cheryl, works perfectly

thanks you for your time and patience.

carol