Report with table update?

  • Thread starter Thread starter Parts Manager
  • Start date Start date
P

Parts Manager

Hello,

I am working on a report, now completed, that prints out an invoice of all
items used for a customer. All looks good and is ready to go.

However, I am now pondering the best way to update all the invoices that are
printed so that the flag in the table changes from "P" (For Pending) to "C"
(For Closed).

If I print the report, I don't want to automatically have the flag in the
table set until I know the printed invoice has printed successfully. So I
am not sure if I want to do this in some AFTER print (if there is an event
like this in the Reports) event or not. Maybe if I print the report, some
event transpires that asks a question if the print was successful and if it
was, then it could go change all the flags.

Another option I thought of was to create a form using the same control
source query. After the printing of the invoices (report) was ran and was
successful, I would run this new form with the query and just update the
flag through this form in some loop or something.

Any suggestions or some normal practice that is in place to adhere to,
especially when updating flags like this (Status Flag/Codes)?

Tim
 
Hello,

I am working on a report, now completed, that prints out an invoice of all
items used for a customer. All looks good and is ready to go.

However, I am now pondering the best way to update all the invoices that are
printed so that the flag in the table changes from "P" (For Pending) to "C"
(For Closed).

If I print the report, I don't want to automatically have the flag in the
table set until I know the printed invoice has printed successfully. So I
am not sure if I want to do this in some AFTER print (if there is an event
like this in the Reports) event or not. Maybe if I print the report, some
event transpires that asks a question if the print was successful and if it
was, then it could go change all the flags.

Another option I thought of was to create a form using the same control
source query. After the printing of the invoices (report) was ran and was
successful, I would run this new form with the query and just update the
flag through this form in some loop or something.

Any suggestions or some normal practice that is in place to adhere to,
especially when updating flags like this (Status Flag/Codes)?

Tim
Click to expand...

You can place the following code in the Report's Close event.
This way you can preview the report without printing it and not change
the value of the (I assume) Yes/No field.

If MsgBox("Did the Report print OK and do you want to update the file
that it has been printed?",vbYesNo) - vbYes Then
CurrentDb.Execute "Update YourTable Set YourTable.[FieldName] = -1;",
dbFailOnError
End If

If No is clicked, nothing will happen. If Yes is clicked, the field is
updated.

By the way, the above will mark all records in the table as printed.
Is that what you want?
 
However, I am now pondering the best way to update
all the invoices that are printed so that the flag in the
table changes from "P" (For Pending) to "C" (For Closed).

Maybe if I print the report, some event transpires that
asks a question if the print was successful and if it
was, then it could go change all the flags.
Click to expand...

You can place the following code in the Report's
Close event. This way you can preview the report
without printing it and not change
the value of the (I assume) Yes/No field.

If MsgBox("Did the Report print OK and do you _
want to update the file that it has been printed?", _
vbYesNo) - vbYes Then
CurrentDb.Execute "Update YourTable Set _
YourTable.[FieldName] = -1;",
dbFailOnError
End If

If No is clicked, nothing will happen. If Yes is clicked,
the field is updated.
Click to expand...

Fred, thanks for the reply and the help on this.

I think your suggestion of "Did the Report print OK...." is the method that
would be preferred. I can print out the invoices, verify they all printed
fine, then answer YES to the 'question' posed.

The way I see your code, I would just need to drop this into the Report's
Closed Event and will be good with the logic. Then I will need to change
your suggested CurrentDb.Execute statement to something more appropriate to
my table.

An example is the report will generate an invoice with many items spanning
over 4 pages, which is probably about 100 items on the invoice. Each item
is a record in the table (tblInvoiceLine) and the field I need to change
will be (invlineStatus). When this report is generated it will pull all
open invoice lines with a status code of "P" for pending. I want to now
change the field 'invlineStatus' to "C" for closed. Of course, change the
status after a successful print of the invoice.

So with your last lines;
By the way, the above will mark all records in the table
as printed. Is that what you want?
Click to expand...

Yes, I want all records in this REPORT based on a QUERY to be changed; but I
do not want to categorically update/change ALL records. This is why I will
need to adjust the actual change code to work.

Can you also suggest a loop or procedure to change a single field based upon
what has just printed in the report? The report is based on the Query:
"BoatInvoice" and so it will be important that I only update/change the
status for the records that printed. I know I can find all those records
from this Query, so now need to figure out a few lines of code to update all
those records.

Table: tblInvoiceLine
Field: invlineStatus
Goal: Change the field from "P" to a "C" after printing of the invoice
during the report's 'Close Event'.



Tim
 
However, I am now pondering the best way to update
all the invoices that are printed so that the flag in the
table changes from "P" (For Pending) to "C" (For Closed).

Maybe if I print the report, some event transpires that
asks a question if the print was successful and if it
was, then it could go change all the flags.
Click to expand...

You can place the following code in the Report's
Close event. This way you can preview the report
without printing it and not change
the value of the (I assume) Yes/No field.

If MsgBox("Did the Report print OK and do you _
want to update the file that it has been printed?", _
vbYesNo) - vbYes Then
CurrentDb.Execute "Update YourTable Set _
YourTable.[FieldName] = -1;",
dbFailOnError
End If

If No is clicked, nothing will happen. If Yes is clicked,
the field is updated.
Click to expand...

Fred, thanks for the reply and the help on this.

I think your suggestion of "Did the Report print OK...." is the method that
would be preferred. I can print out the invoices, verify they all printed
fine, then answer YES to the 'question' posed.

The way I see your code, I would just need to drop this into the Report's
Closed Event and will be good with the logic. Then I will need to change
your suggested CurrentDb.Execute statement to something more appropriate to
my table.

An example is the report will generate an invoice with many items spanning
over 4 pages, which is probably about 100 items on the invoice. Each item
is a record in the table (tblInvoiceLine) and the field I need to change
will be (invlineStatus). When this report is generated it will pull all
open invoice lines with a status code of "P" for pending. I want to now
change the field 'invlineStatus' to "C" for closed. Of course, change the
status after a successful print of the invoice.

So with your last lines;
By the way, the above will mark all records in the table
as printed. Is that what you want?
Click to expand...

Yes, I want all records in this REPORT based on a QUERY to be changed; but I
do not want to categorically update/change ALL records. This is why I will
need to adjust the actual change code to work.

Can you also suggest a loop or procedure to change a single field based upon
what has just printed in the report? The report is based on the Query:
"BoatInvoice" and so it will be important that I only update/change the
status for the records that printed. I know I can find all those records
from this Query, so now need to figure out a few lines of code to update all
those records.

Table: tblInvoiceLine
Field: invlineStatus
Goal: Change the field from "P" to a "C" after printing of the invoice
during the report's 'Close Event'.

Tim
Click to expand...

Let me see if I understand your set up now.
You have a field named [invlineStatus].
It is a Text datatype field, not a check box field as I originally
thought.
Among it's possible values are a "P" or a "C".

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P';", dbFailOnError

The above shold be all on one line, unless you know how to break the
line.

I assume the query (and the report) will show all of the 'P' records.
If the query only shows some of the 'P' records, then you need to
further restrict the Update using whatever other criteria was used by
the query to restrict the data.
 
fredg said:
Table: tblInvoiceLine
Field: invlineStatus
Goal: Change the field from "P" to a "C" after printing of the invoice
during the report's 'Close Event'.

Tim
Click to expand...

Let me see if I understand your set up now.
You have a field named [invlineStatus].
It is a Text datatype field, not a check box field
as I originally thought. Among it's possible values
are a "P" or a "C".

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P';", dbFailOnError

The above shold be all on one line, unless you know how to break the
line.

I assume the query (and the report) will show all of the 'P' records.
If the query only shows some of the 'P' records, then you need to
further restrict the Update using whatever other criteria was used by
the query to restrict the data.
Click to expand...

Fred,

Yes, you have an understanding of what I am trying to accomplish. :-)

I will use your code and do test runs to verify it all works correctly, but
it sure looks what I need. And I do appreciate you making note about the
Restricting The Update. It is true that this query used to pull out these
lines to print, does have another criteria used besides the status flag.
Had I used your code, I suppose all invoices that were 'P'ending would have
been changed to 'C'losed. Now that wouldn't have been good.

I have one other criteria and that is BoatID = 8 so that only items used on
this boat will print out in the report and only those items that are
pending. I will update your statement to this;
CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P';", dbFailOnError
Click to expand...

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus]='C' Where "AND I NEED HELP HERE"

