Report with selcted records

[Boysie]

hi

I am creating an exercise database - simply design - just one table with the
exercise name, muscles used info, teaching points, and a picture. I want to
be able to create a report which prints of the selcted exercises the
instructor has chosen. However, at the moment the report shows all of the
exercises in the database. I know this is probably really simple to do but I
have no idea where to go?! Can anyone help please?

 

[Al Campagna]

Boysie,
Several ways to do that...

Each of your exercise records should have a key value, that is unique to
each record.
Something like an autonumber field named ExerciseID.
Let's say you're on your form (ex. frmExercises) at record with an
ExerciseID = 123.
And, there is a button on the form (ex. name cmdRunReport) that opens
the report.
Use this record's ExerciseID value to filter the query behind your
report.

Field: ExerciseID
Criteria: =Forms!frmExercises!ExerciseID

Now the report will only return the exercise associated with ExerciseID
= 123.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Wayne26el]

Al,
I have a similar report but I am using a DrawingNo as my key field to
get my report. But when I put in =[Forms]![frmQuote]![DrawingNo] in the
filter name in my embedded macro. I get an error which says “The Microsoft
Office Access database engine could not find the object†and the list the
drawing no. that I’m looking for the report on.

Wayne

 

[Al Campagna]

Wayne,
Well, the form must remain open while the report is run. Other
than that, anything I would offer would be a guess.
I have never used Macros, but in VB, another way to filter the report
is to use the Where argument in the OpenReport method.
(from a button on the form - all code on one line)...

DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo = " &
Me.DrawingNo
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Al,
I have a similar report but I am using a DrawingNo as my key field to
get my report. But when I put in =[Forms]![frmQuote]![DrawingNo] in the
filter name in my embedded macro. I get an error which says "The Microsoft
Office Access database engine could not find the object" and the list the
drawing no. that I'm looking for the report on.

Wayne

Boysie,
Several ways to do that...

Each of your exercise records should have a key value, that is unique
to
each record.
Something like an autonumber field named ExerciseID.
Let's say you're on your form (ex. frmExercises) at record with an
ExerciseID = 123.
And, there is a button on the form (ex. name cmdRunReport) that opens
the report.
Use this record's ExerciseID value to filter the query behind your
report.

Field: ExerciseID
Criteria: =Forms!frmExercises!ExerciseID

Now the report will only return the exercise associated with
ExerciseID
= 123.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

 

[Wayne26el]

I guess I’m so new at this that I don’t understand or maybe I didn’t make
myself clear I’m not sure where to put the statement “DoCmd.OpenReport
"ReportName", acViewPreview, , "DrawingNo = " & Me.DrawingNo†(without the
quotes). I created a button to preview the report on the form in which I
found the drawing no which automatically gives me an embedded macro. I’m not
sure how to or where to put the statement above, which gives me a syntax
error. I think I starting in the wrong direction.

Wayne

Wayne,
Well, the form must remain open while the report is run. Other
than that, anything I would offer would be a guess.
I have never used Macros, but in VB, another way to filter the report
is to use the Where argument in the OpenReport method.
(from a button on the form - all code on one line)...

DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo = " &
Me.DrawingNo
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Al,
I have a similar report but I am using a DrawingNo as my key field to
get my report. But when I put in =[Forms]![frmQuote]![DrawingNo] in the
filter name in my embedded macro. I get an error which says "The Microsoft
Office Access database engine could not find the object" and the list the
drawing no. that I'm looking for the report on.

Wayne

 

[Al Campagna]

Wayne,
I'll use the name cmdRunReport as you form's button name...
(you use your own)
With your form in Design mode...
Select the cmdRunReport button.
In the Properties dialog box, locate the button's OnClick event.
Put your cursor in the text box next to the OnClick.
Using the little arrow on the right of the text box, and select
[EventProcedure]
Now click the little box on the right, with 3 dots (...)
You are now in the form's VB Module, and you should see...

Private Sub cmdRunReport_Click()
| <= Cursor is here..
End Sub

Now, add my code between the lines...

