Updating upon report close

  • Thread starter Thread starter Tim Fierro
  • Start date Start date
T

Tim Fierro

Hello, I have been using the following line in my report's close event after
doing a couple of If/Then type statements to insure this is what I want to
do.

CurrentDb.Execute "Update tblLabor Set tblLabor.[laborStatus] =
'C' Where tblLabor.[laborBoatId] = 5 AND tblLabor.[laborStatus] = 'O';",
dbFailOnError

The above is all on 1 line and works just fine. I have now added a new
field in the table called [laborPosted]. This will now allow me to not only
Close a line that matches the above criteria of Boat #5 and a status of
Open; but I am hoping to add a date that this line was closed.

I believe maybe there is a way to add some form of Set
tblLabor.[laborPosted] = "TODAY'S DATE" somehow, but I am not sure how. The
reason I believe I should change the above line and add the new one, is that
if I add a whole new line after this one; the criteria of "O"pen will not be
in effect; it would have already been closed as well as every other line in
the database up to this time.

So I think I need to update my line I already have instead of adding a new
line. Am I correct and does anyone know if you can do what I am proposing?
If not the way I propose, is there a proper way it is supposed to be done?

Thank you,

Tim
 
Hello, I have been using the following line in my report's close event after
doing a couple of If/Then type statements to insure this is what I want to
do.

CurrentDb.Execute "Update tblLabor Set tblLabor.[laborStatus] =
'C' Where tblLabor.[laborBoatId] = 5 AND tblLabor.[laborStatus] = 'O';",
dbFailOnError

The above is all on 1 line and works just fine. I have now added a new
field in the table called [laborPosted]. This will now allow me to not only
Close a line that matches the above criteria of Boat #5 and a status of
Open; but I am hoping to add a date that this line was closed.

I believe maybe there is a way to add some form of Set
tblLabor.[laborPosted] = "TODAY'S DATE" somehow, but I am not sure how. The
reason I believe I should change the above line and add the new one, is that
if I add a whole new line after this one; the criteria of "O"pen will not be
in effect; it would have already been closed as well as every other line in
the database up to this time.

So I think I need to update my line I already have instead of adding a new
line. Am I correct and does anyone know if you can do what I am proposing?
If not the way I propose, is there a proper way it is supposed to be done?

Thank you,

Tim
Click to expand...

You can include additional fields in the Update by separating the
fields with a comma.
The current date is generated by using Date in VBA.
The current date and time is returned by using Now.

CurrentDb.Execute "Update tblLabor Set tblLabor.[laborStatus] =
'C', tblLabor.[LaborPosted] = #" & Date & "# Where
tblLabor.[laborBoatId] = 5 AND tblLabor.[laborStatus] = 'O';",
dbFailOnError
 
I believe maybe there is a way to add some form of Set
tblLabor.[laborPosted] = "TODAY'S DATE" somehow, but I am not sure how.
The
Click to expand...

You can include additional fields in the Update by separating the
fields with a comma.

CurrentDb.Execute "Update tblLabor Set tblLabor.[laborStatus] =
'C', tblLabor.[LaborPosted] = #" & Date & "# Where
tblLabor.[laborBoatId] = 5 AND tblLabor.[laborStatus] = 'O';",
dbFailOnError
Click to expand...

Thank you Fred, that was exactly what I needed; and now I know how.

Tim
 
Back
Top