Access coding for forms

[LittleJess]

Hi,
I am trying to create a contacts database I have created a form with personal details such as Name, Tel, Fax etc and on this form I have included a subform with Location (Address 1, 2, 3 Postcode etc) In order to create 1:M instead of M:M (People may have many locations and locations will have many people)relationships between the tables I have created an in-between table containing just PeopleID and LocationID.
I am having real problems working out how to send the people ID and the Location ID from the form to the In-Between table so as to create a link. I have tried cmd.RunSQL and various statements but am having no luck. I have also tried some coding but have hit a proverbial brick wall.
I would greatly appreciate anything anyone could suggest to populate this linking table.
Thanks in advance.

 

[psemianonymous]

I'd recommend you do this the denormalized way and just add the address to the contact table. This is the simplest solution--and what is the price you're paying for this simplicity? Denormalization.


If you really have to use the intermediary table, then this is how it's done:
1. You have your main form where you enter Name/Tel/Fax info. This I assume is working.
2. You have the part where you enter address information -- of course you'll have to check for already-existing addresses, because of the way everything is set up. I assume you could put this as a subform to the main form, not directly linked.

3. When you click on "Save" on the main form, you need to add an SQL statement to the AfterUpdate() event of the main form. In this event, attempt to save a new record in your intermediary table with the current PeopleID (from the main form) and the current LocationID (from the subform).


This process should work. Of course you could simplify everything by just having one step: move all address info to contacts/People table.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[LittleJess]

Thank-you foolio12.
I would have put them in the same table except that some of the people will have mutiple locations which would create problems. The database will also have the potential to be very large and doing it De-Normalised may cause problems.
I'll try what you said with the AfterUpdate() SQL and hope that works
Thanks again

 

[psemianonymous]

I've been thinking -- and I don't like my original answer. Everything's fine except for the event stuff. I'd recommend you put the "add person/location entry" SQL in the ID control's AfterUpdate() event. This way, if someone changes the person's fax number, it won't try and file a new Person/Location entry. But if they change the ID (hopefully an autonumber to keep it simple), this means that they're adding a new Person.

Also you need to add an event to the subform when you add a new location. On the LocationID's AfterUpdate() event, do the same thing.


Maybe instead of all this event-based stuff, you might want to have the users manually click on "link" to link the current Person to the current Location. That would definitely be the simplest way.




--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[LittleJess]

Hi Again,
Have you any ideas on the SQL statement I should be using ?
I have been trying INSERT INTO and SELECT INTO. But neither of these ahve the desired effect and i can't seem to find the statement to help.
anything would be greatly appreciated.
THANKS

 

[danvlas]

How about a main form based on Contacts and a subform based on the 'bridge' table. The LocationID in the subform could be looked up from the Locations table (a combo box sounds OK). And the ContactID should be the link between the main form and the subform.
And you won't need any coding or fancy SQL...

But you should place an ID field in the 'bridge' table, maybe some notes...

I have to disagree with foolio, denormalization is (IMHO) always a bad idea...integrity first, speed after that, but I admit that's arguable.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[LittleJess]

That sounds good to me,I'll give it a try.
Thanks