Re: Trouble with some simple programming

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 01/28/05 

Date: Fri, 28 Jan 2005 01:07:26 -0500

"Logi Bakels" <LogiBakels@discussions.microsoft.com> wrote in message
news:EB755C2F-1BAB-4678-9775-17DD96627A35@microsoft.com
> 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" ----- 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)