Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Newbie
    Joined
    Jun 2013
    Posts
    3
    Location

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

    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

  2. #2
    VBAX Newbie
    Joined
    Jun 2013
    Posts
    3
    Location
    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?

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •