Good morning! Here is the code....
Private Sub btnUpdateWeekOfTable_Click()
On Error GoTo Err_btnUpdateWeekOfTable_Click
If Me![bxCurrentWeekEndedDate].Value = DMax("[Week Ended]",
"tblWeekOfTable") Then
MsgBox "Procede to calculations!", vbOKOnly + vbExclamation
Else
Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Dim datWeekEnded As Date
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate, [Week
Ended]) " & "VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing
MsgBox "Update complete!", vbOKOnly + vbExclamation
End If
Exit_btnUpdateWeekOfTable_Click:
Exit Sub
Err_btnUpdateWeekOfTable_Click:
MsgBox Err.Description
Resume Exit_btnUpdateWeekOfTable_Click
End Sub
Thanks, again!
Ken Snell said:
Nothing appears to be incorrect in the code nor what you have for
settings/formats. So I am at a loss for how to explain what you are
seeing
as the resulting records' values for the inserted records after the code
runs.
Can you post the entire procedure that you used (starting with "Private
Sub
...." through the "End Sub" lines)?
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
bxCurrentWeekEndedDate gets its value when a date is typed into the
box,
which is formatted as Short Date on the text box properties. The value
entered in that box prior to running the code was 04/17/2005 (which, my
desired results are to add 04/11/2005, 04/12/2005.....04/17/2005 -- 7
new
records, since the latest date in the PolicyEffectiveDate field is
04/10/2005). The fields PolicyEffectiveDate and Week Ended are both
formatted as date/time in the data type.
:
Declaring a variable as Date means it will hold date and time data.
Dates
and times are stored as a floating point number, where the "integer"
portion
is the number of days since December 30, 1899, and the "decimal"
portion
is
the percentage of a 24-hour day represented by the time.
Thus, 4/15/2005 1:28:07 PM is the number 38457.5611921296 --- this
number
is what is actually stored in the variable.
Your results puzzle me, though, because the DateAdd function will not
increment the time when you use "d" as the first argument and a
negative
number as the second argument (note my use of -lngLoop there). From
where
or
how does bxCurrentWeekEndedDate get its value? What was the value that
it
supposedly contained when you ran the code? If bxCurrentWeekEndedDate
is
a
textbox, what is the Format property for it in the form's design view?
Also, what is the format setting for the two fields in the table's
design
view?
--
Ken Snell
<MS ACCESS MVP>
message
My appologies. VBA is definetly a foreign language to me, but you
all
have
been very helpful in my education! This worked, after I added Dim
datWeekEnded As Date
to it as I got "Variable not defined" (I'm proud of myself for
figuring
that
one out!). After clicking the button, 7 new records were added as
follows:
In PolicyEffectiveDate 12:00:10 AM and the seconds incremented by
one
each
time up to 12:00:16 AM, and in Week Ended 12:00:10 AM for each of
the 7
records. Why did it give time and not date, since I Dimed As Date?
:
A completely different thing than what I thought you wanted to do.
To do this, you'll need to create an append query's SQL statement
("INSERT
INTO ...") for inserting a single record and then loop through that
part
of
the code 7 times.
Assuming that you have a variable named datWeekEnded that holds the
"new"
final value for WeekEnded field:
Dim lngLoop As Long
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
datWeekEnded = DateValue([bxCurrentWeekEndedDate])
For lngLoop = 0 To 6
strSQL = "INSERT INTO [tblWeekOfTable] (PolicyEffectiveDate,
[Week
Ended]) " & _
"VALUES (" & DateAdd("d", -lngLoop, datWeekEnded) & ", " &
datWeekEnded & ");"
dbs.Execute strSQL, dbFailOnError
Next lngLoop
dbs.Close
Set dbs = Nothing