Private Sub cmdRunReport_Click()
DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo = " &
Me.DrawingNo"
End Sub

Close out the module, save the form, and then run the form.
When you click the cmdRunReport, the report will run, filtered by the
DrawingNo currently on the form.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"ReportName", acViewPreview, , "DrawingNo = " & Me.DrawingNo"

I guess I'm so new at this that I don't understand or maybe I didn't make
myself clear I'm not sure where to put the statement "DoCmd.OpenReport
"ReportName", acViewPreview, , "DrawingNo = " & Me.DrawingNo" (without
the
quotes). I created a button to preview the report on the form in which I
found the drawing no which automatically gives me an embedded macro. I'm
not
sure how to or where to put the statement above, which gives me a syntax
error. I think I starting in the wrong direction.

Wayne

Wayne,
Well, the form must remain open while the report is run. Other
than that, anything I would offer would be a guess.
I have never used Macros, but in VB, another way to filter the report
is to use the Where argument in the OpenReport method.
(from a button on the form - all code on one line)...

DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo = " &
Me.DrawingNo
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Al,
I have a similar report but I am using a DrawingNo as my key field
to
get my report. But when I put in =[Forms]![frmQuote]![DrawingNo] in the
filter name in my embedded macro. I get an error which says "The
Microsoft
Office Access database engine could not find the object" and the list
the
drawing no. that I'm looking for the report on.

Wayne

 

[Wayne26el]

Al,
I didn’t know that there was an “EventProcedure†over there. That helped but
I now have another problem. Most all of my drawing no. have a comma in them
(B31-17, 2304) and I get a runtime error 3075. If my drawing no. does not
have a comma I get a box asking for a parameter.

Wayne

Wayne,
I'll use the name cmdRunReport as you form's button name...
(you use your own)
With your form in Design mode...
Select the cmdRunReport button.
In the Properties dialog box, locate the button's OnClick event.
Put your cursor in the text box next to the OnClick.
Using the little arrow on the right of the text box, and select
[EventProcedure]
Now click the little box on the right, with 3 dots (...)
You are now in the form's VB Module, and you should see...

Private Sub cmdRunReport_Click()
| <= Cursor is here..
End Sub

Now, add my code between the lines...

Private Sub cmdRunReport_Click()
DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo = " &
Me.DrawingNo"
End Sub

Close out the module, save the form, and then run the form.
When you click the cmdRunReport, the report will run, filtered by the
DrawingNo currently on the form.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"ReportName", acViewPreview, , "DrawingNo = " & Me.DrawingNo"

I guess I'm so new at this that I don't understand or maybe I didn't make
myself clear I'm not sure where to put the statement "DoCmd.OpenReport
"ReportName", acViewPreview, , "DrawingNo = " & Me.DrawingNo" (without
the
quotes). I created a button to preview the report on the form in which I
found the drawing no which automatically gives me an embedded macro. I'm
not
sure how to or where to put the statement above, which gives me a syntax
error. I think I starting in the wrong direction.

Wayne

Wayne,
Well, the form must remain open while the report is run. Other
than that, anything I would offer would be a guess.
I have never used Macros, but in VB, another way to filter the report
is to use the Where argument in the OpenReport method.
(from a button on the form - all code on one line)...

DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo = " &
Me.DrawingNo
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Al,
I have a similar report but I am using a DrawingNo as my key field
to
get my report. But when I put in =[Forms]![frmQuote]![DrawingNo] in the
filter name in my embedded macro. I get an error which says "The
Microsoft
Office Access database engine could not find the object" and the list
the
drawing no. that I'm looking for the report on.

Wayne

 

[John Spencer MVP]

Since your drawing no field is a text field, you need to modify the code slightly

The following should all be on one line
DoCmd.OpenReport "ReportName", acViewPreview, , "DrawingNo=""" & Me.DrawingNo
& """"

Naturally, you need to substitute your report name and the name of your
drawing number field in the above.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Wayne26el]

Thanks that worked. I'm still learning the in and outs to Access.

Wayne

 

[Armen Stein]

Thanks that worked. I'm still learning the in and outs to Access.

So are we all.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com