Create Time stamp using vba 1

[vba317]

I have created a table to time stamp how long it takes to process my work. The name of the table is tblTime. I am getting a syntax error in my INSERT INTO statement. What I want to do in insert the current time using the Now() command. Into the tblTime in the RptProcStart field with the clientID and clientName. The fields in the tblTime are:

ClientID
ClientName
RptProcStart
RptProcEnd

If I get this code to work, I will have to write another query to put anither time stamp when the procedure ends that would go into the RptProcEnd Field. Any help is greatly appreciated.

Tom

 ' Get List of UCI to cycle through
    strSQL = "SELECT u.clntid,u.uci " & _
                "FROM [_UCI_Select] u " & _
                "GROUP BY u.clntid,u.uci " & _
                "ORDER BY u.uci;"
    Set rstUCI = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rstUCI.EOF Then ' Make sure not empty set
        With rstUCI
            .MoveLast
            .MoveFirst
        End With
        For iZ = 1 To rstUCI.RecordCount
            ' Get ClntID UCI Variables
            liClntID = (rstUCI![clntid])
            strUCI = (rstUCI![uci])
            strCompany = Trim(GetCompany(liClntID))
            strFilePath = "\\salmfilesvr1\Public\Client Services\AutoRpts\_Rpts\" & (strCompany) & "\" & (strUCI) & "\"
            strTemplatePath = "\\salmfilesvr1\Public\Client Services\AutoRpts\Templates\"
            
            'Start Time Count
[Blue]            strSQL = "Insert INTO tblTime (strUCI, strCompany, RptProcStart=Now() " & _
            " FROM ((tblTime);"
             Debug.Print strSQL
            CurrentDb.Execute strSQL [/Blue]
           

[\code]

 

[dhookom]

Something is wonky with your code. You strSQL has four "(" and two ")". I also don't understand why your FROM clause has a table name but no field names.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

how about

strtime = Now()

strSQL = "INSERT INTO tblTime ( ClientID, ClientName, RptProcStart ) " _
       & "SELECT " & strUCI & ", " & strCompany & ", " & strtime & ";"

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[vba317]

This must be close, but I am getting an error 3075 syntax error(Missing expression) in query expression '4/2/2013 6:48 PM'

 

[dhookom]

I'm not sure how you can state your error without providing us with the result of the debug.print. Do you understand faq705-7148?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

strSQL = "INSERT INTO tblTime (ClientID,ClientName,RptProcStart)" _
 & " VALUES (" & strUCI & ",'" & strCompany & "',Now())"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV, your code did work. It inserted the time stamp into the file. Here is what the code produced.
ID ClientID ClientName RptProcStart RptProcEnd RptProcTot PrtSetStart PrtSetEnd PrtSetTot
1 63 TAL 4/3/2013 12:37:00 PM

So now when the procedure is over how would I update this record in the RptProcEnd field using the same record?

 

[Andrzejek]

strSQL = "UPDATE tblTime SET RptProcEnd = Now() WHERE ClientID = " & strUCI

Have fun.

---- Andy

 

[vba317]

I get a Runtime error 3061. Too few parameters. Expected 1

strSQL = "UPDATE tblTime SET RptProcEnd =Now()" & _
        "WHERE RptProcEnd Is Null and ClientName = " & strUCI

when run my strSQL statement reads
UPDATE tblTime SET RptProcEnd =Now()WHERE RptProcEnd Is Null and ClientName = AMA

 

[PHV]

" WHERE RptProcEnd Is Null and ClientName='" & strUCI & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

The current code does update the record the way I wanted it to. But now that I have been using it a situation happened that I didnt think of. If I have to run the reports for a client a second or third time, the time stamp updates all records instead of the latest record. I have looked at the Dmax function but I could only find examples with a select statement not an update statement. Is there a way to use the dmax function in access? I tried with the following code but I get an error 2428 invalid argument in a domain aggregate function. The Id field is an autonumber field.

current code that works but updates all records.

 strSQL = "UPDATE tblTime SET PrtSetEnd =Now() " & _
          " WHERE ClientName='" & (rstPQ![UCI]) & "'"
            CurrentDb.Execute strSQL

code that gives an error 2428

  strSQL = "UPDATE tblTime SET PrtSetEnd =Now() " & _
           " WHERE ClientName='" & (rstPQ![UCI]) And DMax(ID, ClientName) & "'"

 

[PHV]

What about this ?

strSQL = "UPDATE tblTime SET PrtSetEnd=Now() " & _
         " WHERE ID=(SELECT Max(ID) FROM tblTime " & _
         " WHERE ClientName='" & rstPQ!UCI & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886