Forms, subforms and headaches

[forson96]

Morning,



I am hoping I can get some guidance and get this sorted. I am an apprentice at a company where our sales guys go to events, at these events we receive a list of attendees and who has payed for them to attend. I have been asked to come up with a method where we can hold all of this data and look at it so that it makes sense... being able to see who's been to which event through which company, who's been at each event and which companies send which people to which events would be most useful. I remembered back to my school IT classes and suggested to make a database. Subsequently I've had all the data thrown at me - in paper form might I add - and told to get on which it.

I have successfully made a companies, events, customer tables and a linking table called CustomersEvents which holds all the primary keys as foreign keys linking people to companies and events. I have one to many relationships between the primary keys in the main tables and the foreign keys in the latter.

This is where good old Mr Paudyaw (not promising anything with that spelling) has failed me. I go to create a table using the table wizard, the first table I have tried to build is based on events, I want to be able to add an event, then basically fill in the list of people from the piece of paper I am handed. It creates a form 'Events' and a subform 'Companies'. I can see the data I have put in so far, so for viewing its fine, however it won't let me edit. I have attached screenshots.

In a perfect world I need the database to have a form where I can enter the details, it knows whether the event, customer or company already exists and assigns a new record in the CustomersEvents table with that customer etc.

I would be so grateful for any help, I have been messing around with this for over a week now, reading and watching videos, I just can't seem to get it right. At first it seemed really simple but it looks rather complicated

AccessAssist.zip

[ranman256]

If you are going to differentiate people and companies then each needs a tables...
tCo table
CoID (autoNum)
CoName
CoAddr
etc...

tPerson table
PersonID. (AutoNum)
FirstN
LastN
CoID (long)
etc...

TEvent table
EventID
EventName
EventDate
etc...

And the tCustomersEvents table
EventID. (Long)
PersonID (long)

Storing the PersonID will give you his CompanyID.
You need the Paerent form frmEvent, and the sub form , frmCustEvents
in the subForm , set the common fields on EventId
then you can enter people into the event, provided they are in tPerson table.
both could happen at the same time with some work.

[forson96]

Ranman thanks for your reply, I thought for a second that would crack the nut however people can have multiple assigned companies. For example JBloggs could be sponsored by Microsoft to one event and then Apple to another. That's why I have taken the approach of:

Table Customers
Table Events
Table Companies
Table CustomersEvents which holds foreign keys of Customers, Events and Companies

When I go to make the form I can see the data I have put in so far, but I would like to be able to use one form to enter data for the whole thing rather than having to go into each table and add the data, it's causing a lot of errors.

Thanks again!

[aytee111]

To enter a new record on a form:
- combobox to select event from events table
- combobox to select customer from customers table
- combobox to select company from company table
- any other fields

From Ranman's suggestions move the CoID from the tPerson table to the CustomerEvents table.

Note: when creating tables do not use the wizard. The table design is THE most important aspect of your database, everything from that point on is based on this solid foundation. Know what you are doing before you go into Access and begin typing and don't allow Access to make decisions for you. Also, do not use the tables for formatting or for lookups, create the tables to store data only and use forms/queries/reports for everything else.