I understand the part about tblInvoiceLine.[invlineStatus]='P', but I want
to insert before the other criteria used in the Query.

Here is the SQL statement's view of what the criteria is I am using;

SELECT tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId,
tblInvoiceLine.invlineQty, tblItem.itemDescription,
tblInvoiceLine.invlinePrice
FROM tblItem INNER JOIN (tblInvoice INNER JOIN tblInvoiceLine ON
tblInvoice.invId = tblInvoiceLine.invlineInvId) ON tblItem.itemId =
tblInvoiceLine.invlineItemId
WHERE (((tblInvoice.invBoatId) Like "8") AND ((tblInvoiceLine.invlineStatus)
Like "O"))
ORDER BY tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId;

I need to make sure the only records changed are related to Boat #8 which is
tblInvoice.invBoatId. How do I reference this when it is not in the same
table as the invoice line items?

Maybe you can tell, this is my first real report I am tackling. :-)


Thank you for the help on this.

Tim
 
fredg said:
Table: tblInvoiceLine
Field: invlineStatus
Goal: Change the field from "P" to a "C" after printing of the invoice
during the report's 'Close Event'.

Tim
Click to expand...

Let me see if I understand your set up now.
You have a field named [invlineStatus].
It is a Text datatype field, not a check box field
as I originally thought. Among it's possible values
are a "P" or a "C".

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P';", dbFailOnError

The above shold be all on one line, unless you know how to break the
line.

I assume the query (and the report) will show all of the 'P' records.
If the query only shows some of the 'P' records, then you need to
further restrict the Update using whatever other criteria was used by
the query to restrict the data.
Click to expand...

Fred,

Yes, you have an understanding of what I am trying to accomplish. :-)

I will use your code and do test runs to verify it all works correctly, but
it sure looks what I need. And I do appreciate you making note about the
Restricting The Update. It is true that this query used to pull out these
lines to print, does have another criteria used besides the status flag.
Had I used your code, I suppose all invoices that were 'P'ending would have
been changed to 'C'losed. Now that wouldn't have been good.

I have one other criteria and that is BoatID = 8 so that only items used on
this boat will print out in the report and only those items that are
pending. I will update your statement to this;
CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P';", dbFailOnError
Click to expand...

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus]='C' Where "AND I NEED HELP HERE"

I understand the part about tblInvoiceLine.[invlineStatus]='P', but I want
to insert before the other criteria used in the Query.

Here is the SQL statement's view of what the criteria is I am using;

SELECT tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId,
tblInvoiceLine.invlineQty, tblItem.itemDescription,
tblInvoiceLine.invlinePrice
FROM tblItem INNER JOIN (tblInvoice INNER JOIN tblInvoiceLine ON
tblInvoice.invId = tblInvoiceLine.invlineInvId) ON tblItem.itemId =
tblInvoiceLine.invlineItemId
WHERE (((tblInvoice.invBoatId) Like "8") AND ((tblInvoiceLine.invlineStatus)
Like "O"))
ORDER BY tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId;

I need to make sure the only records changed are related to Boat #8 which is
tblInvoice.invBoatId. How do I reference this when it is not in the same
table as the invoice line items?

Maybe you can tell, this is my first real report I am tackling. :-)

Thank you for the help on this.

Tim
Click to expand...

According to your SQL Where clause [BoatID] is text datatype (You
placed it within Quotes "8").

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P'
AND [BoatID] = '8';" , dbFailOnError

If , however, [BoatID] is a Number datatype, then use:

...... AND [BoatID] = 8;" , dbFailOnError


*********
NOTE: Your SQL Where clause above needs a bit of help.

Select .... WHERE (((tblInvoice.invBoatId) Like "8") AND
((tblInvoiceLine.invlineStatus) Like "O"))

In your Where clause use the = sign:

WHERE (((tblInvoice.invBoatId) = "8") AND
((tblInvoiceLine.invlineStatus) = "O"))

The LIKE keyword is used when you are using the wild card * and are
only inputting part of the text, i.e.
Where [BoatID] LIKE "8*" and [invlineStatus] LIKE "O*"
(assuming both [BoatID] and [invlineStatus] are Text datatypes.)

This would return all records if [BoatID] starts with the number 8
(8456, 812, 80, etc.) and the [invlineStatus] starts with the letter
O (Oregon, Ohio, etc. ).

