Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37

    Question Unanswered: User-Defined Error...Need Help!

    When switching from Design View to Normal (Form) View I get an error message...

    "The expression On Click you entered as the event property setting produced the following error: User-defined Type not defined."

    Here's the VBA I used from another site in trying to set up moving items between listboxes. Any idea why I'm getting this error. The link where the code can be found is...

    MS Office Gurus | Access 2007 move items between listboxes, filter listbox and clear listbox Items

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Can you please post the code you use in the database and if possible, indicate the line that causes the error. Seeing the code on the site you mention, the first thing that comes to my mind is that your database needs a reference to the ADODB library.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37

    Heres the code I used in my DB

    Option Compare Database
    Option Explicit

    '************************************************* ************************************************** ******************
    'CONSTANTS
    Private Const mstr_MsgBoxTitle As String = "Meu projeto Access 2007"
    Private Const mstr_MsgNoItemToMove As String = "Não há item para mover ou item não foi selecionado..."
    Private Const mstr_Yes As String = "Yes"
    Private Const mstr_No As String = "No"
    Private Const mstr_Filtered As String = "Filtered"

    '************************************************* ************************************************** ******************
    'STRINGS
    Private mstr_SQLInstruction As String

    '************************************************* ************************************************** ******************
    'OBJECTS
    Private mobj_ADODBRecordset As ADODB.Recordset

    Sub ExecuteCommand(ByVal strExecuteSQL, ByVal strShowYesNoFilter As String)

    Set mobj_ADODBRecordset = New ADODB.Recordset

    With mobj_ADODBRecordset
    .Open strExecuteSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If Not .BOF Then .MoveFirst

    Do While Not .EOF
    .Fields("ShowYesNoFilter").Value = strShowYesNoFilter
    .Update
    .MoveNext
    Loop
    End With

    Me.ListYesItems.Requery
    Me.ListNoItems.Requery

    mstr_SQLInstruction = ""
    Set mobj_ADODBRecordset = Nothing


    End Sub


    Private Sub cmdAddAll_Click()
    mstr_SQLInstruction = "SELECT Tbl_Skills.KeyID, Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.ShowYesNoFilter)='" & mstr_Yes & "'));"

    ExecuteCommand mstr_SQLInstruction, "No"

    End Sub

    Private Sub cmdAddOne_Click()
    Dim strSelectedItem As String
    Dim lngSelectedItemIndex As Long

    On Error Resume Next
    If Me.ListYesItems.ListIndex = -1 Then
    MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
    Exit Sub
    End If

    lngSelectedItemIndex = Me.ListYesItems.ListIndex
    strSelectedItem = Me.ListYesItems.ItemData(lngSelectedItemIndex)

    If Len(Me.ListYesItems.ItemData(lngSelectedItemIndex) ) < 1 Then
    MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
    Exit Sub
    End If


    mstr_SQLInstruction = "SELECT Tbl_Skills.KeyID, Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.Skills)='" & strSelectedItem & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_No

    End Sub

    Private Sub cmdRemoveAll_Click()
    mstr_SQLInstruction = "SELECT Tbl_Skills.KeyID, Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.ShowYesNoFilter)='" & mstr_No & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Yes

    End Sub

    Private Sub cmdRemoveOne_Click()
    Dim strSelectedItem As String
    Dim lngSelectedItemIndex As Long

    'On Error Resume Next

    If Me.ListNoItems.ListIndex = -1 Then
    MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
    Exit Sub
    End If

    lngSelectedItemIndex = Me.ListNoItems.ListIndex
    strSelectedItem = Me.ListNoItems.ItemData(lngSelectedItemIndex)

    If Len(Me.ListNoItems.ItemData(lngSelectedItemIndex)) < 1 Then
    MsgBox mstr_MsgNoItemToMove, vbInformation, mstr_MsgBoxTitle
    Exit Sub
    End If

    mstr_SQLInstruction = "SELECT Tbl_Skills.KeyID, Tbl_Skills.Skills, tblNames.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.Skills)='" & strSelectedItem & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Yes
    End Sub

    Private Sub Form_Current()

    End Sub

    Private Sub Form_Open(Cancel As Integer)

    mstr_SQLInstruction = "SELECT Tbl_Skills.KeyID, Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter, "
    mstr_SQLInstruction = mstr_SQLInstruction & "Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.ShowYesNoFilter)='" & mstr_No & "')) "
    mstr_SQLInstruction = mstr_SQLInstruction & "OR (((Tbl_Skills.ShowYesNoFilter)='" & mstr_Filtered & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Yes

    End Sub

    Private Sub ListNoItems_DblClick(Cancel As Integer)
    cmdRemoveOne_Click
    End Sub

    Private Sub ListYesItems_DblClick(Cancel As Integer)
    cmdAddOne_Click
    End Sub

    Private Sub txtFilter_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim strFilter As String

    strFilter = Me.txtFilter.Text

    Select Case KeyCode
    Case 8, 46

    mstr_SQLInstruction = "SELECT Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.Skills) Not Like '%" & strFilter & "%') "
    mstr_SQLInstruction = mstr_SQLInstruction & "Or ((Tbl_Skills.ShowYesNoFilter) = '" & mstr_Filtered & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Yes


    mstr_SQLInstruction = "SELECT Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.Skills) Not Like '%" & strFilter & "%') "
    mstr_SQLInstruction = mstr_SQLInstruction & "And ((Tbl_Skills.ShowYesNoFilter) = '" & mstr_Yes & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Filtered

    Case Else
    mstr_SQLInstruction = "SELECT Tbl_Skills.Skills, Tbl_Skills.ShowYesNoFilter FROM Tbl_Skills "
    mstr_SQLInstruction = mstr_SQLInstruction & "WHERE (((Tbl_Skills.Skills) Not Like '%" & strFilter & "%') "
    mstr_SQLInstruction = mstr_SQLInstruction & "And ((Tbl_Skills.ShowYesNoFilter) = '" & mstr_Yes & "'));"

    ExecuteCommand mstr_SQLInstruction, mstr_Filtered
    End Select

    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Did you check the reference to the ADODB library?
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37
    Quote Originally Posted by Sinndho View Post
    Can you please post the code you use in the database and if possible, indicate the line that causes the error. Seeing the code on the site you mention, the first thing that comes to my mind is that your database needs a reference to the ADODB library.
    As for what line is causing the error, I would say definitely the 'AddSingle' and 'AddAll', because I get that error when trying to move an item to the other list box.

  6. #6
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37
    Quote Originally Posted by Sinndho View Post
    Did you check the reference to the ADODB library?
    I honestly don't have any idea what that is. I'm not knowledgable of anything programming related. As you can see on the link I sent, there was nothing in the instruction which said to reference the ADODB library.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by ctown View Post
    I honestly don't have any idea what that is. I'm not knowledgable of anything programming related. As you can see on the link I sent, there was nothing in the instruction which said to reference the ADODB library.
    so why not go back to the original source for this code and ask them what is missing or corrupt

    the easiest (but not quickest) fix will be to comment out the code for the on click events
    then uncomment out lines / sections till the error reappears, then you know where to problem is.

    as an alternative open the form in design mode, then try to compile it.. if there are compilation errors that could cause the errors you report
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37
    Quote Originally Posted by healdem View Post
    so why not go back to the original source for this code and ask them what is missing or corrupt

    the easiest (but not quickest) fix will be to comment out the code for the on click events
    then uncomment out lines / sections till the error reappears, then you know where to problem is.

    as an alternative open the form in design mode, then try to compile it.. if there are compilation errors that could cause the errors you report
    I would totally do that but it seems the comments section is closed and noone has posted anything on it since 2010.

    How would I compile it?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    should be an option in the VBA window
    taking a wild guess that will be tools | compile
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree with Sinndho, this sounds like a Missing Reference problem. If you haven't done this before, here are Doug Steele's detailed instructions on how to troubleshoot the problem:

    Access Reference Problems

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37
    When I click compile, it gives a message "User defined type not defined" and highlights "mobj_ADODBRecordset As ADODB.Recordset"

    I'll check out the link you sent to see if I can troubleshoot this. If anyone can think of a simpler solution that would be great.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    As far as you want to use objects from a library, there is no simpler solution than creating a reference to it.
    Have a nice day!

  13. #13
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37
    I appreciate the help with this. If I run into any other issues I'll make sure to let you guys know. Thanks so much.

    Now, here goes nothing.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  15. #15
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37
    From what I've read on the article about References, it would seem that the code may not work given that it wasn't created on the workstation I'm currently using. Not sure if I can edit this and change it so that it works for the DB i'm creating. But it would seem I need to build the code from scratch, possibly. Is there any alternatives someone can provide so that I can get this to work. I can provide any info necessary to help take me through the steps of creating a code that will get the listboxes to function properly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •