Adding new records to a subform (m2m w/ assoc tbl) w/ unbound combo

[demosoc]

Hello!
New to Access and having a time.
Basically I want to be able to add records to a subform based on a non-id combo list. Did some research and it appears that this can be done with an unbound combo- great! But sadly, it's not working.

So, here's the background (probably too much info…)

TABLES AND FIELDS

tblCompany [fields Company_ID and Company_Description]
tblEvents [fields Events_ID, Event_Name, Event_Code]

both of which have a one to many relationship to the associate table

tblEventsCompany [fields EventsCompanyId, EventId, CompanyID]

QUERY

qryECom_Com

relationship:
tblCompany o2m tblEventsCompany, tblEvents o2m tblEventsCompany

fields:
tblCompany.Company_Decscription, tblCompany.Company_ID, tblEvents.Event_Name, tblEvents.EventCode, tblEventsCompany. EventsCompanyID, tblEventsCompany. EventsID, tblEventsCompany. CompanyID

FORM

frmEVENTS this form has a tab control with several tabs, each with a different subreport. Data is enabled on all tabs.

fields:
tblEvents.Event_Name, tblEvents.EventCode, tblEvents.Events_ID

SUBREPORT

subECom_Com link on EventId

record source:
SELECT tblCompany.Company_Description, tblEventsCompany.EventsCompanyID, tblEventsCompany.EventsId, tblEventsCompany.CompanyId
FROM tblCompany INNER JOIN tblEventsCompany ON tblCompany.Company_ID = tblEventsCompany.CompanyId;

Fields: I’ve currently included all fields in the query so that I can see if all is saving correctly. Which it’s not.
tblCompany.Company_Decscription, tblCompany.Company_ID, tblEvents.Event_Name, tblEvents.EventCode, tblEventsCompany. EventsCompanyID, tblEventsCompany. EventsID, tblEventsCompany. CompanyID

I’ve also added an unbound combo field:
Row Source:
SELECT tblCompany.Company_Description, tblCompany.Company_ID
FROM tblCompany
ORDER BY tblCompany.Company_Description;
Column count:
2

So. Here’s my extensive list of problems. I’m sorry.
1) While the combo box works great displaying the info so that the user can select from description w/o creating a new record, it changes that field for every record on every tab.
2) No new record is actually created
3) I don’t really want the user to be able to add a new company description, just choose from a list.
4) None of the other fields populates including the autonumber id.

I suspect that this might have something to do with the Event tab (On Enter, On Exit etc). Like I said, I’m new to Access and don’t know anything much. Beginner. No coding experience.
Any help would be much appreciated.

Thanks!

 

[fneily]

You mention you have a form and subreports. You must mean a form and subforms.
Are your primary keys autonumbers? Except for the junction table, I'd stay away from that. Alot of posts on why to avoid autonumber primary keys if you search. Like in your tblEvents you have Events_ID and Event_Code. Event_Code can be the primary key so get rid of Events_ID.
You recognized the many-to-many relationship and created a junction table. Good.
The tblCompany and tblEvents seemed to be pre-filled. You state no one will input a new company. So the junction table is the only table to be filled.
You only need one form based on the junction table, tblEventCompany. Create a form based on all the three fields. Then just delete the fields for EventId, CompanyID and in their place create comboboxes. The recordsource for the combos will be:
Select [Company_ID], [Company_Description] From [tblCompany] Order By [Company_ID]
Select [Event_Code], [Event_Name] From [tblEvents] Order By [Event_Code]

Bound these to in the Control Source to EventId, CompanyID.

So, since EventsCompanyId will be an autonumber, the other two fields are comboboxes, the user doesn't type. No typing, no errors. A perfect form.

 

[demosoc]

Thank you fneily!

The combo boxes are working great- but unfortunately I need them to be subforms off of the Event form. And though that's working great...
Is there a way to automatically fill the (subform, junction table) new record's EventId with the Id of the main form's Event_ID?

I'll have to go back and remedy my primary keys- thanks for the heads up!

 

[fneily]

First, be sure to use Event_ID in both table. Do not spell it differently. Access will automatically fill in Event_ID in one table when inputted in the other.
There's a one-to-many relationship between tblEvents and tblEventCompany. So you can use the Form Wizard to create the form/subform, or you can make two separate forms and the combine them using the subform/subreport button on the Toolbox toolbar. Either way, when you place a new Event_ID in the main form, Access will automatically put it in the subform, if spelled the same.
Don't know why you're doing it this way, though.

 

[demosoc]

Thanks again! I only wanted to see the event_ID to figure out why it wasn't creating records in tblEventsCompany, but the naming makes sense. Fixed the names so that they are spelled the same, and now everything is working great. Thanks so much!