Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Multi-Select from a List to Populate table (Access 2000)

    Hey guys,
    My objective is to give the user the capability to open a form, click all the people from a list (multi-select), indicate the date from a combo box, and with one click of a button, add these selected people to the Attendance table along with the one date that was specified on the combo box.

    Can anyone think of a better way to do it? So far I have this:

    Have a make-table query, which takes values from two tables, Participants and Meeting. The criteria for this query are 1) whether or not the Participants are selected (Select field in Participants table is checked) and 2) the date specified on the frmAttendance.

    This query creates a temporary table. This temporary table is used in an append query. The records, which consist of NetID and MeetingID fields, in the temp table are appended to the table Attendance.

    Now, the form that the user uses, contains a two lists and a combo box, and command buttons. The first list is an unbound list that lists all the Participant names from the Participant table. The user will highlight Participant names on this list and then click on the button, Select, at which point, the highlighted names will be transfered over to the second list. Then the user will click on another button to run the Make-Table query.

    So, here are my questions:
    1) Can a list with the multi-select option on be used in this case? if so, how can i indicate which values on the list are highlighted? is it " List_Name.Value.Yes "?

    2) Is there a better system of doing this?

    thanks guys for any help!!

    quiddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    There are two ways to get at the selected items in a multi-select list box: the Selected property and the ItemsSelected collection. ItemsSelected is not entirely reliable for list boxes with MultiSelect set to Extended in Access 97; I don't know if this has been repaired in Access 2000.

    The Selected property is a zero-based array of boolean values indicating whether a list item is selected. For example, lstMyListBox.Selected(2) is True if the third item (remember, it's zero-based) of lstMyListBox is selected, False otherwise.

    So you can loop through all items:

    Dim i As Long
    For i = 0 To lstMyListBox.ListCount - 1
    If lstMyListBox.Selected(i) = True Then
    ' do something here
    End If
    Next i

    I don't see the need for creating a temporary table; you can use the code to assemble the SQL string for an append query, then execute the SQL string using DAO, ADO or DoCmd.RunSQL.

    The SQL string would look like:
    "INSERT INTO Attendance (NetID, MeetingID) SELECT NetID, " & _
    Me.txtMeetingID & " AS MeetingID FROM Participants " & _
    "WHERE NetID In (" & strList & ")"

    Of course, you must substitute the appropriate names.
    The string variable strList is assembled in a loop like above:

    Dim strList As String
    Dim i As Long
    For i = 0 To lstMyListBox.ListCount - 1
    If lstMyListBox.Selected(i) = True Then
    strList = strList & "," & lstMyListBox.ItemData(i)
    End If
    Next i
    If strList = "" Then
    ' nothing selected - get out now!
    Exit Sub ' or something
    Else
    ' Remove leading comma
    strList = Mid(strList, 2)
    End If

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    Thanks Hans!
    I have another question regarding the code.

    Dim i As Long
    For i = 0 To lstMyListBox.ListCount - 1
    If lstMyListBox.Selected(i) = True Then
    ' do something here
    End If
    Next i

    In the code above, how can I update the Participants table so that the for each i selected in the list, its corresponding netid in the Participants table will be selected. The Participants table has a Selected field (type Yes/No). How can I update the Selected field to Yes of the netid's whose corresponding netid's are selected in the list?
    Does the list have another property that captures the i's data? I will be putting this code in a command button on the frmAttendance. Therefore, how should I reference tblParticipants in the code?

    Please let me know if I am or am not making sense.

    Thanks!!
    chris

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    Thanks, Hans.
    But when I click on the command button, after I have pasted the code in the command button, it is giving me a run time error which reads:

    Run time error '3061':

    Too few parameters. Expected 1.

    Then when I debugged, it highlighted

    CurrentDb.Execute strSQL ' this is DAO

    Then I tried the other option which is

    DoCmd.RunSQL strSQL , but that does not work either.

    any ideas?

    thanks!!

    quiddy

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    Hans,

    I never ran into any problems with a multiselect in A97. We use them in some of our commercial apps and I use the ItemsSelected collection without any reliability issues. I don't want to hijack the thread, but if you could be more specific about any problems, it would be useful information for us all.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    Hi Charlotte,

    The ItemsSelected collection of a list box with MultiSelect set to Extended can return null or incorrect items in Access 97. I first learned about this from Roger Carlson's Access 97 Sample Library.

    I have attached a somewhat modified version of Roger Carlson's demo. The startup form contains instructions.

    As noted before, I don't have Access 2000 or XP, so I don't know if this bug (?) still exists there.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    Hi quiddy,

    The error message you get means that the database engine didn't recognize one of the field names in the query. You have to check very carefully whether the names used in strSQL are EXACTLY the same as the names in your table. I assumed a table named tblParticipants with fields named NetID and Selected; if you have different names, use those in strSQL.

    Note: in my earlier post, there was a typo: the list box was listMyListBox in one occurrence instead of lstMyListBox. I have corrected this.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    Chris,

    The code could look like this:

    Dim strList As String
    Dim strSQL As String
    Dim i As Long

    ' Loop through list box items
    For i = 0 To lstMyListBox.ListCount - 1
    If lstMyListBox.Selected(i) = True Then
    ' Append item data to string if selected
    strList = strList & "," & lstMyListBox.ItemData(i)
    End If
    Next i

    ' Check if anything was selected
    If strList = "" Then
    ' nothing selected - get out now!
    Exit Sub ' or something
    End If

    ' Remove leading comma
    strList = Mid(strList, 2)

    ' Create SQL string for update query
    strSQL = "Update tblParticipants SET Selected = True " & _
    "WHERE NetID In (" & strList & ")"

    ' Run the SQL
    CurrentDb.Execute strSQL ' this is DAO
    ' or DoCmd.RunSQL strSQL

  9. #9
    New Lounger
    Join Date
    Nov 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    hey hans,
    i have checked but i still get that error message. i don't know if this tidbit indicates any clues but in the error message, "Too few parameters. Expected 12",
    the number 12 corresponds to the number of names i highlighted in the list. In this case, I highlighted 11 people, and when I clicked on the command button, Command23, it gave me the error message.

    Anyways, the code below is what I have under the on enter event of Command23:
    Note that the table name is tblLiaisonParticipants, with fields NetID and Selected (of type Yes/No). I tried using "Yes" in strSQL instead of "True" but it still gave the same message.

    Private Sub Command23_Enter()
    Dim strList As String
    Dim strSQL As String
    Dim i As Long

    ' Loop through list box items
    For i = 0 To lstUL.ListCount - 1
    If lstUL.Selected(i) = True Then
    ' Append item data to string if selected
    strList = strList & "," & lstUL.ItemData(i)
    End If
    Next i

    ' Check if anything was selected
    If strList = "" Then
    ' nothing selected - get out now!
    Exit Sub ' or something
    End If

    ' Remove leading comma
    strList = Mid(strList, 2)

    ' Create SQL string for update query
    strSQL = "Update tblLiaisonParticipants SET Selected = True " & _
    "WHERE NetID In (" & strList & ")"

    CurrentDb.Execute strSQL

    End Sub


    i can't possibly find anything wrong with it, can u?

    thanks
    quiddy

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    I had assumed that NetID is a numeric field. If it is a text field, you will need to put quotes around the selected values. Try and see if it works with<pre>strList = strList & ",'" & lstUL.ItemData(i) & "'"</pre>

    instead of<pre>strList = strList & "," & lstUL.ItemData(i)</pre>

    If you still have problems, insert a statement MsgBox strSQL immediately above CurrentDb.Execute strSQL and check the displayed string carefully. You can use Debug.Print strSQL instead; this will output the SQL string to the Debug window; you can copy it there and paste it into a reply if you wish.

  11. #11
    New Lounger
    Join Date
    Nov 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Multi-Select from a List to Populate table (Access 2000)

    omg!! it worked!! thanks so much. im so dumb for not having cleared that up in the beginning. oops on my part! but thanks soo much, hans.
    quiddy

Posting Permissions

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