Multiple entries from a drop down list

[atlanteavila]

Hello all,

I've attached a database here that we're using to register people for a series of classes, for a course that we're offering for pre-baptismal certification.

I'm wondering if there is a way that we could use one selection from the drop down list to auto populate the entire course dates in the sub form. That way, we would only select one item instead of having to select every class individually for every person that registers.

I hope that's clear enough please take a look at the contacts form to get a better idea of what I'm trying to do.

Thanks for your help in advance!

Atlante

P.S.
I'm using the Access2003

[ADezii]

@atlanteavila
The only Drop-Down I see is the Date Field in the Registrations Sub-Form. Is this what you are referring to?

[atlanteavila]

Yes, that's the one I would like to use. Thank you!

[ADezii]

@atlanteavila
Are you requesting that if the User selects 'ANY' Date from the Date Drop-Down that it (Sub-Form) should be populated with 'ALL' available Dates? If my assertion is correct, the only thing that makes any kind of sense to me is that the Sub-Form is automatically populated after a Record is added to the Main Form. Is this logic, assuming the previous is correct, acceptable to you? In my thinking, you can run into a lot of problems, and a lot of coding any other way.

[atlanteavila]

Well sort of. I'm thinking I may need to add another drop down field and once I select something like full course from that field, the after update comes in and automatically adds all of the classes to the sub-form. Sounds a little far fetched but we have over 200 people that are planning to attend this course and it's just driving me crazy having to select every class for these registrants. Any help would be great or if you can think of a different work around I wouldn't mind going with anything at this point. It's just so time consuming!

Thanks for your help!

[ADezii]

@atlanteavila
When I get a chance, I'll create some Generic Code that will automatically populate the Sub-Form with all the available Dates. How to implement it, I'll leave up to you. Actually, I made this a Rush Job before I went to bed. The following Code should work rather nicely for you:

Dim intNumOfRelatedRecords As Integer
Dim MyDB As DAO.database
Dim rstClasses As DAO.Recordset
 
'Are there already Records in the Sub-Form based on the Current [CID]?
intNumOfRelatedRecords = DCount("*", "[Registrations]", "[CID] = " & Me![CID])
 
If intNumOfRelatedRecords > 0 Then Exit Sub   'Related Records exist
 
Set MyDB = CurrentDb()
Set rstClasses = MyDB.OpenRecordset("Classes", dbOpenForwardOnly)
 
With rstClasses
  Do While Not .EOF
    CurrentDb.Execute "Insert Into [Registrations] ([CID], [Date]) Values (" & _
                       Me![CID] & ", '" & ![ClassID] & "');", dbFailOnError
    .MoveNext
  Loop
End With
 
Forms![Contacts]![Registrations Subform].Form.Requery
 
rstClasses.Close
Set rstClasses = Nothing