opend_db.vba

Public Function InsertMachineHoursRecord() As Boolean

Dim SaveTime As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset

'//Variables used to hold field values
Dim strEmployee = Me.cboEmployee.Value
Dim dtmEventDate = Me.txtDate.Value
Dim lngEventTypeID = Me.cboType.Value
Dim dtmStartTime = me.txtStartTime.Value
Dim dtmEndTime = me.txtEndTime.Value
Dim strMachineNumber = Me.txtMachine.Value
Dim strDescription = Me.txtDescription.Value

'//Database Location
Const DB_LOCATION = "C:\SomeFolder\SomeDatabase.mdb"

    '//If errors occur the function will exit with a return value of false (insertion failed)
    On Error GoTo ErrHandler:

    '//Get Field Values from Form and store in variables
    strEmployee = Me.cboEmployee.Value
    dtmEventDate = Me.txtDate.Value
    lngEventTypeID = Me.cboType.Value
    dtmStartTime = Me.txtStartTime.Value
    dtmEndTime = Me.txtEndTime.Value
    strMachineNumber = Me.txtMachine.Value
    strDescription = Me.txtDescription.Value

    '//Recommended: Validate all values here before inserting into DB
    '//   -- Check that data is of right type and meets business rules
    
    '//Table has a datecreated/datemodified timestamp for each record
    SaveTime = Now
    
    '//Open Database
    If db Is Nothing Then
       Set db = DAO.Workspaces(0).OpenDatabase(DB_LOCATION)
    End If

    '//Open Table
    If rs Is Nothing Then
        Set rs = db.OpenRecordset("MachineRepairHours", dbOpenDynaset)
    End If
    
    '//Create a new record
    With rs
        .AddNew
        ![Employee] = strEmployee
        ![EventDate] = dtmEventDate
        ![EventTypeID] = lngEventTypeID
        ![StartTime] = dtmStartTime
        ![StopTime] = dtmStopTime
        ![HoursWorked] = ((dtmStopTime - dtmStartTime) * 24)
        ![MachineNumber] = strMachineNumber
        ![DateModified] = SaveTime
        ![Description] = strDescription
        '//Insert Record into Database
        .Update
        InsertMachineHoursRecord = True '//SUCCESSFUL INSERTION
    End With

    '//Note that we use recordset in this example, but equally effective
    '//  is to create an update query command text and simply run the update query:
    '//  (INSERT INTO Table (Field1, Field2) VALUES (Value1, Value2);
    
'//Make sure we have closed the database
My_Exit:
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function

ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub