Generate report from a form containing a subform?

[Scott]

Hi,
I'm having a really hard time with what should be a really
simple thing to accomplish.
I have a MAIN form that contains a subform named VENDORS.

The MAIN form contains part info like PartNumber,
Location,PartDescription,etc.
The sub form (VENDORS) contains vendor info like company
name, address etc.

There are two tables for these two forms.
They are related. And the forms work perfectly and
everything looks great.
But I can't for the life of me generate a report from the
information my forms display.

I need to create a purchase order from the information.
And that means that I need some info from one form. And
some info from the subform form.
This also means that I don't want the report to show me
every record. Only the record that's currently active.

I can successfully generate the report info from the MAIN
form by using direct refrences to the fields like: =
[Forms]![MAIN]![PartNumber],etc,etc,etc.
But when I try to do the same thing with the fields on the
subform. It won't work.

How do you get both form, and subform, fields to show up
correctly on a single report? And just the active record
for each?


I'd be glad to send the .mdb file to anyone who needs it
to solve the problem. It's a very small file.

Thanks for any advise,
-Scott

 

[Duane Hookom]

Use subreports like you would use subforms. Make sure you set the link
master child properties like you would on your forms.

 

[Marshall Barton]

I have a MAIN form that contains a subform named VENDORS.

The MAIN form contains part info like PartNumber,
Location,PartDescription,etc.
The sub form (VENDORS) contains vendor info like company
name, address etc.

There are two tables for these two forms.
They are related. And the forms work perfectly and
everything looks great.
But I can't for the life of me generate a report from the
information my forms display.

I need to create a purchase order from the information.
And that means that I need some info from one form. And
some info from the subform form.
This also means that I don't want the report to show me
every record. Only the record that's currently active.

I can successfully generate the report info from the MAIN
form by using direct refrences to the fields like:
=[Forms]![MAIN]![PartNumber],etc,etc,etc.
But when I try to do the same thing with the fields on the
subform. It won't work.

How do you get both form, and subform, fields to show up
correctly on a single report? And just the active record
for each?

In general, a report would not refer back to the form for
its data. If pactical, the report should obtain its data
from its record source query.

In this case I think(?) you want a query that includes all
the records from the parts table and the matching records
from the vendor table (with the connecting line between the
vendorID fields). Without getting the report involved, see
if you can get that query put together so that it displays
every part with its associated vendor name.

Once the query has the needed data, then modify the report
to use the data from the query instead of the form. You can
use the form's print report button to initiate printing the
report and filter the data to the part currently displayed
on the form by modifying the button's Click event so it
looks like:

Dim stDoc As String
Dim stWhere As String
stDoc = "nameof report"
stWhere = "partnumfield = " & Me.txtpartnumtextbox
DoCmd.OpenReport stDoc, acViewPreview, , stWhere

If I totally missed the problem and none of that applies to
what you're doing, then you can refer to the subform's data
using this kind of syntax:

=Forms!MAIN!vendorsubform.Form.txtvendornametextbox

 

[Guest]

Marshall,

I liked your approach and I created a query.
But I'm still having trouble just getting the report to
display the current record and not all of them.

I don't understand what I'm supposed to put in this line
of code:
stWhere = "partnumfield = " & Me.txtpartnumtextbox


I'm totally confused on that line. And How to implement it
so my report only shows the currrent record.


-Scott

 

[Marshall Barton]

Marshall,

I liked your approach and I created a query.
But I'm still having trouble just getting the report to
display the current record and not all of them.

I don't understand what I'm supposed to put in this line
of code:
stWhere = "partnumfield = " & Me.txtpartnumtextbox

That along with something like the other lines should be in
the Click event procedure for the button you created to
print the report.

From the tone of your question, I'm getting the impression
that you're not particularly comfortable with VBA coding.
If so, then use create a new command button on your form and
let the wizard create the code to print/preview your report.
Then open the form's module, look for the new event
procedure and modify it to look like what I posted earlier.
If you're still having trouble, copy/paste the Sub procedure
into a followup post so I can see what you have and the
names you're using.

 

[Guest]

Thanks Marshall,
I found a very easy way around it.
See the Holy Cow post. ;-)

I'm comforatble with basic VBA. But I'm very unsure about
the whole Table relationship thing. So I was just unclear
where to get the actual data from.

I do have one more little problem though.
Do you happen know the VBA code to make a command button
produce the standard pop-up message "Are you sure you want
to do this" type option? I would like the use to be able
to cancel if neccessary.

Thanks,
-Scott

 

[Marshall Barton]

Thanks Marshall,
I found a very easy way around it.
See the Holy Cow post. ;-)

I'm comforatble with basic VBA. But I'm very unsure about
the whole Table relationship thing. So I was just unclear
where to get the actual data from.

I do have one more little problem though.
Do you happen know the VBA code to make a command button
produce the standard pop-up message "Are you sure you want
to do this" type option? I would like the use to be able
to cancel if neccessary.

Use the MsgBox function, it has lots of options for various
buttons, icons, etc. Too many to explain here, so check
Help for details.