New Record every Friday morning

[Matt]

I have table that has the following fields:

JobNumber
WeekEndingDate
ReportRequired_ 01
ReportRequired_ 02

every Friday morning the weekending date changes. I need to
automatically generate a new record for each record that has a job
number and weekending date for the new weekending date.

i.e. JobNumber = 12345, WeekEndingDate = "4/12/2007",
ReportRequired_ 01 = yes, ReportRequired_ 02 = yes

This Friday, 4/20/2007, at 12am I would need to generate a duplicate
record for all jobs that have a weekending record of 4/12/07 and grab
the settings of reportRequired fields of the previous week. I have
total visual agent that I can use to automate the query or macro but I
am not sure how i would setup that query.

Thanks

[Don Leverton]

Hi Matt,

So ... this PC is left on and the database is open 24 / 7 / 365?
Assuming that this IS the case ... here are some thoughts:
Use a form timer event to test the Weekday value of Now() so that when 12:01
AM rolls around
and the Weekday evaluates to "6" (Friday) ... both conditions met ...
the following code should run and duplicate all records from the following
week.

Here is the form's code (Tested and appears to be working correctly ...You
may wish to add error handling.)
=============== =============== =============== =====
Option Compare Database
Option Explicit
' This variable is "in scope" as long as this form remains open, and is
False by default.
' So, if this form is closed and then re-opened (on a Friday only), the
duplication will occur again.
' You could prevent that from happening by declaring it as a Public variable
in a database module.
Dim blnCodeRun As Boolean
Private Sub Form_Timer()
'Timer interval is 6,000 milliseconds (60 seconds)
Dim vCurrDay
Dim vCurrDate As Date
Dim vLastFriday As Date
Dim strFind As String
Dim x
Dim Msg As String
vCurrDay = WeekDay(Now)
vCurrDate = Date

Select Case vCurrDay

Case 7 'Saturday
blnCodeRun = False 'This will remain False until the next Friday,
when the code runs again.

Case 6 'Friday (Set to current day if you want to test this eg 3 =
Tuesday)
'This code should run ONCE every Friday at 12:01 AM
If blnCodeRun = False Then

Dim rst As DAO.Recordset
Set rst = Me.Form.Records etClone

vLastFriday = DateAdd("d", -7, Date)

With rst
.MoveLast
.MoveFirst
strFind = "WeekEnding Date = " & "#" & vLastFriday & "#"
.FindFirst strFind

If .NoMatch Then
MsgBox "No details exist for Friday, " & vLastFriday
Else
Do Until .NoMatch

Dim vPrevJob
vPrevJob = !JobNumber
Dim PrevRpt01
PrevRpt01 = !ReportRequired _01
Dim PrevRpt02
PrevRpt02 = !ReportRequired _02

.AddNew
x = x + 1 'get a count of the number of records added
!WeekEndingDate = Date
!JobNumber = vPrevJob
!ReportRequired _01 = PrevRpt01
!ReportRequired _02 = PrevRpt02
.Update

.FindNext strFind
Loop

Msg = ""
Msg = x & " records from Friday, " & vLastFriday
Msg = Msg & " have been succesfully duplicated."
MsgBox Msg
blnCodeRun = True
Me.Bookmark = .LastModified
'This makes the last record visible in the form after
completion.
.Close
Set rst = Nothing
End If
End With
End If

End Select

End Sub
"Matt" <mw******@caldr ywall.comwrote in message
news:11******** **************@ p77g2000hsh.goo glegroups.com.. .

>I have table that has the following fields:

JobNumber
WeekEndingDate
ReportRequired_ 01
ReportRequired_ 02

every Friday morning the weekending date changes. I need to
automatically generate a new record for each record that has a job
number and weekending date for the new weekending date.

i.e. JobNumber = 12345, WeekEndingDate = "4/12/2007",
ReportRequired_ 01 = yes, ReportRequired_ 02 = yes

This Friday, 4/20/2007, at 12am I would need to generate a duplicate
record for all jobs that have a weekending record of 4/12/07 and grab
the settings of reportRequired fields of the previous week. I have
total visual agent that I can use to automate the query or macro but I
am not sure how i would setup that query.

Thanks