Results 1 to 9 of 9
  1. #1
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161

    Error:User-Define type not defined

    I have two fuction


    First with code
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Through First Fuction I an Call the Second Fuction
    Second Fuction Have Code
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    I am getting Error as
    Compile error
    User-Define type not defined
    Both Fuction work fine independtly on two different files.
    Can anyone quide what is wrong in this code
    Thanks in advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us all the code.

  3. #3
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    And just for your information (as I am guessing that English is not your native language) - it is Function with an N and not fuction.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I think you just need to make sure that you have both the ADO reference and DAO reference set. If you are using the newer ACCDB file format you would need to have Microsoft Office 12.0 Access Engine Object Library selected in the TOOLS > REFERENCES (in the VBA Window).

  5. #5
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    Thanks for Quick Responce,
    I am using accdb file in Office-2007 Version.
    I had ckecked Microsoft Office 12.0 Access Engine Object Library is in VBA selected.
    It looks like the version issue.
    Dim db As DAO.Database
    Dim rs As DAO.Recordset Code works in accdb file.

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset Code Works in mdb file.
    But Both do not work in one file.

    My First Function Codes are

    Function Send_EMail_Form()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strField1 As String
    Dim intField3 As Date
    On Error GoTo Error_Handle
    Set db = CurrentDb
    strSQL = "Select * From [Renewal_Reminder_Query]"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    With rs
    'This Do While loop goes through all the records in strSQL.
    Do While Not rs.EOF
    strField1 = rs![E-Mail_ID]
    intField3 = rs![Date_of_Renewal]
    Dim strRecipient, strBody, strSubject, astAttach(2) As String
    Dim i, intCount, intSend As Integer
    Dim oLook, oMail As Object
    strRecipient = rs![E-Mail_ID]
    strSubject = Forms![E-Mail_Form]![Subject].Value
    strBody = Forms![E-Mail_Form]![Massage].Value
    intSend = True
    intCount = 2
    astAttach(0) = "D:\Data.doc"
    astAttach(1) = "D:\Data.xls"
    'On Error GoTo errEmailAttach
    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.CreateItem(0)
    With oMail
    .To = strRecipient
    .Body = strBody
    .Subject = strSubject
    .ReadReceiptRequested = True 'You can set this to False if you want.
    If intCount <> 0 Then
    For i = 1 To intCount
    .Attachments.Add (astAttach(i - 1))
    Next
    End If
    If intSend = True Then
    .Send
    Else
    .Display
    End If
    End With
    Set oMail = Nothing
    Set oLook = Nothing
    'errEmailAttach:
    'MsgBox "Error Message: " & Err.Description & Chr$(10) & Chr$(13) & _
    '"Your email may not have been sent.", vbCritical, "Error"
    On Error Resume Next
    Set oMail = Nothing
    Set oLook = Nothing
    MsgBox "The Email is Sucessfuly send to " & strField1
    MsgBox "The Date Of Renewal is due on " & intField3
    Call LogEMailSend(strRecipient, strSubject, strBody)
    On Error Resume Next
    .MoveNext
    Loop
    Exit_Send_EMail:
    If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
    Error_Handle:
    Resume Exit_Send_EMail
    End With
    End Function

    I am Calling Second Function LogEMailSend From First Function

    Function LogEMailSend(EMail_ID As Variant, Subject As Variant, Massage As Variant)
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from EMailLog"
    rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rs.AddNew
    rs!EMail_ID = EMail_ID
    rs!Subject = Subject
    rs!Massage = Massage
    rs!SendBy = getUser()
    rs!SendOn = Now()
    rs.Update
    rs.Close
    Set rs = Nothing
    End Function


    I am getting Error as in Second Fuction at place
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Compile error
    User-Define type not defined.
    Will be you quide what is wrong?
    Thanks

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I wish I had Access 2007 here at work so I could check it out, but ADO and DAO DO work in the same file. The problem would be, since you are getting the error on the ADO part, that you need to make sure that a check is in the ADO reference (in the VBA Window under TOOLS > REFERENCES) and since this is a 2007 file type, you can use the

    Microsoft ActiveX Data Objects 2.7 Object Library

  7. #7
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    I had checked Microsoft ActiveX Data Objects 2.7 Object Library in and
    Thanks it has solve the issue in accdb file.

    In mdb file I need to apply one more check at
    Micosoft Office 14 Access Database Engine Object
    That will Solve the issue in mdb file type.

    Just for your information
    in 2007 Version
    Micosoft Office 14 Access Database Engine Object get automatically converted to
    Micosoft Office 12 Access Database Engine Object
    Which make some code non fuctional in 2007 version which work Fine in 2010 version.
    But not a big issue I have all the version 2003,2007,2010 in same machine and i swap between them at will.
    Thanks for timely help.
    The issue is solved.

  8. #8
    nicknameoscar is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2011
    Location
    Earlysville, VA
    Posts
    91
    I use Access 2007 at work and Access 2000 at home. I was using both ADO and DAO in my database. Every once in a while Access 2007 would throw an error (I think it was the user defined type error but I am not sure). I finally figured out what the problem was. If I moved the ADO reference above the DAO reference, closed and reopened the database then no problems. If I then returned the DAO reference to being above the ADO reference , closed and reopened the database - still no problems. I would experience no further problems for a few weeks as best as I can remember and then it would pop up again. I finally said to hell with it and converted everything to DAO and unchecked the ADO reference. No problems at all since then. Just thought I would mention my experience in case anyone else comes across this peculiarity.

  9. #9
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    I am also facing the same problem in 2007 Version
    Micosoft Office 14 Access Database Engine Object get automatically converted to
    Micosoft Office 12 Access Database Engine Object. I had checked that.
    Which make ADO non fuctional in 2007 dut to Micosoft Office 12 Access Database Engine Object does not read ADO.
    ADO work Fine in 2010 version due to Micosoft Office 14 Access Database Engine Object.
    Thanks for information.
    I had puzzled a lot to understand why this is happening.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Application-defined or object-defined error
    By hawkins in forum Access
    Replies: 6
    Last Post: 07-01-2011, 01:57 PM
  2. User Defined Sorting in Form
    By sparlaman in forum Forms
    Replies: 6
    Last Post: 04-26-2011, 12:02 PM
  3. Where to start: user defined reports
    By noweyout in forum Reports
    Replies: 2
    Last Post: 04-22-2011, 01:23 PM
  4. Replies: 6
    Last Post: 07-22-2010, 05:53 PM
  5. Error: "User-defined type not defined"
    By mastromb in forum Programming
    Replies: 10
    Last Post: 01-08-2010, 02:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums