Trouble with some simple programming

[Logi Bakels]

G'day all

I have a problem which I would appreciate any help with.

I have a table (called "tblUsageLog") with the following fields;-
- Autonumber (primary key)
- Date
- Company
- StartTime
- StopTime (or can be "Difference")
- Task

I have a form with 3 grouped command buttons (which has names of companies
on them) and a unbound text box.

What I would like to acheive is when the user clicks on a command button the
Date, StartTime, Button Name and task are entered into a record. When the
user clicks on another command button, the same information is recorded in
the next record but also the StopTime (which of course is the new StartTime)
or a time difference, is recorded in the previous record.

From a practical point of view I imagine that the user clicks on a command
button and then enters information into the text box (entry into box is
optional). When they click on another command button, the contents of the
text box is sent to the appropriate field.

So the table would look like;-
Auto Date Company StartTime StopTime Task
1 27/1/5 Coy 1 9:00 9:30
Words in Task text box
2 27/1/5 Coy 2 9:30 11:15 More
words in text box
3 27/1/5 Coy 1 11:15 15:00 Again
more words

or if it is better to use time difference;-
Auto Date Company StartTime Difference Task
1 27/1/5 Coy 1 9:00 0:30
Words in Task text box
2 27/1/5 Coy 2 9:30 1:45
More words in text box
3 27/1/5 Coy 1 11:15 3:45
Again more words

Also the code would have to include the ability to enter in either the
StopTime or last Difference time when the program closes down (for whatever
reason). This I haven't been able to work out.

The code I've come up with so far is;-

'######Start of Program#################
Private Sub Coy_Buttons_Click()

On Error GoTo Coy_Buttons_Click_Err

' Set the button choice

Function Choice(Coy_Buttons)
Select Case Coy_Buttons
Case 1
Choice = "Coy 1"
Case 2
Choice = "Coy 2"
Case 3
Choice = "Coy 3"
Case Else
Choice = "error"
End Select
End Function