Also, the above is hard coded into the query.
You cannot get a different BoatID or invlineStatus without changing
the query criteria. Is that what you want?

An alternative would be to use a query parameter prompt, so that the
user could use the same query to get different records, without
changing the query.

WHERE (((tblInvoice.invBoatId) = [Enter the BoatID] ) AND
((tblInvoiceLine.invlineStatus) = [Enter invlineStatus]))

You'll get prompted for both parameters.
If you use the above parameter you need to alter the Execute code in
the Report close event, as you are no longer hard coding the "8"
BoatID.

Add an unbound text control to the report header.
Set it's control source to
= [Enter the Boat ID]
(Make sure this is spelled exactly the same as in the query.)
Name this control "TextParameter".
You can make this control not visible (or make the entire Header not
visible, as you wish).

Then change the CurrentDb.Execute Update to:

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P'
AND [BoatID] = '" & [TextParameter] & "';" , dbFailOnError

Make sure the parameter Enter BoatID is spelled exactly the same in
the query and in the report header.
Again, the above assumes [BoatID] is a Text datatype.

If [BoatID] is Number datatype, do not use the quotes around the 8 in
the hard coded code, and change the last part of the code using the
parameter to:

.... AND [BoatID] = " & [TextParameter] & ";" , dbFailOnError

Confused yet? :-)
 
fredg said:
Here is the SQL statement's view of what the criteria is I am using;
WHERE (((tblInvoice.invBoatId) Like "8") AND
((tblInvoiceLine.invlineStatus)
Like "O"))
Click to expand...

According to your SQL Where clause [BoatID] is text datatype (You
placed it within Quotes "8").

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where tblInvoiceLine.[invlineStatus] = 'P'
AND [BoatID] = '8';" , dbFailOnError

If , however, [BoatID] is a Number datatype, then use:

..... AND [BoatID] = 8;" , dbFailOnError


*********
NOTE: Your SQL Where clause above needs a bit of help.
Click to expand...

Yes, the BoatID is a numeric and I didn't catch this.

The SQL was done with the wizard and when I first reviewed my book, I saw
LIKE and falsely assumed it was what I needed. Now I reviewed the chapter
on queries and now know that "=" is the appropriate statement. As I cruise
more into Access, I am learning the correct way to do things that I did not
catch thus far.

Here is what the SQL shows now in that view;

SELECT tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId,
tblInvoiceLine.invlineQty, tblItem.itemDescription,
tblInvoiceLine.invlinePrice
FROM tblItem INNER JOIN (tblInvoice INNER JOIN tblInvoiceLine ON
tblInvoice.invId = tblInvoiceLine.invlineInvId) ON tblItem.itemId =
tblInvoiceLine.invlineItemId
WHERE (((tblInvoice.invBoatId)=8) AND ((tblInvoiceLine.invlineStatus)="O"))
ORDER BY tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId;

Also, the above is hard coded into the query.
You cannot get a different BoatID or invlineStatus without changing
the query criteria. Is that what you want?
Click to expand...

Yes, hard coded is quite fine for our purpose here. It takes months to
produce 1 boat, so this will work fine for us. Thank you for pointing that
out though since I can see where having 1 report is more beneficial than a
hard coded report.
An alternative would be to use a query parameter prompt, so that the
user could use the same query to get different records, without
changing the query.
Click to expand...
Confused yet? :-)
Click to expand...

Nope, I fully understand where you are going with this and it all makes
sense. :-)

My main concern when I left on Friday was the part about not needing
anything to identify the [BoatId] since that field is not technically in the
table 'tblInvoiceLine'. However it does have a relationship with
'tblInvoice'. I think this should be what I need; correct?

The only thing that still concerns me is whether or not;
[BoatID] should be changed to [tblInvoice.invBoatId].


CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where [BoatID] = 8 AND
tblInvoiceLine.[invlineStatus] = 'P';" , dbFailOnError

I rearranged to have Boat ID first as it is the primary focus to pull the
Open invoices from.

Time to go and test what you have taught me this fine Monday morning!

Thanks again Fred.
 
The only thing that still concerns me is whether or not;
[BoatID] should be changed to [tblInvoice.invBoatId].

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where [BoatID] = 8 AND
tblInvoiceLine.[invlineStatus] = 'P';" , dbFailOnError

Time to go and test what you have taught me this fine Monday morning!
Click to expand...


