Forms and reports - Updating

[Roderick O'Regan]

(Access 2002 - Windows XP Pro)

I have a table "orders", a form "NewOrders" with a subform
"newOrderSub", a select query called "queryTotals" and a report
"rptPO"

To start the process, I open NewOrders, write all the details about
cost centres, delivery, etc on the main form. The details of the
orders go in the subform e.g.. item ordered, cost.

On the main form there is a command button with the following code
behind it:

stDocName = "rptPO"
stLinkCriteria = "[PO_Nbr]=" & Me![PO_Nbr]
Me.Refresh
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

This opens the report (rptPO) based on the query (queryTotals) for the
user to check that everything is OK, no spelling mistakes, etc. All
the calculations, VAT, sub-totals, etc. are correct. User can print
it.

If a mistake is spotted, e.g. wrong amount, incorrect cost centre,
then the report is closed by the user and focus returns to the form
for alterations. I want to be able to see what I had written.

I've tried a combination in my code using the commands Me.Repaint or
..Refresh or .Requery or even nothing at all.

Each one has a different effect: the report is correct with its
calculations but the form loses its sub-form details. Or, the report
is not correct(No VAT calculations, for instance) but the form is OK.

What I'm trying to do is to make the underlying table update itself
before going to the report, see that report shows OK and then return
to the form with details still intact.

Any pointers on where I'm going wrong, please?

Roderick

 

[Rob Parker]

Hi Roderick,

It's rather late at night here, and I'm nearly asleep, so I've not exactly
followed your comments about what exactly you're trying to do. But, if it's
simply a matter of ensuring that any edits to the current record are saved
to the underlying tables (since you've got a subform, you've presumably got
a related table for order details), then all you should need is the
following line before your current code to open the report:
Me.Dirty = False

HTH,

Rob

 

[Roderick O'Regan]

Hope you're refreshed now after your nigh'ts rest! Prior to my posting
I was on a LiveMeeting conference with one of my clients in Australia.
Small world!

Anyway, thanks for your advice. It seems to have done the trick.

I'm always amazed at the power of newsgroups. One can hack away all
day trying to solve a puzzle with little effect. Quick post to a
newsgroup. Half an hour later a message back form a guy on the other
side of the world and...bingo!

Brilliant. Thanks again.

Roderick

Hi Roderick,

It's rather late at night here, and I'm nearly asleep, so I've not exactly
followed your comments about what exactly you're trying to do. But, if it's
simply a matter of ensuring that any edits to the current record are saved
to the underlying tables (since you've got a subform, you've presumably got
a related table for order details), then all you should need is the
following line before your current code to open the report:
Me.Dirty = False

HTH,

Rob

(Access 2002 - Windows XP Pro)

I have a table "orders", a form "NewOrders" with a subform
"newOrderSub", a select query called "queryTotals" and a report
"rptPO"

To start the process, I open NewOrders, write all the details about
cost centres, delivery, etc on the main form. The details of the
orders go in the subform e.g.. item ordered, cost.

On the main form there is a command button with the following code
behind it:

stDocName = "rptPO"
stLinkCriteria = "[PO_Nbr]=" & Me![PO_Nbr]
Me.Refresh
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

This opens the report (rptPO) based on the query (queryTotals) for the
user to check that everything is OK, no spelling mistakes, etc. All
the calculations, VAT, sub-totals, etc. are correct. User can print
it.

If a mistake is spotted, e.g. wrong amount, incorrect cost centre,
then the report is closed by the user and focus returns to the form
for alterations. I want to be able to see what I had written.

I've tried a combination in my code using the commands Me.Repaint or
.Refresh or .Requery or even nothing at all.

Each one has a different effect: the report is correct with its
calculations but the form loses its sub-form details. Or, the report
is not correct(No VAT calculations, for instance) but the form is OK.

What I'm trying to do is to make the underlying table update itself
before going to the report, see that report shows OK and then return
to the form with details still intact.

Any pointers on where I'm going wrong, please?

Roderick