Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    append records to table from multiselect list box (2000 (all updates))

    Two tables ... tblMembers, tblAttendance, related by MemberID in tblMembers (primary key) and MemberID in tblAttendance (foreign key).

    tblAttendance has 4 fields...
    AttendanceID (autoNumber)
    MemberID (foreign key)
    MeetingDate (date field)
    Present (Yes/No field)

    I have an unbound form called frmAttendance. The purpose of this form is to post weekly attendance for a club.

    On the form is a multiselect list box, lstMembers. The first column, or Column(0), in the list box is MemberID.

    A selection on a Calendar populates a text box, called txtMeetingDate.

    Problem 1
    What I want to do is append, by a command button, to tblAttendance, the rows selected in the list box.

    What I have been working with is...
    <code>Dim ndx As Integer
    Dim strList As String
    For ndx = 0 To Me.lstMembers.ListCount - 1
    If Me.lstMembers.Selected(ndx) = True Then
    strList = strList & Me.lstMembers.ItemData(ndx) & ", "
    End If
    Next ndx
    sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present) VALUES (Forms!frmAttendance!lstMembers.ItemData(ndx), Forms!frmAttendance!txtMeetingDate,True)"

    DoCmd.RunSQL sql</code>

    Obviously I have something wrong here.

    Problem 2
    Once I get the above working, I will need some method to ensure that the user who posts the attendance data will not re-post additional data for the same MemberID for the same date.

    Thanks for any assistance.

    Tom

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

    Re: append records to table from multiselect list box (2000 (all updates))

    INSERT INTO with the VALUES keyword can append only one record at a time, so you must insert the records within the loop:

    If Me.lstMembers.Selected(ndx) = True Then
    sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present) VALUES (Forms!frmAttendance!lstMembers.ItemData(ndx), Forms!frmAttendance!txtMeetingDate,True)"
    DoCmd.RunSQL sql
    End If

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

    Re: append records to table from multiselect list box (2000 (all updates))

    Problem 2: you could define a unique index on the combination of MemberID and MeetingDate (you could even make it the primary key).

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append records to table from multiselect list box (2000 (all updates))

    Hans
    Thanks for that. Still not working quite right.

    Here's what I have for code
    <code>Dim sql As String
    Dim ndx As Integer

    If Me.lstMembers.Selected(ndx) = True Then
    For ndx = 0 To Me.lstMembers.ListCount - 1
    sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present) VALUES (Forms!frmAttendance!lstMembers.ItemData(ndx), Forms!frmAttendance!txtMeetingDate,True)"
    DoCmd.RunSQL sql
    Next ndx
    End If</code>

    When I attempt to run it, I get
    Undefined function 'Forms!frmAttendance!lstMembers.ItemData(ndx)' in expression.

    If the Values keyword can only append one record at a time, is there a better way to do it?

    Thanks.

    Tom

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

    Re: append records to table from multiselect list box (2000 (all updates))

    This doesn't make sense
    <code>
    For ndx = 0 To Me.lstMembers.ListCount - 1
    If Me.lstMembers.Selected(ndx) Then
    sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present) VALUES (" & _
    Me.lstMembers.ItemData(ndx) & ", #" & Me.txtMeetingDate & "#,True)"
    DoCmd.RunSQL sql
    End If
    Next ndx
    </code>
    The alternative would be to build an SQL string of the form

    INSERT INTO ... SELECT ... FROM ... WHERE ...

    Although that can be done, it's more complicated.

Posting Permissions

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