Exporting to Access

[Mike_CS]

Hi All

I have created a userform to try and capture some MI it has labels, text and combo boxes.

When I press the button to upload the completed form I get the following message:

Complile error: User-defined type not defined

Have tried different ways to define things but drawn an absolute blank. I copied this from another site and I think this was only a part of the code as I'm not sure what else needs to be defined.

Can anybody help with this? I will explain what I'm trying to do below and underneath is the code. I haven't used the actual database file location.

Label13 is the staff number of someone and needs to go in to the allocatedby field in the database
Textbox1 is the name of the person requesting a job and goes in requestedby field
textbox3 is the date request received and goes in daterequestreceived field
combobox3 is a list of job types and goes in requesttype field
textbox2 is a description of the request and goes in to requestdetails field
textbox4 is the date of when request needs to be done by and goes in requesteddate field
combobox2 is a list of staff members and goes in the allocatedto field

Public Function InsertRecord() As Boolean
Dim SaveTime As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmployee As String ' - never did anything
Dim FrmCreateRequest As Object '  - never did anything
Dim Label13 As Object ' - never did anything
Dim value As String ' - never did anything

''//Variables used to hold field values
Dim strEmployee As FrmCreateRequest.Label13.value
Dim dtmRequestDate As FrmCreateRequest.TextBox3.value
Dim lngRequestedby As FrmCreateRequest.TextBox1.value
Dim lngRequesttype As FrmCreateRequest.ComboBox3.value
Dim dtmRequestedBy As FrmCreateRequest.TextBox4.value
Dim lngRequestDetails As FrmCreateRequest.TextBox2.value
Dim strAllocatedTo As FrmCreateRequest.ComboBox2.value

'Original 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 = "my database file location"

    '//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 = FrmCreateRequest.Label13.value
    dtmRequestDate = FrmCreateRequest.TextBox3.value
    lngRequestedby = FrmCreateRequest.TextBox1.value
    lngRequesttype = FrmCreateRequest.ComboBox3.value
    dtmRequestedBy = FrmCreateRequest.TextBox4.value
    lngRequestDetails = FrmCreateRequest.TextBox2.value
    dtmRequestedBy = FrmCreateRequest.ComboBox2.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("DataCapture", dbOpenDynaset)
    End If
    
    '//Create a new record
    With rs
        .AddNew
        ![AllocatedBy] = strEmployee
        ![DateRequestReceived] = dtmRequestDate
        ![RequestedBy] = lngRequestedby
        ![RequestDetails] = lngRequesttype
        ![RequestedDate] = dtmRequestedBy
        ![AllocatedTo] = dtmRequestedBy
        ![DateAllocated] = SaveTime

        '//Insert Record into Database
        .Update
        InsertRecord = 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 Function

 

[anvg]

Re: Exporting to Access - first timer

Hi
Do you set a reference on Microsoft DAO 3.x Object Library in your VBA project?
Regards,

 

[Mike_CS]

Re: Exporting to Access - first timer

Hi
Do you set a reference on Microsoft DAO 3.x Object Library in your VBA project?
Regards,

Hi sorry not really sure what that is so probably not. It is ticked in the tools-> references box.

Have never done this type of thing at all.

I'm thinking I may just keep it within the same worksheet using the form to populate a table but then still would have to find out how to amend existing entries.

I was always told that Access was more stable for this kind of thing.