Append Records using MultiSelect Listbox

[Ted C]

It appears that I can't accomplish this without writing some code, so I'll need some help from people who know Visual Basic.

The objective is to create enrollment records for students in a class session.

• I have a table of class sessions with a primary key of "ClassEventID".
• I have a table of students with a primary key of "StudentID".
• I have a table for class attendance (ClassAttendance) with a primary key of "AttendanceID". It also has "ClassEventID" and "StudentID" as foreign keys.

I have a form with a list field bound to the "ClassEventID" of the table of sessions (ClassEvents).

I have a MultiSelect list box (lstStudentIDs) bound to the "StudentID" column of a query that pulls a list of all active students.



I have created an enrollment button (btnEnroll).

I need code for the btnEnroll button's click event that will append a record to the ClassAttendance table for every selected student in the lstStudentIDs listbox.

[pbaldy]

Does this help?

http://www.baldyweb.com/MultiselectAppend.htm

[Ted C]

Does this help?

http://www.baldyweb.com/MultiselectAppend.htm

Some, but my ability to read VB is limited (if this were C#, it would be much easier).

Is this what would go in the click event for the Enroll button? I'm not seeing anything that looks like SQL, so I don't see how the records are being added.

[Ted C]

I've added your sample db code and renamed objects for my database.

Private Sub btnEnroll_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  On Error GoTo ErrorHandler
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblClassAttendance", dbOpenDynaset, dbAppendOnly)
  'make sure a selection has been made
  If Me.lstStudentIDs.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 student"
    Exit Sub
  End If
  If Not IsNumeric(Me.SessionID) Then
    MsgBox "Must enter numeric Session ID"
    Exit Sub
  End If
  'add selected value(s) to table
  Set ctl = Me.lstStudentIDs
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!EmpID = ctl.ItemData(varItem)
    rs!OtherValue = Me.SessionID
    rs.Update
  Next varItem
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

Should I now be able to execute it? I still don't see the SQL "string" defined or called anywhere.

[pbaldy]

VB is practically like English. The records are added using the AddNew method of a recordset. For multiple records, that's more efficient that using SQL for each. Looking at your second post, you'd need to adjust the field names to match yours.

[Ted C]

VB is practically like English. The records are added using the AddNew method of a recordset. For multiple records, that's more efficient that using SQL for each. Looking at your second post, you'd need to adjust the field names to match yours.

Is the strSQL variable used within that AddNew method, then? I'm just wondering why it's defined but never given a value.

[pbaldy]

No, that's a "my bad". I use code templates and it got added by my DAO recordset template. I should have deleted it. Sorry about that.

[Ted C]

No, that's a "my bad". I use code templates and it got added by my DAO recordset template. I should have deleted it. Sorry about that.

So I need to write an append query and have it used in the function somehow, but I'm not sure how it should work.

[pbaldy]

No, you don't need SQL. These lines are adding the record:

rs.AddNew
rs!EmpID = ctl.ItemData(varItem)
rs!OtherValue = Me.SessionID
rs.Update

The first tells it you want to add a new record. The middle two lines are the lines that set specific fields to specific values. The last finalizes the insert. You would modify the middle two to your fields, adding more fields if necessary. The EmpID line is the one getting the current value from the listbox.

If you want to do it with SQL, you'd do something like this instead of those 4 lines:

db.Execute "INSERT INTO ..."

but you have to concatenate the values from the form into the SQL, which can be tricky if you haven't done it before. Like I said, the recordset method is faster for multiple inserts.

[Ted C]

I think I've got it worked out. I got rows added to the attendance table. The db is acting odd in other ways, but I think they're unrelated.

[RobertD0275]

I read through your tutorial listed here and tried to modify the code with no luck. I am self taught on this so I am learning, but not familiar at all with list boxes or better yet multiselect boxes. Here is the issue:

I have 3 tables, 1 with my user info (EndUserID, UserID, and FullName), 1 with All my software (SoftID, Vendor, Description, CostUS, CostCAD), and 1 empty table to store infor from prior 2 tables. the third table uses a unique key of SoftID2, the other fields are Vendor, Description, CostUS, CostCAD, EndUserID, FullName.

I have a sub form (subfrmUserSoftware) that is attached via a child linked tab to my UserForm. it is filtered based on the EndUserID to display only entried from the 3rd table that matches the Enduser ID from the endusers and usersoftware tables.

I have a button to ADD software, which opens a form called subfrmSoftwareSelect, which contains a listbox which quiries the first table containing all my software (SoftID, Vendor, Description, CostUS, CostCAD) are all visible. I also have a button Labeled "Select to Assign".

The listbox IS A MULTISELECT (Basic) listbox. I need to know how to run this so that when I click the select to assign button, it will make a table entry for each item that I have highlighted (say 5 at a time for example), have i write the 5 records to the third (empty table right now) with all the fields (SoftID, Vendor, Description, CostUS, CostCAD), as well as the EndUserID, UserID and FullName from the Form as well.

So what I will have at the end on the second (now blank) table is an entry for each piece of software that user has assigned to them, the enduserid, the domain (Userid), thier full name and all of the information from the main software table for each entry.

Then my child linked subform will be able to display what software they have in thier user details form.

Please help. I can send my db if needed.

[RobertD0275]

I read through your tutorial listed here and tried to modify the code with no luck. I am self taught on this so I am learning, but not familiar at all with list boxes or better yet multiselect boxes. Here is the issue:

I have 3 tables, 1 with my user info (EndUserID, UserID, and FullName), 1 with All my software (SoftID, Vendor, Description, CostUS, CostCAD), and 1 empty table to store infor from prior 2 tables. the third table uses a unique key of SoftID2, the other fields are Vendor, Description, CostUS, CostCAD, EndUserID, FullName.

I have a sub form (subfrmUserSoftware) that is attached via a child linked tab to my UserForm. it is filtered based on the EndUserID to display only entried from the 3rd table that matches the Enduser ID from the endusers and usersoftware tables.

I have a button to ADD software, which opens a form called subfrmSoftwareSelect, which contains a listbox which quiries the first table containing all my software (SoftID, Vendor, Description, CostUS, CostCAD) are all visible. I also have a button Labeled "Select to Assign".

The listbox IS A MULTISELECT (Basic) listbox. I need to know how to run this so that when I click the select to assign button, it will make a table entry for each item that I have highlighted (say 5 at a time for example), have i write the 5 records to the third (empty table right now) with all the fields (SoftID, Vendor, Description, CostUS, CostCAD), as well as the EndUserID, UserID and FullName from the Form as well.

So what I will have at the end on the second (now blank) table is an entry for each piece of software that user has assigned to them, the enduserid, the domain (Userid), thier full name and all of the information from the main software table for each entry.

Then my child linked subform will be able to display what software they have in thier user details form.

Please help. I can send my db if needed.

[pbaldy]

If you want to post your db with the failing code, we'll try to fix it.

[RobertD0275]

Sorry to have bothered you. I found a workaround using your sample code. It posted the softwareID for the software, and I used your other field to post the enduserID by using the =Nz(Forms![3frmUserDetails].[txtEndUserID]). This gave the 2 primary numbers I needed. Then I made a continuous form that pulled data from a query using the User table, the and the software table to fill in the blanks.

It now allows me to make a 2 line entry rather than 6 or 7, and I can easliy add and delete multiple entries from the resulting filtered form (filtered by EndUserID).

Thanks so much for answering so quickly.

[pbaldy]

No problem, glad you got it working.