BTW, I thought I wanted to change status codes from Closed from Pending, but
it ended up being from Open to Closed. That is why you see a 'C' and 'O' in
the following code.



Fred,

I have tried;

CurrentDb.Execute "Update tblInvoiceLine Set tblInvoiceLine.[invlineStatus]
= 'C' Where [invBoatId] = 8 AND tblInvoiceLine.[invlineStatus] = 'O';",
dbFailOnError

and I have tried;

CurrentDb.Execute "Update tblInvoiceLine Set tblInvoiceLine.[invlineStatus]
= 'C' Where tblInvoice.[invBoatId] = 8 AND tblInvoiceLine.[invlineStatus] =
'O';", dbFailOnError


Here is my SQL view of the Query used to determine what I want to go back
and change on the invoice lines;

SELECT tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId,
tblInvoiceLine.invlineQty, tblItem.itemDescription,
tblInvoiceLine.invlinePrice
FROM tblItem INNER JOIN (tblInvoice INNER JOIN tblInvoiceLine ON
tblInvoice.invId = tblInvoiceLine.invlineInvId) ON tblItem.itemId =
tblInvoiceLine.invlineItemId
WHERE (((tblInvoice.invBoatId)=8) AND ((tblInvoiceLine.invlineStatus)="O"))
ORDER BY tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId;

I have not been able to figure out what my CurrentDb.Execute Update
statement should be to make sure I get it right. It keeps coming up an
error;

Run time error '3061':
Too few parameters. Expected 1.

RECAP
--------
Table: tblInvoiceLine
Field: invlineStatus
Goal: Change the field from "O" to a "C" after printing of the invoice
during the report's 'Close Event'.
Criteria for Change: tblInvoice.invBoatId = 8 AND
tblInvoiceLine.invlineStatus = "O"

I am going to see if I can find out what error 3061 is, but in the meantime,
do you see something glaringly obvious to this newbie that would set the
line correctly?

Tim
 
The only thing that still concerns me is whether or not;
[BoatID] should be changed to [tblInvoice.invBoatId].

CurrentDb.Execute "Update tblInvoiceLine Set
tblInvoiceLine.[invlineStatus] ='C'
Where [BoatID] = 8 AND
tblInvoiceLine.[invlineStatus] = 'P';" , dbFailOnError

Time to go and test what you have taught me this fine Monday morning!
Click to expand...

BTW, I thought I wanted to change status codes from Closed from Pending, but
it ended up being from Open to Closed. That is why you see a 'C' and 'O' in
the following code.

Fred,

I have tried;

CurrentDb.Execute "Update tblInvoiceLine Set tblInvoiceLine.[invlineStatus]
= 'C' Where [invBoatId] = 8 AND tblInvoiceLine.[invlineStatus] = 'O';",
dbFailOnError

and I have tried;

CurrentDb.Execute "Update tblInvoiceLine Set tblInvoiceLine.[invlineStatus]
= 'C' Where tblInvoice.[invBoatId] = 8 AND tblInvoiceLine.[invlineStatus] =
'O';", dbFailOnError

Here is my SQL view of the Query used to determine what I want to go back
and change on the invoice lines;

SELECT tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId,
tblInvoiceLine.invlineQty, tblItem.itemDescription,
tblInvoiceLine.invlinePrice
FROM tblItem INNER JOIN (tblInvoice INNER JOIN tblInvoiceLine ON
tblInvoice.invId = tblInvoiceLine.invlineInvId) ON tblItem.itemId =
tblInvoiceLine.invlineItemId
WHERE (((tblInvoice.invBoatId)=8) AND ((tblInvoiceLine.invlineStatus)="O"))
ORDER BY tblInvoice.invBoatId, tblInvoiceLine.invlineStatus,
tblInvoiceLine.invlineInvId, tblInvoiceLine.invlineId;

I have not been able to figure out what my CurrentDb.Execute Update
statement should be to make sure I get it right. It keeps coming up an
error;

Run time error '3061':
Too few parameters. Expected 1.

RECAP
--------
Table: tblInvoiceLine
Field: invlineStatus
Goal: Change the field from "O" to a "C" after printing of the invoice
during the report's 'Close Event'.
Criteria for Change: tblInvoice.invBoatId = 8 AND
tblInvoiceLine.invlineStatus = "O"

