Desperately in need of help

[Brad108]

Hi

I am in the process of finishing off a database and was wondering whether I could seek some assistance to problems which are driving me mad.

I have has some very helpful responses from the forum and I am very grateful for them. I have 2 remaning problems and would be so appreciative of some help.

Database details:

It is an access 2003 contact database, where I have 3 tables: contact, company and address.

I have 3 forms, to match the above. The contact form asks for contact details and has an address subform. The company form asks for company details and has an address subform and contact subform.

My relationships go from addressID to companyID and from contactID to companyID (with both arrows going towards companyID at the centre).

The problems remaining are:

1. I am unable to enter new record information in the address subforms. They are set to allow deletions, additions etc. (and show the correct information) but when I try and type I get an error saying unable to enter data as 'control is read only, open in design view or value too large'. The source is the address table, so it should work okay.

2. I can't seem to get the links working correctly. When I enter a new contact, I type all the information in, including the company name (which is in a combo drop down box, getting information from the company table) and although it all saves fine, the new contact doesn't appear on the company screen (which has a contacts subform).

Occasionally I get a runtime error 3075 'syntax error (missing operator) in query expression 'CompanyID' =', am not sure how this relates.

If anyone could help, that would be so great. Am getting very depressed and frustrated by it all.

Let me know if you need any more information.

Thanks in advance.

Brad

 

[MarkK]

It doesn't make sense to me that you describe your relationships as "going from addressID to companyID and from contactID to companyID (with both arrows going towards companyID at the centre)."

The Company table contains a unique key CompanyID, also called a primary key. In addition, your company table should have an AddressID, also called a foreign key, which relates a unique address record to the company. A company record might actually have two foreign address keys if you want to support a billing and shipping address for a company.

Your tables' key fields should look something like this...

[B]Company[/B]
CompanyID, PK
AddressID, FK

[B]Address[/B]
AddressID, PK

[B]Contact[/B]
ContactID, PK
CompanyID, FK
AddressID, FK

Same named fields should be related to each other. PK fields are unique to the table in which they appear, and FK fields should contain the same ID number of the PK field to which they are related.

 

[Brad108]

Hi

Thanks for the reply - i have set up the connections as you said. With regards the the way the arrow on the line goes, I have set up the arrow going 'away' from the PK towards the FK - is that right?

The relationship screen shows 4 table columns now.

Addresses, with addressID (pk) going towards AddressID (fk) in Company table. Then I have companyID (pk) in company table going towards CompanyID (fk) in contact table. Then I have AddressID from Contacts_1 going AddressID in company (neither of which is a PK).

Is that right?

Thanks so much for the help.

Kind Regards

Brad

 

[Brad108]

Hi

Looking at the values of the ID fields - all are the same starting autonumber (although this could be just how I imported them) apart from AddressID in Company which has no values in it and is not set up as an autonumber field.

This field should have the same value as AddressID in addresses, is that right? At the moment it is empty.

Thanks

Brad

 

[Brad108]

Hi

I am also getting this message when I try and add a record to the Contacts via the contacts subform on the Company page:

"the changes made were not successful because they would creat duplicate values in the index, primary key or relationship. remove key etc."

Does this help?

Thanks so much

Brad

 

[MarkK]

Brad:
1) All the primary keys in the relationship diagram should be bold, indicating that they are indexed and do not allow duplicates. Autonumber will have these properties by default.
2) Foreign keys related to autonumbers must be Number->Long Integer in table design, but this must be so already or Access would not allow the relation.
3) Relationships should not have arrows, but rather the PK should have a little "1" nearby, and foreign keys should have a little inifinity symbol. This indicates a one to many relationship. Don't worry that some of your relations are effectively one-to-one. Doesn't matter. Think of these as Parent-Child.
Deal with arrows later.
4) Now, any foreign key that belongs to a primary key simply requires that the numbers match. Note that many Contacts might have the same CompanyID? This is the classic one-to-many relationship.
5) Next we'll figure out how to get new child record to automatically figure out the PK they belong by linking your subforms. You'll love it.

More later, gotta get dental work. You're totally on the right track man.
Mark

 

[Brad108]

Thanks for all the help Mark

 

[Brad108]

Got those links set up ok, with all bits matching what you said.

Looking good.

Looking forward to 5 - to get the new child record to automatically figure out the PK they belong to by linking the subforms. This part sounds like it could solve my problems!

All the best and thanks again. Hope the dental work went ok.

Thanks

Brad

 

[Brad108]

Hi

Have tried some different settings, but they don't seem to have worked.

Think its probably best to wait until you're able to give me some advice regarding the linking of subforms etc.

Thanks again for all your help.

Brad

 

[MarkK]

Brad:
Here's a sample db that demonstrates where I'd go with what we've been talking about. Post back if things get dodgey.
Cheers,
Mark

 

[Brad108]

Hi Mark

Thanks so much for the sample database. I have now managed to match my table relationships and mother/child subform details to yours.

It’s still not working though – I must be doing something so wrong.

When I try and add an address via the company screen, it comes up with this error message:

‘cannot change or add a record because a related record is required in table Attendees’

And on the contact screen, when I add a contact, and assign it to a company name, the contact doesn’t appear on the company screen, under that company name.

I really just want it to work like your sample one, as in:

I can add a company or add a company address, via the company screen.
I can then go to the contact screen and assign a contact to that company, choosing the correct address for that contact from a drop down list.
Then the company screen shows all the contacts for each company.

I have spent so long trying to sort this out and am now really desperate to get it working.

I was wondering whether you could have a look at the attached database and see where I am going wrong. This is where I have got to with it. If you can help me out with this, I would SO appreciate it!

Note, although the field descriptions are correct, the Contacts table is actually called Attendees and the Company table is called Events.

Thanks again Mark. Hope to hear from you, as you’ll be a life saver!

Brad

 

[MarkK]

I recommend you put your address in the same record as the item to which it belongs. For the scope of your application and your level of experience I think you're making things way harder than they need to be.

Your 'can't change or add record' error occurs because you're enforcing referential integrity on your table relationships.

I'd make sure my data structures, table relationships, etc.. are working well in advance of developing my user interface. Notice how data structure changes now require a significant rework of interface elements? Doh!

And looking at what you have I'm not clear what the purpose is of your app. The switchboard advertises Contact and Company, which suggest to me different data and relationships than the tables named Events and Attendees.

Don't link your addresses. Just put them in the same record. The best solution is the simplest.

 

[Brad108]

Hi

Thanks for the feedback.

I originally started setting the database up as a conference listing database, but then decided that would be too complicated, which is why the tables are called events & attendees, but, aside from the names, everything else has worked towards a contact database.

When you say put the address in the same record as it belongs, what do you mean? Do you mean just having 2 tables, and not having a seperate address one?

I would like to make things simpler, but the database does need to be able to link more than one contact name to a company address, and then list the contacts (and there different addresses) under a company listing form.

I feel kinda that the database is almost there - is there a straighforward way I could get this linking working?

So that I can input addresses, link these addresses to a company and then link contacts to a company?

I really hope you can help me out on this. It so important I get this database finished soon.

Thanks so much. Really appreciate it!

Brad

 

[MarkK]

Brad:
If each contact has an address in its own record, and each contact is a child of a company, then its easy to get a list of contact addresses for a company. That's a very simple query. I'd go that route.

SELECT <address fields> FROM tContact WHERE CompanyID = <companyID>

I'd break out the address in a new table if I had suppliers, customers with billing and shipping addresses, employees, and contacts, all needing address info. In that case the overhead work of a separate "address system" becomes the simplest solution. Not so, I think, in your case.

I suggest you go this route...

[B]Company[/B]
CompanyID, PK
<Data Fields>
<Address Fields>

[B]Contact[/B]
ContactID, PK
CompanyID, FK
<Data Fields>
<Address Fields>

Doesn't that just look delightfully simple to you? Simple is good.
And always get your table structures solved and solid before working out the details of the user interface.
Cheers,
Mark