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

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.

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

Did you check the 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.

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 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?

should be an option in the VBA window
taking a wild guess that will be tools | compile

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)>

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.

As far as you want to use objects from a library, there is no simpler solution than creating a reference to it.

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.

You're welcome!

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.