Now Function is not assigning a date and showing wrong time.

[balla506]

I am trying to integrate into my form a audit table showing how long a person takes to work a record. I get a missing operator error on the insert into statements. When I debug it the StartTime is showing as 12 AM and has no date associated with it. Any ideas why this is not being assigned with the StartTime = Now()? Thanks.






Option Compare DatabasePublic StartTime As Date
Public EndTime As Date






Private Sub Form_Load()


Dim User As String
Dim MyLogIn As String
Dim Task As String
Dim StartTime As Date
Dim EndTime As Date




User = Environ("UserName")


MyLogIn = DLookup("Username", "tblUser", "UserLogin = '" & User & "'")


Task = "Select * from tblAff_Needing_Review Where [QA/QC Analyst] = '" & MyLogIn & "'"




Me.RecordSource = Task




StartTime = Now()










End Sub
Private Sub NextRecord_Click()


If Me.CurrentRecord < Me.Recordset.RecordCount Then
EndTime = Now()
DoCmd.RunSQL "INSERT INTO Aff_Audit (RecordNumber,StartTime,EndTime )VALUES (" & Me.ID & ", " & StartTime & ", " & EndTime & ");"
DoCmd.GoToRecord , , acNext
StartTime = Now()
End If
End Sub






Private Sub Form_Close()
EndTime = Now()
DoCmd.RunSQL "INSERT INTO Aff_Audit (RecordNumber,StartTime,EndTime )VALUES (" & Me.ID & ", " & StartTime & ", " & EndTime & ");"
End Sub

[balla506]

I managed to figure it out. I was redimming the two dates which reset them. I am running into a new issue though where the insert on the form_close is not showing the right ID number but instead going back to record 1. Any ideas on this?

[OBP]

I am not sure why you are using insert into queries rather than work directly with the Form recordset or the table using VBA.
It would appear that for some reason the insert into query is re-querying the form.