Add days to dates and add new record

[Ben]

On a form, I have two fields StartDate and EndDate.

I want a command button to ask the user if there is a report next week or not.

"Is there a report due next week?"
If the answer is yes, then add 7 days to the StartDate and EndDate and have
those fields be in a new record. So i want it to add a new record and add 7
days to both dates.

If the answer is no, then add 14 days, etc.

How do I do this?

 

[PJFry]

On a form, I have two fields StartDate and EndDate.

I want a command button to ask the user if there is a report next week or not.

"Is there a report due next week?"
If the answer is yes, then add 7 days to the StartDate and EndDate and have
those fields be in a new record. So i want it to add a new record and add 7
days to both dates.

What do you mean by 'add a new record'? Are you looking to duplicate the
exisiting record with new dates? Or do you want to update your date fields
with the start and end dates plus 7?

 

[Ben]

PJ,
I am looking to click the button and it will add a new record. Not
duplicate. After it goes to a new record take the start and end dates and
add 7 days to those.

Ben

 

[PJFry]

Ok, I think will look something like this:

'Declare a variable for your original dates
Dim oStart As Date
Dim oEnd As Date
'Variable for the message box
Dim mBox As String


'Assign the values
oStart = <control for the start date, probably something like Me.StartDate>
oEnd = <control for the end date, probably something like Me.EndDate>

'Here is the message box. It will the user the next date
mBox = MsgBox("Is there a report due next week?", vbYesNo, "Week of " &
oStart)

'If they click yes, create the new record and exit
If mBox = vbYes Then
DoCmd.GoToRecord , , acNewRec
Me.StartDate = oStart + 7
Me.EndDate = oEnd + 7
Exit Sub
End If

'If they don't click yes, loop until they do, adding 7 days each time
Do Until mBox = vbYes
oStart = oStart + 7
oEnd = oEnd +7
mBox = MsgBox("Is there a report due next week?", vbYesNo, "Week of " &
oStart)
Loop

'After the loop exits, the new record is created and the values assigned
DoCmd.GoToRecord , , acNewRec
Me.StartDate = oStart
Me.EndDate = oEnd

More code here if you need it...

You might need to play with the starting or ending values to get the correct
week.

 

[EMonk]

On a form, I have two fields StartDate and EndDate.

I want a command button to ask the user if there is a report next week ornot.

"Is there a report due next week?"
If the answer is yes, then add 7 days to the StartDate and EndDate and have
those fields be in a new record.  So i want it to add a new record and add 7
days to both dates.

If the answer is no, then add 14 days, etc.

How do I do this?

Dates are stored as a floating point number of days (or part days for
time values) since a base value (30-Dec-2899, for historic reasons).
Adding 1 to a date value adds 1 day, adding 0.5 is 12 hours, etc. So
adding a week to a date value is as simple as adding 7... so long as
weeks remain 7 days

Assuming that your form is bound to a table, and that table has fields
like "StartDate" and "EndDate", and your week starts on a monday, then
this might work:

'''Start Code
Private Sub Command0_Click()
' start at beginning of next calendar week
Dim baseDate As Date
baseDate = Date + 8 - Weekday(Date, vbMonday)

' loop until user selects "yes"
Do While True
If vbYes = MsgBox("Is a report due for " & _
Format(baseDate, "ddd dd-mmm-yyyy") & _
"?", vbYesNo) Then

' goto new record.
DoCmd.GoToRecord , , acNewRec

' set StartDate and EndDate fields
Me!StartDate = baseDate
Me!EndDate = baseDate + 6

' break loop
Exit Do
End If

' add a week and try again
baseDate = baseDate + 7
Loop
End Sub
'''[End Code]

 

[Ben]

EMonk,
The code works great. HOWEVER, there is a minor glitch. Each time I click
on the command button, it asks me "Is a report due for 02-Mar-2009?" over and
over again. It won't ask me "Is a report due for (current dates)?"

Thanks

Ben

On a form, I have two fields StartDate and EndDate.

I want a command button to ask the user if there is a report next week or not.

"Is there a report due next week?"
If the answer is yes, then add 7 days to the StartDate and EndDate and have
those fields be in a new record. So i want it to add a new record and add 7
days to both dates.

If the answer is no, then add 14 days, etc.

How do I do this?

Dates are stored as a floating point number of days (or part days for
time values) since a base value (30-Dec-2899, for historic reasons).
Adding 1 to a date value adds 1 day, adding 0.5 is 12 hours, etc. So
adding a week to a date value is as simple as adding 7... so long as
weeks remain 7 days

Assuming that your form is bound to a table, and that table has fields
like "StartDate" and "EndDate", and your week starts on a monday, then
this might work:

'''Start Code
Private Sub Command0_Click()
' start at beginning of next calendar week
Dim baseDate As Date
baseDate = Date + 8 - Weekday(Date, vbMonday)

' loop until user selects "yes"
Do While True
If vbYes = MsgBox("Is a report due for " & _
Format(baseDate, "ddd dd-mmm-yyyy") & _
"?", vbYesNo) Then

' goto new record.
DoCmd.GoToRecord , , acNewRec

' set StartDate and EndDate fields
Me!StartDate = baseDate
Me!EndDate = baseDate + 6

' break loop
Exit Do
End If

' add a week and try again
baseDate = baseDate + 7
Loop
End Sub
'''[End Code]

 

[Ben]

PJ,
It almost works. When the code asks me if there is a report due next week,
and I say No, it should go to the next record and add 14 days. When I click
no..nothing happens.