Tab Control and Recording Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

G'day all

Could anyone please help me with the following problem as I'm not very good
with VBA.

What I would like is that everytime the user clicks on a particular tab on a
form, I want the computer to record the tab name as well as the time and date
(using the computers time/date) it was clicked.

When the user selects another tab, I want the computer to record the above
information but also the time difference between the two selections.

For example the table should look like this;-
TabName Date Time Difference
Tab 1 25/01/05 13:30pm 00:00
Tab 4 25/01/05 14:15pm 00:45
Tab 2 25/01/05 14:25pm 00:10
Tab 6 25/01/05 17:00pm 02:35

And if there is no "Time Difference" (as in Tab 1), then "00:00" is put in
by default.

Thanks for your help

LB
 
What I would like is that everytime the user clicks on a particular
tab on a form, I want the computer to record the tab name as well as
the time and date (using the computers time/date) it was clicked.

When the user selects another tab, I want the computer to record the
above information but also the time difference between the two
selections.
Click to expand...

' can't remember the best event to use, don't think there
' is an AfterUpdate on a tab control?

private sub tabMyTab_Change()

static s_dtLastChange as Date ' remember last change
dim dtNow as Date ' remember this one

dim strSQL as string
dim dwMinutes as long

' remember the correct time
dtNow = Now()

' set up the INSERT statement
strSQL = "INSERT INTO AuditTable (TabName, DateAndTime, Difference)"

' I think this is the right way to get the tab name
strSQL = strSQL & vbNewLine & "VALUES ( """ MyTab.Value & """, "

' Note the correct way to pass a date to Jet
strSQL = strSQL & Format(dtNow, "\#yyyy\-mm\-dd\#") & ", "

' How to calculate the last value
if CInt(s_dtLastChange) = 0 Then ' first time round
strSQL = strSQL & "NULL"

else
dwMinutes = DateDiff("n", s_dtLastChange, dtNow)
strSQL = strSQL & """" & Format(dwMinutes Div 60,"00") & ":" & _
Format(dwMinutes Mod 60, "00") & """"

end if

' close the command: the semicolon is not strictly necessary but
' is good form
strSQL = strSQL & ");"


' don't forget this line: it'll save hours in debugging
' ... and don't forget to remove it after!
MsgBox strSQL,,"Debugging"

' now run the thing
On Error Resume Next
CurrentDB().Execute strSQL, dbFailOnError
If Err.Number <> 0 Then
' command failed

Else
' hurray

End If

' and reset the timestamp for the next time round
s_dtLastChange = dtNow

End Sub



As ever, this is untested code, so treat with great caution!

Hope it helps


Tim F
 
Back
Top