Add days to dates and add new record

  • Thread starter Thread starter Ben
  • Start date Start date
B

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?
 
Ben said:
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.
Click to expand...

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?
 
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
 
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.
 
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?
Click to expand...

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]
 
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

EMonk said:
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?
Click to expand...

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]
Click to expand...
 
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.
 
Back
Top