One Record creates multiple records

[poul0035]

I have a table (Events) in which I need the action to be: when I create a record in event it automatically creates records on another table (Event_description) based on another table (Event_location).

Tables:
Event
-Event_ID
-Event_name

Event_location
-Location_ID
-Location_name

Event_Description
-Description_ID
-Event_ID_fk
-Event_location_ID_fk
-Location_interest (Y/N)

Basically I need to have it be relational on that when an event is created it automatically creates records in Event_description based on the Event_location table, so that I can mark whether the event has interest in the location

Or would this be accomplished through a form? Form lists each of the locations in the Location table and a check box next to each that records to the Description table.

Am i making sense? This is my first post.

Thanks

 

[pbaldy]

Welcome to the site. Access does not have triggers, so you'd need to use a form. Exactly how would probably depend on your work flow. Are you saying that as you add an event, you'd pick the locations, and the combination's would go to the description table?

 

[poul0035]

Thanks.

Pretty much, I would add an event and pick the locations and Description table would record event_id, location_id, and interest
If three locations were chosen, three records would be made: all three same event_ID, 3 different location_ids and 3 yes

 

[pbaldy]

Would something along these lines do the trick?

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

 

[poul0035]

looks pretty similar to what i need. Thanks. But, maybe I am stupid for asking, how do you make it work? shouldnt the 'add record' button then make records in Other table?

 

[pbaldy]

It should be working "out of the box"; is it not? Based on your description, you might want to base the form where the button and listbox are on the events table, and the button would add records to the description table.

 

[poul0035]

No the event does not seem to be working.

 

[pbaldy]

Well, I know it works. If you have 2007, you'd need to make sure code is enabled or the db is in a trusted location. First item under configuration:

http://allenbrowne.com/Access2007.html

 

[poul0035]

Awesome, works great. If its not too much trouble any way that you could add another table (like employees) in which you must select one from each and an 'other value'?..Trying to make sure I understand the code. Thanks.

 

[pbaldy]

You could change the text box in my example to a combo box that got its selections from another table, if that's what you mean.

 

[poul0035]

Yeah, that should get me going. Thanks for all of the help.