The following code is in a form containing 2 list boxes. It allows the user to double click on an item in listbox1 which moves it to listbox2. The code works fine with regular tables but not with linked tables. It fails at the line "MyTable.Index = 'field1' " with the error message "Operation is not supported by this object".
Can this be made to work with linked tables?
Dim MyDB As Database
Dim MyTable As Recordset
Dim y As Control
Set y = Me![Listbox1]
If IsNull(y) Then
MsgBox "Please select something in the list."
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("MyMaster")
Here is something that I think you will enjoy. But, you will have to do a little modifying. O'k???
First goto your List Box that you double click on and choose properties. Goto the Other tab and under the field Muti Select choose Extended. This will allow you to select multiple items in your list box. Now create a new button on your form and place this code on the On Click event:
Private Sub Button_Click()
Dim ctl As Control
Dim varItem As Variant
Dim msgText, Response As String
Dim txtMove As String
If Me.ListBox.ItemsSelected.Count = 0 Then
MsgBox "No selection was made", vbInformation, "Message"
If MsgBox("Are you sure you want to move the selected record(s)?", vbYesNo, "Confirmation") = vbNo Then
Set ctl = Me.ListBox
For Each varItem In ctl.ItemsSelected
txtMove = ctl.ItemData(varItem)
DoCmd.RunSQL "INSERT INTO SomeTable SELECT SomeField FROM SomeTable WHERE(SomeField = """ & txtMove & """);"
You may have to change the ListBox text in this code to the actual name of your listbox and also in the INSERT INTO SQL Statement change the values to suit your tables. Finally, when you select as many records as you want the code will insert the selected text into your second table while looping itself until there are no more selections. This helps to cut down on double clicking all those records. If you want to keep the double click feature just modify the code so it only runs once. Let me know if you have any questions about this, or if you want a different method because I can give you others if needed.