Changing Many Records

[DS]

I need to change may records with the same date. This code works for
one record but I need to change all of the Time In fields for all
records with one Date. Here is the code...

Dim dteTimeIn As Date
Dim dteRoundedTime As Date
Dim ONESQL As String
dteTimeIn = Forms!frmTimeCardAdjust!TxtTimeIn
dteRoundedTime = RoundTime(dteTimeIn)
DoCmd.SetWarnings False
ONESQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" &
dteRoundedTime & "# " & _
"WHERE tblTimeLog.LogDateIN = " & Forms!frmTimeCardAdjust!TxtDate1 & ";"
DoCmd.RunSQL (ONESQL)
DoCmd.SetWarnings True

Forms!frmTimeCardAdjust!ListEdit.Requery
DoCmd.Close acForm, "frmTimeAdjustSelect"

Any hel appreciated,
Thanks
DS

 

[Arvin Meyer [MVP]]

It should do all the records with the date set in the TxtDate1 textbox. If
you want all the records regardless of what the LogDateIN is, just remove
the where clause:

"UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" & dteRoundedTime & "# "

 

[Tom Wickerath]

Hi DS,

Just create the appropriate Update query. Here is a link to information on
creating various action queries. Update queries are covered in the last link
on this page:

http://office.microsoft.com/en-us/access/CH063653171033.aspx

Do you really need to use VBA code to accomplish this goal? If so, I
recommend using the .execute method instead of DoCmd.SetWarnings. See this
previous post for an example:

http://www.microsoft.com/office/com...ding&mid=34220444-0097-49c1-8dba-ce8e8aa46d58

If you are really determined to use DoCmd.SetWarnings, then, as a minimum,
you should make sure to use proper error handling code that turns warnings
back on, in the event that your action query fails for any reason.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[DS]

It should do all the records with the date set in the TxtDate1 textbox. If
you want all the records regardless of what the LogDateIN is, just remove
the where clause:

"UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" & dteRoundedTime & "# "

Didn't work.

Here is what I have.

Private Sub CommandAll_Click()
Dim dteTimeIn As Date
Dim dteRoundedTime As Date
Dim ALLSQL As String
dteTimeIn = Forms!frmTimeCardAdjust!TxtTimeIN
dteRoundedTime = RoundTime2(dteTimeIn)
DoCmd.SetWarnings False
ALLSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" &
dteRoundedTime & "# " & _
"WHERE tblTimeLog.LogDateIN = " & Forms!frmTimeCardAdjust!TxtDate1 & ";"
DoCmd.RunSQL (ALLSQL)
DoCmd.SetWarnings True

Forms!frmTimeCardAdjust!ListEdit.Requery
DoCmd.Close acForm, "frmTimeAdjustSelect"
End Sub

Function RoundTime2(TxtTimeIN As Date) As Date
Select Case Minute(Forms!frmTimeCardAdjust!TxtTimeIN)
Case 0 To 15
RoundTime2 = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":15")
Case 16 To 30
RoundTime2 = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":30")
Case 31 To 45
RoundTime2 = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":45")
Case Else
RoundTime2 = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) + 1 & ":00")
End Select
End Function

I think I need Recordset and then A FOR NEXT thing to loop, but how is
that done?
Thanks
DS

 

[Douglas J. Steele]

Dates need to be delimited with # as well:

ALLSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" & _
dteRoundedTime & "# " & _
"WHERE tblTimeLog.LogDateIN = #" & _
Forms!frmTimeCardAdjust!TxtDate1 & "#"

Actually, since Access prefers to work with dates in mm/dd/yyyy (although it
will accept any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy),
you'd be better to use:

ALLSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" & _
dteRoundedTime & "# " & _
"WHERE tblTimeLog.LogDateIN =" & _
Format(Forms!frmTimeCardAdjust!TxtDate1, "\#mm\/dd\/yyyy\#")

 

[DS]

Dates need to be delimited with # as well:

ALLSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" & _
dteRoundedTime & "# " & _
"WHERE tblTimeLog.LogDateIN = #" & _
Forms!frmTimeCardAdjust!TxtDate1 & "#"

Actually, since Access prefers to work with dates in mm/dd/yyyy (although it
will accept any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy),
you'd be better to use:

ALLSQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" & _
dteRoundedTime & "# " & _
"WHERE tblTimeLog.LogDateIN =" & _
Format(Forms!frmTimeCardAdjust!TxtDate1, "\#mm\/dd\/yyyy\#")

Thanks Douglas but I still have the problem of changing the TimeIn field
for may records where the Date is the same, Is the Recordset, For Next
the way to go? If so then how.
Thanks
DS

 

[Douglas J. Steele]

Thanks Douglas but I still have the problem of changing the TimeIn field
for may records where the Date is the same, Is the Recordset, For Next the
way to go? If so then how.

What sort of problem? "Didn't work" doesn't give us much to go by.

Looping through a recordset, setting the values one by one is almost never
the correct answer!

 

[DS]

What sort of problem? "Didn't work" doesn't give us much to go by.

Looping through a recordset, setting the values one by one is almost never
the correct answer!

Didn't work means the records didn't update.
Thanks
DS

 

[Douglas J. Steele]

Didn't work means the records didn't update.

You sure the value of dteRoundedTime is correct?

Print out the value of ALLSQL to see what it's supposed to be doing. Try
pasting that SQL into the query manager and running it. Do you get any
feedback?