Updating a bound control on a report

[Guest]

I have a report bound to a work order table. When a work order is printed
(via the report) it is considered to be issued and a field named IssueDate in
the table is updated with the current date.

Sometimes, I need a batch operation to print work orders that are not yet
printed/issued (like at the end of the week). I have a button on a
switchboard form that when clicked, changes the RecordSource of the report
from the underlying table to a query that selects all records where the
IssueDate field is empty. However, when I run the report, the IssueDate bound
control on the report is empty, since the report is based on this query. If I
create a RecordSet and update the IssueDate field to reflect the current
date, the report fails because there are now no records with an empty
IssueDate field.

Is there any way to set the value of the report's IssueDate control at
runtime? Or, do I need an unbound text box control, and if this is the case,
how would I set the value of this text box in VBA?

Thanks

 

[Steve]

An unbound text box control for IssueDate would work. Put the following
expression in the textbox's control source:
=Date()

Be aware that the IssueDate in the table will not be changed when the work
order is printed!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Thanks, Steve. I've been experimenting since I posted my question. I have
added an unbound text box with the control source set to Date. I placed it
almost, but not completely on top of the bound control IssueDate. When I am
changing the RecordSource of the report, I also make the bound control
invisible and the unbound control visible. Then I reverse these settings
after the report is finished. Seems there would be an easier way, but I
cannot find it.

Thanks again,
Lowell

 

[Marshall Barton]

I have a report bound to a work order table. When a work order is printed
(via the report) it is considered to be issued and a field named IssueDate in
the table is updated with the current date.

Sometimes, I need a batch operation to print work orders that are not yet
printed/issued (like at the end of the week). I have a button on a
switchboard form that when clicked, changes the RecordSource of the report
from the underlying table to a query that selects all records where the
IssueDate field is empty. However, when I run the report, the IssueDate bound
control on the report is empty, since the report is based on this query. If I
create a RecordSet and update the IssueDate field to reflect the current
date, the report fails because there are now no records with an empty
IssueDate field.

Is there any way to set the value of the report's IssueDate control at
runtime? Or, do I need an unbound text box control, and if this is the case,
how would I set the value of this text box in VBA?

I deal with this kind of thing by updating the table before
printing the report, then using a filter for the date.

Dim db As Database
Set db = CurrentDb()
db.Execute "Update table Set dtfield = Date() " _
& "WHERE dtfield Is Null"
DoCmd.OpenReport "repostname", , , "dtfield = Date()"

 

[Steve]

You can simplify all this by doing the following ---

Use only one textbox named IssueDate for IssueDate. Put the following code
in the report's Open event:
If Me.Recordsource = "NameOfYourWorkOrderTable" Then
Me!IssueDate.ControlSource = "IssueDate"
Else
Me!IssueDate.ControlSource = "=Date()"
End If

This code automates IssueDate. If your report is based on your work order
table, IssueDate will be bound to IssueDate. If your report is based on your
query, IssueDate will be unbound and show the current date.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

I deal with this kind of thing by updating the table before
printing the report, then using a filter for the date.

Dim db As Database
Set db = CurrentDb()
db.Execute "Update table Set dtfield = Date() " _
& "WHERE dtfield Is Null"
DoCmd.OpenReport "repostname", , , "dtfield = Date()"

Marsh - Good idea. I'm not sure it will work for me in my situation. It
could be my client will print some orders in the morning and some in the
afternoon of the same day. If I update the table before printing each report,
won't the morning orders be printed again in the afternoon?

 

[Marshall Barton]

Marsh - Good idea. I'm not sure it will work for me in my situation. It
could be my client will print some orders in the morning and some in the
afternoon of the same day. If I update the table before printing each report,
won't the morning orders be printed again in the afternoon?

I was wondering about that possibility ;-)

How about using Now() instead of Date()?

Dim db As Database
Dim PrTime As Date
Set db = CurrentDb()
PrTime = Format(Now, "\#yyyy\-m\-d\#")
db.Execute "Update table " _
& "Set dtfield = " & PrTime _
& " WHERE dtfield Is Null"
DoCmd.OpenReport "repostname", , , "dtfield = " & PrTime

 

[Guest]

I was wondering about that possibility ;-)

How about using Now() instead of Date()?

Dim db As Database
Dim PrTime As Date
Set db = CurrentDb()
PrTime = Format(Now, "\#yyyy\-m\-d\#")
db.Execute "Update table " _
& "Set dtfield = " & PrTime _
& " WHERE dtfield Is Null"
DoCmd.OpenReport "repostname", , , "dtfield = " & PrTime

Perhaps. I'll give it some thought. Thanks.

 

[Marshall Barton]

Perhaps. I'll give it some thought. Thanks.

I have some oops in there. If you try it, these lines need
to be:

Dim PrTime As String
. . .
PrTime = Format(Now, "\#yyyy\-m\-d h:nn:ss\#")
. . .