Form to Report printing question

[jrthor_85 via AccessMonster.com]

I have a Form with a subform (inquiry // parts within that inquiry), which I
am trying to have print out only the current record shown in the form. I
understand that I should only print from reports, and so I've used "Save-as"
to convert my parent form to a report. What I need is to be able to only
print the current record (from both main and sub forms). I've read over and
again that I should use an OpenReport function with something in the WHERE
clause... only problem is I don't know how to type it out or where to put it
into the code.

Note also that I am trying to do the above procedure in 2 different instances.
One form/subform which I want to have do this is only used to enter basic
information for a new entry, and the fields are directed to the tables
themselves. The other form/subform I want to do this with is tied to a
SelectQuery that manipulates the data into many fields not stored in the
tables at all. I dont know if this will make any difference in how the code
is entered, but thought I'd put it out there just in case.

I have scoured these forums for a few weeks now trying to find the answer to
this, and while i'm positive i had found the answer already, I cant seem to
figure out how to implement it.

I thank you in advance for any responses to this, and doubly thank all of you
for your continued effort on this excellent newsgroup/forum.

John

 

[Jeanette Cunningham]

John,
use code like this:

DoCmd.OpenReport "ReportName", , , "[KeyID] = " & Me!KeyID

on the On Click event of a button. The user clicks the button to print the
report.

Replace ReportName with the name of your report.
KeyID is the name of the primary key from the table that the form is based
on.
Replace KeyID with the name of your primary key field.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[jrthor_85 via AccessMonster.com]

John,
use code like this:

DoCmd.OpenReport "ReportName", , , "[KeyID] = " & Me!KeyID

Thankyou for your reply, Jeanette.

I tried the code above with my own values in place as instructed, but had no
success at all in using the code typed into the "On Click" property. I also
attempted (using a blank command button) entering the line directly into VB,
which did open the report as I had hoped, but did not limit the report in any
way; It still tried to print several hundred pages. I tried several
variations of the above in both expression and code, but only seemed to make
headway with macros. I ended up creating a macro with Microsoft's "general"
where clause layout -- [Fieldname]=[Forms]![Formname]![Controlname] which
not only opened the report (yay) but also narrowed the report down to only
whichever Inquiry is currently being viewed in the form (yay x2).

The problem still remaining now is that while the Inquiry of choice is shown,
the report is automatically showing me only the first PartNo record under
that inquiry, no matter which part number is currently being viewed on the
subform. How would I go about making the PartNo as shown on screen print
along with the Inquiry from a Form>Subform setup?

For reference, here is the applicable portions of my layout.
tblContacts --one to many-- tblInquiryList
tblInquiryList --one to many-- tblPartsList
-----------------------------
formNewInquiry (direct link tblInquiryList) with subformNewInquirySubform
(direct link to tblPartsList)

finally have:

NewInquiryRpt made via "Save-As" from the form, that includes
NewInquirySubform in it.

Do I need to create another report of just the subform and put that in place
of the Subform in the parent report? I'm not sure what to do at this point.
And if you're wondering about the absence of a query, this form is solely for
basic data entry, and using a query linking the multiple tables the
information would be saved under prevented me from entering anything at all.
I have another form/subform which does use a query combining above tables
that is used to view/test variable "trial" values. Based on the lack of a
reply regarding my question of differences in coding between the forms
(sourced from query rather than tables), I can safely assume there should be
no difference in the code structure?

 

[jrthor_85 via AccessMonster.com]

Another note: While researching this further and having come across Allen
Browne's website, I noticed that there is a difference in quotations if the
key field is a text type rather than a number.
as in:
strWhere = "[ID] = """ & Me.[ID] & """"

I did make the appropriate changes to the code you posted in your reply, but
still received same result: The Inquiry is shown properly, but the subform
(partno) is locked to the first entry under the given inquiry

 

[jrthor_85 via AccessMonster.com]

Another note: While researching this further and having come across Allen
Browne's website, I noticed that there is a difference in quotations if the
key field is a text type rather than a number.
as in:
strWhere = "[ID] = """ & Me.[ID] & """"

I did make the appropriate changes to the code you posted in your reply, but
still received same result: The Inquiry is shown properly, but the subform
(partno) is locked to the first entry under the given inquiry

 

[Jeanette Cunningham]

Hi, it is morning here and I am back on the discussion group.
This report you need is a bit more complicated than just a save as of the
form.
Create the report using the report wizard and get the report working by
itself.
Once you have the report, you can use the strWhere = "[ID] = """ & Me.[ID] &
""""
to open it.
Use either a single quote or a triple quote depending on whether the ID is a
number or text data type.


Jeanette Cunningham -- Melbourne Victoria Australia