'Put data into the table

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "data source=" & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "tblUsageLog", CurConn, , , adCmdTable
With rst
.AddNew
![Date] = Date
![Company] = Choice
![OpenDateTime] = Now()
![Task] = (i don't know what to put here)
.Update
End With
rst.Close

Coy_Buttons_Click_Exit:
Exit Function

Coy_Buttons_Click_Err:
MsgBox Err.Description
Resume Coy_Buttons_Click_Exit

End Function

'##### END of PROGRAMME ######

I have also noticed that when running this, it gives the following error at
the "Dim CurDB As Database" line -> "Compile error. User-defined type not
defined".

I am the first to admit that I'm not even good enough to be called a novice
so any help would be appreciated.

Thanks

LB

 

[Dirk Goldgar]

G'day all

I have a problem which I would appreciate any help with.

I have a table (called "tblUsageLog") with the following fields;-
- Autonumber (primary key)
- Date
- Company
- StartTime
- StopTime (or can be "Difference")
- Task

I have a form with 3 grouped command buttons (which has names of
companies on them) and a unbound text box.

What I would like to acheive is when the user clicks on a command
button the Date, StartTime, Button Name and task are entered into a
record. When the user clicks on another command button, the same
information is recorded in the next record but also the StopTime
(which of course is the new StartTime) or a time difference, is
recorded in the previous record.

From a practical point of view I imagine that the user clicks on a
command button and then enters information into the text box (entry
into box is optional). When they click on another command button, the
contents of the text box is sent to the appropriate field.

So the table would look like;-
Auto Date Company StartTime StopTime Task
1 27/1/5 Coy 1 9:00 9:30
Words in Task text box
2 27/1/5 Coy 2 9:30 11:15
More words in text box
3 27/1/5 Coy 1 11:15 15:00
Again more words

or if it is better to use time difference;-
Auto Date Company StartTime Difference Task
1 27/1/5 Coy 1 9:00 0:30
Words in Task text box
2 27/1/5 Coy 2 9:30 1:45
More words in text box
3 27/1/5 Coy 1 11:15 3:45
Again more words

Also the code would have to include the ability to enter in either the

StopTime or last Difference time when the program closes down (for
whatever reason). This I haven't been able to work out.

The code I've come up with so far is;-

'######Start of Program#################
Private Sub Coy_Buttons_Click()

On Error GoTo Coy_Buttons_Click_Err

' Set the button choice

Function Choice(Coy_Buttons)
Select Case Coy_Buttons
Case 1
Choice = "Coy 1"
Case 2
Choice = "Coy 2"
Case 3
Choice = "Coy 3"
Case Else
Choice = "error"
End Select
End Function

'Put data into the table

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "data source=" & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "tblUsageLog", CurConn, , , adCmdTable
With rst
.AddNew
![Date] = Date
![Company] = Choice
![OpenDateTime] = Now()
![Task] = (i don't know what to put here)
.Update
End With
rst.Close

Coy_Buttons_Click_Exit:
Exit Function

Coy_Buttons_Click_Err:
MsgBox Err.Description
Resume Coy_Buttons_Click_Exit

End Function

'##### END of PROGRAMME ######

I have also noticed that when running this, it gives the following
error at the "Dim CurDB As Database" line -> "Compile error.
User-defined type not defined".

I am the first to admit that I'm not even good enough to be called a
novice so any help would be appreciated.

Thanks

LB

I have a simple time-clock application I wrote for my employees and me,
that does something very like what you are trying to do. The user
interface consists basically of a "time clock" form with a continuous
subform displaying a project names, with a start button beside each
project name. The main form has a big stop button. Clicking the start
button beside a project, or double-clicking the project name, "starts
the clock" on that project, and "stops the clock" on whatever project
was previously active. The stop button just stops the clock.

Maybe these code snippets will give you enough to go on:

'----- code from a standard module -----
Function StopClock() As Boolean

' This function stops the clock on whatever project is
' currently active.

' Returns True if the clock was stopped, false if an error occurred.

On Error GoTo Err_StopClock

Dim strSQL As String

strSQL = _
"UPDATE tblClockData SET ClockStop = Now() " & _
"WHERE ClockStop Is Null"

CurrentDb.Execute strSQL, dbFailOnError

StopClock = True

On Error Resume Next
' Requery the time clock form, if it's open.
Forms!frmTimeClock!sfTimeClockProjects.Requery
' Close the "stop sign", if it's open.
DoCmd.Close acForm, "frmStopSign", acSaveNo

Exit_StopClock:
Exit Function

Err_StopClock:
MsgBox "Can't stop clock:" & vbCr & vbCr & Err.Description, _
vbExclamation, "Error " & Err.Number
StopClock = False
Resume Exit_StopClock

End Function
'----- end code from standard module -----


'----- code from subform "sfTimeClockProjects" -----
Private Sub cmdStart_Click()

On Error GoTo Err_cmdStart_Click

Dim strSQL As String
Dim lngProjectID As Long

If Me.OnTheClock <> 0 Then
Beep
Exit Sub
End If

If Not StopClock() Then
' An error occurred when stopping the clock.
If MsgBox( _
"Do you want to start the clock anyway?", _
vbQuestion + vbYesNo + vbDefaultButton2, _
"Start Anyway?") _
= vbNo _
Then
Exit Sub
End If
End If

strSQL = _
"INSERT INTO tblClockData " & _
"(ProjectID, ClockStart, ClockStop) " & _
"Values(" & Me.ProjectID & ", Now(), Null)"

CurrentDb.Execute strSQL, dbFailOnError

lngProjectID = Me.ProjectID
Me.Requery
Me.Recordset.FindFirst "ProjectID=" & lngProjectID

DoCmd.Minimize

If Nz(DLookup("ShowStopForm", "tblOptions"), False) = True Then
DoCmd.OpenForm "frmStopSign"
End If

Exit_cmdStart_Click:
Exit Sub

Err_cmdStart_Click:
MsgBox "Can't start clock:" & vbCr & vbCr & Err.Description, _
vbExclamation, "Error " & Err.Number
Resume Exit_cmdStart_Click

End Sub


Private Sub ListName_DblClick(Cancel As Integer)

Me.cmdStart.SetFocus
cmdStart_Click

End Sub
'----- end code from "sfTimeClockProjects" -----