Programmatically change 'Record Source' in a report or form

[WAVP375]

I am creating a DB of events. There will be multiple eventsat a given time. The attendees to the event will be selected in another DB andexported to excel. The excel spreadsheet will be imported to the new DB andwill create a table with all the intended attendees. Each event will have anexact table design with specific attendees. The table will contain ‘EventId’, ‘EventName’, ‘AttendeeId’, ‘AttendeeName’ and ‘NbrAttending’.As required each will need to be updated or a roster printed. After the eventis over the table will need to be deleted. I’m thinking a form with a list boxof events from which to select the one to be dealt with. Also a button toselect which of the update, delete or print options to be taken.
With a single form for each option, I am a t a loss as tohow to change the record source based upon the selection made in the list box.
Or is there a better way to address this challenge.


Any thoughts will behelpful, thanks.

[ranman256]

in a form, have a combo box, 2 cols: CAPTION, QRY
users see the caption name: CAPTION.
but combo is bound to hidden column: QRY
user picks ATTENDEES, the query is qsAttendees,

the afterupdate event assignes the query:

sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  me.RecordSource= "qsAll"
Else
  me.RecordSource = cboBox
End If
end sub

[WAVP375]

I'm having trouble with the logic. My combo box will contain 'Event Name' and 'Table Name' sourced from an Event Table.
When I select an event how do I get that table name into the report or update form objects?

[ranman256]

Make it easy,
when you pick the Event, have another column in the combo for the report for that event,
open that report for that event.

[WAVP375]

That would require multiple report objects. One report object with different tables for input.

[ssanfu]

So if you have 16 events in an 8 hour day, you are going to create 16 tables? Really?

Why not have 1 table (since all tables will have the same structure) and append the records to that table?
Then you would filter the table by "EventId" and "EventDateTime" to view in a form or report/roster list. You can delete the attendees but selecting the "EventId" and "EventDateTime".



Would have to see your dB to provide a better answer........

[Robyn_P]

Have to agree with ssanfu, I think you need to think a bit more about how you're setting up this database. I would have one table to hold the event details (EventID, EventName, EventTime, bla bla) with a 1 to many relationship with a table for the attendees (EventID, AttendeeID, AttendeeName). You wouldn't need a number of attendees field as you can just count the number of attendee records, though if you want to check that you have details for the right amount of people it could be useful. For importing the data from the spreadsheet, you could have a form on which you select the event they are attending and from this get the event ID needed to populate EventID in tblAttendees.