Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    Question Unanswered: Linked tables and list boxes

    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?

    Function Add()
    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."
    Else
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set MyTable = MyDB.OpenRecordset("MyMaster")


    MyTable.Index = "field1"
    MyTable.Seek "=", y

    MyTable.Edit
    MyTable![Selected] = 0
    MyTable.Update

    MyTable.Close
    Me![Listbox1].Requery
    Me![Listbox2].Requery

    Me![Listbox1] = Null
    End If
    End Function

    TIA
    SteveH

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey Steve,

    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"
    Exit Sub
    Else
    Beep
    If MsgBox("Are you sure you want to move the selected record(s)?", vbYesNo, "Confirmation") = vbNo Then
    Exit Sub
    Else
    DoCmd.SetWarnings False
    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 & """);"
    Next varItem
    DoCmd.SetWarnings True
    End If
    End If
    End Sub

    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.

    Later, Kal

Posting Permissions

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