repost: Printing out form + subform

  • Thread starter Thread starter Edm
  • Start date Start date
E

Edm

Hi,

Several folks on here have been brave enough to try to
tackle this issue with no success. I cling to the hope,
however, that someone out there may be able to assist me.

The issue:

Im trying to print an invoice from a form and its
subform. The form contains billing data. The subform
contains individual service calls. The rub is that I
need 1 billing set + 1 service call on each invoice, and
what im getting is 1 billing set + all service calls.

What it would look like in your hands is this

Want: BILL
CALL

Get: BILL
CALL
CALL
CALL

ad nauseum...

Ive tried a simple query to generate the singluar result
needed, but no luck.

Does anyone out there know how to resolve this issue?

Thanks,

Edm
 
Hi,

Several folks on here have been brave enough to try to
tackle this issue with no success. I cling to the hope,
however, that someone out there may be able to assist me.

The issue:

Im trying to print an invoice from a form and its
subform.
Click to expand...

Forms are designed and optimized for onscreen viewing. Reports are
designed for printing. If you want to print, use a Report rather than
a Form; they have features (such as the very powerful Sorting and
Grouping dialog) that forms lack.
The form contains billing data. The subform
contains individual service calls.
Click to expand...

No, they don't; any more than my office window contains the Owyhee
Mountains. Your data IS STORED IN TABLES, not in your form, which is
just a tool to edit the data *into* the tables. Your Report needs to
be based on a query of the tables.
The rub is that I
need 1 billing set + 1 service call on each invoice, and
what im getting is 1 billing set + all service calls.
Click to expand...

What is the Record Source property of your form? It sounds like an
incorrect join is at fault. Please open the Form in design view; find
its Properties (View... Properties if the properties window isn't
visible); on the Data tab click the ... by the Record Source property
to open a query view; and select View...SQL. Copy and paste the SQL to
a message here.
What it would look like in your hands is this

Want: BILL
CALL

Get: BILL
CALL
CALL
CALL

ad nauseum...

Ive tried a simple query to generate the singluar result
needed, but no luck.

Does anyone out there know how to resolve this issue?
Click to expand...

By a) using a Report rather than a Form and b) basing the report on a
correct query - which I can't help you with at the moment since I have
no idea how your tables are structured nor how you want to identify
"the 1 service call".
 
Hi Edm:

Several methods come to mind:

1) you can add a clickbutton in which the user clicks to "activate" the CALL
that they wish to send off. In your report, you would place in the
subreport's recordsource something like the following-

SELECT Account.* FROM Account WHERE ((([Account].[ORDER3])= -1));

2) I you want to send off 4 CALLs at once, then you could set up code based
on the recordset method-

SQLStmt = "SELECT TOP 1 ACCOUNT.* FROM ACCOUNT;"
Set rst = dbs.OpenRecordset("SELECT Accounts.* FROM Accounts;",
dbOpenDynaset)
On Error Resume Next
With rst
.MoveLast
.MoveFirst
If .recordcount = 0 Then
Exit Sub
End If
Do Until rst.EOF 'loop procedure to fill in counter field
.Edit
![ORDER3] = -1 'checkbox field
.UPDATE
DoCmd.OpenReport stDocName, acViewNormal
'again, the report's subreport should have the
'recordsource property as noted in 1) above.
.Edit
![ORDER3] = 0 'checkbox field
.UPDATE
.MoveNext
Loop

3) I you want to send off 4 CALLs at once, then you could also set up code
based on the TOP paramenter-

DoCmd.RunSQL "INSERT INTO Accounttemp SELECT Account.* FROM Account WHERE
(((Account.ORDER3) = -1));
DoCmd.RunSQL "UPDATE Accounttemp SET WHERE (((Accounttemp.ORDER4) = -1));

