Have report show current record on form

  • Thread starter Thread starter David K.
  • Start date Start date
D

David K.

Hi. I'm a newbie e-tard when it comes to Access so bare with me if this is
easy to figure out. I checked the forum for my problem but didn't find
anything that seemed to match my problem. Sorry for the long length.

I have a form that has a subform. It is used to enter instances of
nonconformance for rejected parts made in our manufacturing facility. The
main form is used to enter the basic data such as Customer, Part Number, PO
number, Qty, etc. The subform is used to enter the specific defects, i.e,
Defect 1, Cosmetic, 5 pieces were scratched...Defect 2, Dimensional, 1 piece
is .002" below tolerance on the 11.246" dimension...Defect 3, etc.

I created a query that pulls all the info from the NCR main & sub tables and
used that query to create a report. On the JOIN PROPERTIES options for the
two tables I have #2 selected (Include ALL records from main table and ONLY
the records from the subtable where the joined fields are EQUAL). The tables
are connected by the NCR number which is created in the main form and then
shown on each defect detail in the subform (and cooresponding tables of
course).

The first time I viewed the report it worked great! Everything looked like
I wanted it to. It showed the results for the first entry.

BTW - My intention is to create a report that shows only one record at a
time. I will use this to print individual reports that will be distributed
within our company or sent to our customers.

But, when I went to the second entry in my form and clicked on the button I
created that opens the query and then the report (using Macros) the report
still only shows the info for the first record.

FYI, the macro for my button looks like this:
(On Click)
OpenQuery
Requery
OpenReport
Close

I'm pretty sure that isn't correct. Hope it doesn't make you fall out of
your chair from laughter.

How can I set it up to be able to create a report based on the current
record in my form that I am viewing? What do you think is wrong with my
query?

Thanks in advance for any help. I'm not sure what info you need so please
let me know what else I can supply that would give you a better picture of
what is going on.
 
David,

I would use VBA Code instead of Macros for this. I can help you for now on
the Code to Open your Report from the Form you are viewing your Records. As
far as getting the Report to show you a single record and as far as helping
you set up your Query properly, more information is needed (tables and
fields, but mainly the Primary Key or ID Field) Your Report should work fine
with this though.

Create a Button on the Subform (if the wizard starts, cancel it). On the
Properties Sheet for the Button, use the On Click Event (click the "..." to
the right of it and select "Code Builder". Your Code should look like
this(but with your Report Name and PrimaryID or Key Field):

Private Sub ButtonName_Click()
On Error GoTo Err_Code

Dim stLinkCriteria As String
stLinkCriteria = "[PrimaryID] = " & Me![PrimaryID]

DoCmd.OpenReport "ReportName", acViewPreview, , stLinkCriteria

Exit Sub

Err_Code:
MsgBox Err.DESCRIPTION

End Sub


-Scott Channell
 
Thanks, Scott.

I followed your instructions and the report opened after a popup asked me to
enter a parameter value for my NCRepNum. Would like to get rid of the prompt
to input data but I am awaiting further instruction from you. What
information would you like me to give you?

Channell said:
David,

I would use VBA Code instead of Macros for this. I can help you for now on
the Code to Open your Report from the Form you are viewing your Records. As
far as getting the Report to show you a single record and as far as helping
you set up your Query properly, more information is needed (tables and
fields, but mainly the Primary Key or ID Field) Your Report should work fine
with this though.

Create a Button on the Subform (if the wizard starts, cancel it). On the
Properties Sheet for the Button, use the On Click Event (click the "..." to
the right of it and select "Code Builder". Your Code should look like
this(but with your Report Name and PrimaryID or Key Field):

Private Sub ButtonName_Click()
On Error GoTo Err_Code

Dim stLinkCriteria As String
stLinkCriteria = "[PrimaryID] = " & Me![PrimaryID]

DoCmd.OpenReport "ReportName", acViewPreview, , stLinkCriteria

Exit Sub

Err_Code:
MsgBox Err.DESCRIPTION

End Sub


-Scott Channell

David K. said:
Hi. I'm a newbie e-tard when it comes to Access so bare with me if this is
easy to figure out. I checked the forum for my problem but didn't find
anything that seemed to match my problem. Sorry for the long length.

I have a form that has a subform. It is used to enter instances of
nonconformance for rejected parts made in our manufacturing facility. The
main form is used to enter the basic data such as Customer, Part Number, PO
number, Qty, etc. The subform is used to enter the specific defects, i.e,
Defect 1, Cosmetic, 5 pieces were scratched...Defect 2, Dimensional, 1 piece
is .002" below tolerance on the 11.246" dimension...Defect 3, etc.

I created a query that pulls all the info from the NCR main & sub tables and
used that query to create a report. On the JOIN PROPERTIES options for the
two tables I have #2 selected (Include ALL records from main table and ONLY
the records from the subtable where the joined fields are EQUAL). The tables
are connected by the NCR number which is created in the main form and then
shown on each defect detail in the subform (and cooresponding tables of
course).

The first time I viewed the report it worked great! Everything looked like
I wanted it to. It showed the results for the first entry.

BTW - My intention is to create a report that shows only one record at a
time. I will use this to print individual reports that will be distributed
within our company or sent to our customers.

But, when I went to the second entry in my form and clicked on the button I
created that opens the query and then the report (using Macros) the report
still only shows the info for the first record.

FYI, the macro for my button looks like this:
(On Click)
OpenQuery
Requery
OpenReport
Close

I'm pretty sure that isn't correct. Hope it doesn't make you fall out of
your chair from laughter.

How can I set it up to be able to create a report based on the current
record in my form that I am viewing? What do you think is wrong with my
query?

Thanks in advance for any help. I'm not sure what info you need so please
let me know what else I can supply that would give you a better picture of
what is going on.
Click to expand...
Click to expand...
 
Back
Top