Query will only show data for first record

[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 K.]

Thanks, MGRoster. I'm still having difficulties. Just wanted to let you
know that I have the NCR in both the main and subforms. I set up the subform
to automatically enter the NCR number (pulls it from the current NCR number
in the main form) whenever a new detail is entered in the subform. I'm not
sure how this affects everything.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the report you can enter a reference to the open form in the report's
Filter property. The NCR number must be a part of the query's results
for this to work (i.e., it must be one of the columns (fields) that will
be selected by the query).

Filter: [NCR] = Forms!FormName!NCRControlName
Filter On: Yes

Substitute your form name and control name in the above.

When the report opens it will "look" at the form you have open and pluck
the NCR number from the control that holds it. Then it will filter the
query to only print data for that NCR number. This assumes that the NCR
number is on the main form, not the sub-form.

If the NCR number is on the sub-form you'd reference it like this:

Filter: [NCR] = Forms!FormName!subFormName!NCR_ControlName

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScLKmYechKqOuFEgEQI+nwCeLqTMd5olZ5RgWmlKoe/GCGzIIfYAoNFW
2+FCzaCEyZmiy4wCFKulQ2mz
=kl2W
-----END PGP SIGNATURE-----

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.