Adding multiple records from one form

[sotssax]

I have a database that tracks teachers at a school. Right now i have three tables; one for teacher demographics info, one for professional development tracking (for each teacher) and another for tracking time off.



What I would like to do is enter information about a single professional development event and attach the staff ID of everyone who attended. Basically creating a new record for each teacher who attended with all the professional development info the same.

Is there a way of doing this?

Oh, and I'm teaching my self Access while creating this database.

[June7]

Development events and teachers is a many-to-many relationship. Each event has many teachers and each teacher can have many events. This calls for a table of events, a table of teachers, and a junction table. The junction table would have at least two fields, as foreign keys for the event ID and teacher ID.

Date entry setup could be form/subform arrangement. Main form would be bound to the Events table. Subform would be bound to the junction table. Enter new event info on the main form then select attending teachers in the subform. The forms are synchronized by the Master/Child link properties of the subform container.

[pbaldy]

If you want to use a listbox:

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

[June7]

Yes, a multi-select listbox could be a good alternative to the subform. It does require code, as suggested by pbaldy. The junction table is still needed, just a different way of getting records entered.

[sotssax]

Thank you for the help. That clears things up. I have my tables set up. Now I am stuck on the form. I understand what you said about the main form and sub form. The problem is that my junction table only includes my development ID and teacher ID. Teacher Id is an auto number from the teachers table. How do tell the subform to display the teachers name from the teachers table and put the ID in the junction table?

[June7]

So you want the subform approach.

The teacherID control should be bound to the teacherID field of junction table. Make this control a multi-column combobox. Check out this tutorial http://datapigtechnologies.com/flash...combobox3.html

You can also create a multi-column listbox with the same technique.

If you want more teacher info to show in the subform, make its RecordSource a join of the junction and teachers tables. The jointype would be: Show all records from 'junction table name'...
Then have textboxes bound to teachers fields and set Locked property to yes. They should not be editable in this join but this is a precaution. Also set TabStop property to no.

[rpeare]

Create a table called Tbl_Staff
Add three fields
StaffID (autonumber)
StaffFN (text)
StaffLN (text)

Enter three staff names

Create a table called Tbl_Events
Add two fields
EventID (autonumber)
EventName (text)

Enter 3 - 5 Event Names

Create a tabled called Tbl_StaffEvents
Add three fields
StaffEventID (autonumber)
StaffID (number)
EventID (number)

Create a form
Add a listbox with this SQL statement

SELECT Tbl_Staff.StaffID, [staffln] & ", " & [stafffn] AS StaffName, Tbl_Staff.StaffLN, Tbl_Staff.StaffFN FROM Tbl_Staff ORDER BY Tbl_Staff.StaffLN, Tbl_Staff.StaffFN;

Call the list box Lst_Staff
Go to the MULTI SELECT property of the list box and choose SIMPLE

Add a list box with this SQL statement

SELECT Tbl_Events.EventID, Tbl_Events.EventName FROM Tbl_Events;

Call the list box Lst_Events
Go to the MULTI SELECT property of the list box and choose SIMPLE

Put a button on your form
add this code to the ON CLICK property of your button:

Dim lstStaff As Variant
Dim iStaff As Integer
Dim lstEvent As Variant
Dim iEvent As Integer
Dim db As Database
Dim sSQL As String

Set db = CurrentDb
    
iCount = 0
            
If Me!Lst_Staff.ItemsSelected.Count <> 0 Then
    For Each lstStaff In Me!Lst_Staff.ItemsSelected
        iStaff = Me!Lst_Staff.ItemData(lstStaff)
        If Me!Lst_Events.ItemsSelected.Count <> 0 Then
            For Each lstEvent In Me!Lst_Events.ItemsSelected
                iEvent = Me!Lst_Events.ItemData(lstEvent)
                sSQL = "INSERT INTO Tbl_StaffEvents (StaffID, EventID) VALUES (" & iStaff & "," & iEvent & ")"
                'MsgBox sSQL
                db.Execute sSQL
            Next lstEvent
        Else
            MsgBox "Nothing was selected from the EVENT list", vbInformation
            Exit Sub  'Nothing was selected
        End If
    Next lstStaff
Else
    MsgBox "Nothing was selected from the STAFF list", vbInformation
    Exit Sub  'Nothing was selected
End If

If you set the multi select property SIMPLE each item you click will be selected, you have to click the same item again to unselect it

If you set the multi select property to EXTENDED each item you click will erase all other selections in that list box and only select the item you just clicked. If you want to add additional items or remove items one at a time you have to hold down the SHIFT key and click the item.

The code will add a record for each staff and each event chosen. So if you choose two staff and two events you will end up with four records in the table TBL_STAFFEVENTS.

I believe this is what you're after, you can modify the code to your situation.

[sotssax]

Thank you again June7 and thank you rpeare. I'll try it and get back to you both. I'm not comfortable with code so I'll start with June7's approach. I really appreciate all of you helping a complete stranger and noobie.

[sotssax]

I got it to work, but I had to tweak rpeare's code because I didn't need to select multiple events. The point of the form is to enter information about a new event and list the attendees. here is the code I ended up with after making the listbox.

Dim lstStaff As Variant
Dim iStaff As Integer
Dim PD_ID As Variant
Dim iEvent As Integer
Dim db As Database
Dim sSQL As String

Set db = CurrentDb
    
iCount = 0
            
If Me!teacher_list.ItemsSelected.Count <> 0 Then
    For Each lstStaff In Me!teacher_list.ItemsSelected
        iStaff = Me!teacher_list.ItemData(lstStaff)
        iEvent = Me![PD_ID]
                sSQL = "INSERT INTO TeacherPD (staffID, [PD_ID]) VALUES (" & iStaff & "," & iEvent & ")"
                'MsgBox sSQL
                db.Execute sSQL
            Next lstStaff
Else
    MsgBox "Nothing was selected from the STAFF list", vbInformation
    Exit Sub  'Nothing was selected
End If
End Sub

Thank you all for your help.