Report with selcted records

  • Thread starter Thread starter Boysie
  • Start date Start date
B

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?
 
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."
 
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
 
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."

Wayne26el said:
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 said:
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."
Click to expand...
Click to expand...
 
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


Al Campagna said:
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."

Wayne26el said:
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
Click to expand...
Click to expand...
 
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


Al Campagna said:
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."

Wayne26el said:
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
Click to expand...
Click to expand...
 
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"
Click to expand...
Wayne26el said:
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


Al Campagna said:
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."

Wayne26el said:
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
Click to expand...
Click to expand...
Click to expand...
 
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"
Click to expand...
Wayne26el said:
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


Al Campagna said:
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."

Wayne26el said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Al Campagna said:
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"
Click to expand...
Wayne26el said:
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


Al Campagna said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Al Campagna said:
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"
Click to expand...
Wayne26el said:
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


Al Campagna said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
 
Back
Top