I am going to see if I can find out what error 3061 is, but in the meantime,
do you see something glaringly obvious to this newbie that would set the
line correctly?

Tim
Click to expand...

I just now spotted the fact that the query you included contains
several different tables. Sorry I missed that. Their names just ran
together for me.

Unfortunately, invBoatID is not in the same table as invLineStatus, so
your Update will fail.

Create a new query.
You need to include "tblInvoice" and "tblInvoiceLine" in the query
design view.
Make sure the joins are correct.

Add just the tblInvoice.invBoatID and the tblInvoiceLine.invLineStatus
fields to the grid.
Then click on the Query Tool Button and select Update query.

As criteria on the invBoatID column, enter
8
As criteria on the invLineStatus column, enter:
'O'

In the invLineStatus Update To: line, write
'C'

Run the update by clicking on the Bang symbol (!).
** Back up your table first. **
If the changes are correct, copy the query SQL.

Then go back to your CurrentDb.Execute code and paste the SQL in place
of your existing Update code.
Don't forget to place a " at the beginning and end of the SQL to make
it a string.
CurrentDb.Execute "Update ... etc... = 'O';",dbFailOnError

Try it now.
 
fredg said:
I just now spotted the fact that the query you included contains
several different tables. Sorry I missed that. Their names just ran
together for me.

Unfortunately, invBoatID is not in the same table as invLineStatus, so
your Update will fail.
Click to expand...

Yes, that it did. :-)
Create a new query.
You need to include "tblInvoice" and "tblInvoiceLine" in the query
design view.
Make sure the joins are correct.

Add just the tblInvoice.invBoatID and the tblInvoiceLine.invLineStatus
fields to the grid.
Then click on the Query Tool Button and select Update query.

As criteria on the invBoatID column, enter
8
As criteria on the invLineStatus column, enter:
'O'

In the invLineStatus Update To: line, write
'C'

Run the update by clicking on the Bang symbol (!).
Click to expand...

Everything worked!

You are about to update 224 rows. Are you sure? I said yes.

** Back up your table first. **
Click to expand...

DAMN! Now you say this. :-) Actually, I thought I had a copy of the
database from yesterday's testing, but I didn't. So I had to go ahead and
use my test reports as the real thing. That is fine except for that I
wanted to close 1 line item before the final test and printout. I did that
part manually since I didn't have a backup from last night, only from the
day before which had too much data to put in manually. Lessoned learned,
verify that a backup copy is available when testing.

If the changes are correct, copy the query SQL.

Then go back to your CurrentDb.Execute code and paste the SQL in place
of your existing Update code.
Don't forget to place a " at the beginning and end of the SQL to make
it a string.
CurrentDb.Execute "Update ... etc... = 'O';",dbFailOnError

Try it now.
Click to expand...


"You are about to update 224 rows. Are you sure? I said yes."


I am not able to test this in the actual Report's Close Event as I don't
have a copy. Since 224 records were updated (correctly, thank you), I will
wait until next billing to test in the Update Code section. Here is what I
got though;


UPDATE tblInvoice INNER JOIN tblInvoiceLine ON tblInvoice.invId =
tblInvoiceLine.invlineInvId SET tblInvoiceLine.invlineStatus = 'C'
WHERE (((tblInvoice.invBoatId)=8) AND ((tblInvoiceLine.invlineStatus)='O'));

' Parts Room - Boat # 8 & Open, Change to Closed
'
If MsgBox("Did the Report print OK, and do you want to UPDATE the MASTER
TABLE?", vbYesNo) = vbYes Then
'
CurrentDb.Execute "UPDATE tblInvoice INNER JOIN tblInvoiceLine
ON tblInvoice.invId = tblInvoiceLine.invlineInvId SET
tblInvoiceLine.invlineStatus = 'C' WHERE (((tblInvoice.invBoatId)=8) AND
((tblInvoiceLine.invlineStatus)='O'));", dbFailOnError
'
End If
'

The above is what I have put in the Report's Closed Event. I believe it
should work as I took the SQL statement and just replaced the old line I
had. I am pretty confident that with your help in producing this line, that
all should go well when I do the next billing cycle.

Thank you very much Fred for getting me over this hurdle. I am enjoying
having a database to work with parts instead of the old spreadsheet that was
here when I started.

Tim
 
Back
Top