Create Multiple Records In a Subtable From Single Subform (1 Viewer)

[themurph2000]

I'm sure the answer is here somewhere, but I can't find it and it's driving me nuts.

I have three tables.

1. COURSES
2. COURSE SCHEDULE
3. COURSE ROSTER

As you might suspect, they are in one-to-many relationships with each other. (Courses -> Course Schedule -> Course Roster) What I'm looking for is a way where, once a time for a course is set (linked via a CourseID and CourseTimeID fields), where a list can be generated from my Students table and someone can click on each of the students taking a particular course. This would then populate the Course Roster table with those student ID's linked to the appropriate CourseTime ID.

Right now, I have a subtable with the ID field as a drop-down list, but the people who want this want something faster instead of clicking on a drop-down list 20 or 30 times per class. I'm sure the answer is simple, but the caffeine hasn't kicked in yet on an early Monday morning.

Thanks!

 

[pbaldy]

Is this what you're after?

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

 

[themurph2000]

Is this what you're after?

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

More or less, yes sir. The only difference would be, in my database, that number was predetermined before the students were selected, as it was a reference number to a specific class and time.

But this is wonderful. Thanks.

 

[pbaldy]

Happy to help. Let me know if you need help adapting it.

 

[themurph2000]

Happy to help. Let me know if you need help adapting it.

I should be able to make it work, since you did such a nice job of designing it. But I will take you up on that if I run into trouble.

 

[themurph2000]

Happy to help. Let me know if you need help adapting it.

OK, now I need help. I was setting up a command button to clear any selected items in the list box, but I couldn't find the appropriate command in the VBA code. Any suggestions?

Thanks.

 

[pbaldy]

Along the lines of:

  Dim ctl           As Control
  Dim i             As Integer

  Set ctl = Me.lstCharges

  For i = 0 To ctl.ListCount - 1
    ctl.Selected(i) = False
  Next i

  Set ctl = Nothing

 

[themurph2000]

Along the lines of:

  Dim ctl           As Control
  Dim i             As Integer

  Set ctl = Me.lstCharges

  For i = 0 To ctl.ListCount - 1
    ctl.Selected(i) = False
  Next i

  Set ctl = Nothing

OK, cool. I didn't realize there was no magic command. Just do it old-school, right?

I must be getting better. I was even able to program it to return to the top. (which I imagine would have taken you about 3 seconds)

Thanks.

 

[pbaldy]

No problemo Patrick.

 

[spudracer]

I'm in the similar boat as "murph."

I've got two tables, tblPersonnel and tblTrips.

I want to be able to add trips (no, not vacations) to a form. The information would be the title of the trip, start date, end date, and remarks. Once this information is in, the user would then click a button which would, in turn, save the trip, then prompt the user to either add personnel to the trip, or close the box.

If they choose to add personnel, a separate form would pop up, with the personnel from the Personnel table, where they could select all the members of that trip and save them to that trip's record.

This would be a 2007 database.

 

[themurph2000]

I'm in the similar boat as "murph."

I've got two tables, tblPersonnel and tblTrips.

I want to be able to add trips (no, not vacations) to a form. The information would be the title of the trip, start date, end date, and remarks. Once this information is in, the user would then click a button which would, in turn, save the trip, then prompt the user to either add personnel to the trip, or close the box.

If they choose to add personnel, a separate form would pop up, with the personnel from the Personnel table, where they could select all the members of that trip and save them to that trip's record.

This would be a 2007 database.

Most of what I did involved taking the value from the first table (TripID in your case), and store it in a text box which would be set to invisible in its properties. On the opening of the other form (the one where you would add people), there would be another text box where you would have a command to transfer the data from one text box to another. Then the new text box would be tied to a TripID field in the Personnel table.

As for selecting all, simply use the code PBaldy showed me, only change..

ctl.Selected(i) = False

to

ctl.Selected(i) = True

As for adding the records, here is the VBA code PBaldy pointed out to me, which you tie to your "Add Records" button on the form. Use the link he gave in the reply to the original post.

Private Sub cmdAddRecords_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("YourTableofPeople", dbOpenDynaset, dbAppendOnly)

  'make sure a selection has been made
  If Me.lstofPeople.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If

  'add selected value(s) to table
  Set ctl = Me.lstofPeople
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!PersonID = ctl.ItemData(varItem)
    rs!TripID = Forms!frmYour2ndForm.TripIDBox
    rs.Update
  Next varItem
MsgBox "Names have been entered"
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

 

[spudracer]

What's the significance of this line?

Set ctl = Me.lstCharges

And, for me, what should it point to?

 

[spudracer]

I have a feeling I'm getting in way over my head with this. Any chance I could get a sample for this?

 

[pbaldy]

You mean like the sample I linked to earlier?

This line

Set ctl = Me.lstCharges

sets a variable to the listbox being used. You would change "lstCharges" to the name of your listbox. It's optional, you can simply refer to the listbox everywhere you see "ctl".

 

[spudracer]

I've got your sample DB open, but this is confusing the crap out of me. LOL

I keep going back and fourth between yours and what I'm building. Frustrating!!!

 

[spudracer]

I think the problem I'm running into is this...

I'm wanting the user to open one form "frmTripAdd" in order to create a trip including the start and end dates, with a button to then open the form "frmPersonnelTripAdd" where they can select all the applicable personnel for that specific trip.

I've got the ctl set as Me.ListOfPersonnel, but it never recognizes it, even though I have done everything based off your sample.

 

[spudracer]

This is what I have so far.

 

[themurph2000]

This is what I have so far.

You have two different programs tied to the same command button. The button you have so far should only open the Personnel form. Everything else should be done once that form is open.

Also, you need to create a third table. This table should only have the list of trips. Otherwise, you'd be repeating information for every person who goes on a particular trip. Then use the current Trips table (which I'll call Participants) to tie the personnel to the proper TripID, rather than repeating the name of the trip every single time.



Here's a list of items you'll need to do.

1. Tie your current Trip form to the new table I mentioned above. I'll call that one Trips and the one you currently have with a TripID field and a MemberID field we'll call Participants.
1. Add a text box to the Trip form for the TripID field and set it to NOT Visible in the Properties.
2. Delete the current Command button in the Trip Form and create a button that opens the form Participants, which is what you'll rename the Personnel form.
3. There's a thread (Paul's work again, so you know it's good) that explains passing a value from one form to the other.

http://www.access-programmers.co.uk/forums/showthread.php?t=197694&highlight=setvalue


I would recommend doing these things first before worrying about the list, since your table structure has to be fixed.

 

[spudracer]

Patrick, can I put you on retainer? LOL

Thanks for the tips!

 

[spudracer]

Ok, for some reason. I cannot focus on this and I keep missing something.

My intention is not for you to hold my hand through this, but at this point, I'm clearly not doing something right.