Do Until DCount("[Acct]","Accounttemp") = 0
DoCmd.RunSQL "INSERT INTO Accounttemp2 SELECT TOP 1 Account.* FROM Account
WHERE (((Account.ORDER3) = -1));
DoCmd.RunSQL "DELETE ACCOUNTTEMP.* FROM ACCOUNTTEMP SELECT TOP 1WHERE
(((Account.ORDER3) = -1));
' now use a report with a recordset based on something like-
"Accounttemp2"
DoCmd.OpenReport stDocName, acViewNormal
..MoveNext
Loop

There you go- before had nothing; now you have 3 methods to accomplish the
same thing! They may have some bugs (I didn't test them), but if you get
stuck, just holler...

Regards,
Al
 
Two things:

1) delete this line in #2 method-

SQLStmt = "SELECT TOP 1 ACCOUNT.* FROM ACCOUNT;"

2) oh gosh- if I had know that John had already answered the thing, I would
have gone on! I downloaded the message and had an errand to perform before
finishing up my answer. Things get solved pretty quickly here!

I did think that he was eventually using reports, not the actual forms,
although he doesn't actually mention the use of reports. Hmmm... Hopefully
he knows better...

Regards,
Al

Al Borges said:
Hi Edm:

Several methods come to mind:

1) you can add a clickbutton in which the user clicks to "activate" the CALL
that they wish to send off. In your report, you would place in the
subreport's recordsource something like the following-

SELECT Account.* FROM Account WHERE ((([Account].[ORDER3])= -1));

2) I you want to send off 4 CALLs at once, then you could set up code based
on the recordset method-

SQLStmt = "SELECT TOP 1 ACCOUNT.* FROM ACCOUNT;"
Set rst = dbs.OpenRecordset("SELECT Accounts.* FROM Accounts;",
dbOpenDynaset)
On Error Resume Next
With rst
.MoveLast
.MoveFirst
If .recordcount = 0 Then
Exit Sub
End If
Do Until rst.EOF 'loop procedure to fill in counter field
.Edit
![ORDER3] = -1 'checkbox field
.UPDATE
DoCmd.OpenReport stDocName, acViewNormal
'again, the report's subreport should have the
'recordsource property as noted in 1) above.
.Edit
![ORDER3] = 0 'checkbox field
.UPDATE
.MoveNext
Loop

3) I you want to send off 4 CALLs at once, then you could also set up code
based on the TOP paramenter-

DoCmd.RunSQL "INSERT INTO Accounttemp SELECT Account.* FROM Account WHERE
(((Account.ORDER3) = -1));
DoCmd.RunSQL "UPDATE Accounttemp SET WHERE (((Accounttemp.ORDER4) = -1));

Do Until DCount("[Acct]","Accounttemp") = 0
DoCmd.RunSQL "INSERT INTO Accounttemp2 SELECT TOP 1 Account.* FROM Account
WHERE (((Account.ORDER3) = -1));
DoCmd.RunSQL "DELETE ACCOUNTTEMP.* FROM ACCOUNTTEMP SELECT TOP 1WHERE
(((Account.ORDER3) = -1));
' now use a report with a recordset based on something like-
"Accounttemp2"
DoCmd.OpenReport stDocName, acViewNormal
.MoveNext
Loop

There you go- before had nothing; now you have 3 methods to accomplish the
same thing! They may have some bugs (I didn't test them), but if you get
stuck, just holler...

Regards,
Al

Edm said:
Hi,

Several folks on here have been brave enough to try to
tackle this issue with no success. I cling to the hope,
however, that someone out there may be able to assist me.

The issue:

Im trying to print an invoice from a form and its
subform. The form contains billing data. The subform
contains individual service calls. The rub is that I
need 1 billing set + 1 service call on each invoice, and
what im getting is 1 billing set + all service calls.

What it would look like in your hands is this

Want: BILL
CALL

Get: BILL
CALL
CALL
CALL

ad nauseum...

Ive tried a simple query to generate the singluar result
needed, but no luck.

Does anyone out there know how to resolve this issue?

Thanks,

Edm
Click to expand...
Click to expand...
 
Back
Top