Subform / mainform but adding new entry

[Mr_Si]

I've been looking at Pat Hartman's sample db and am slowly understanding the many-many referencing with subforms on main forms being only do-able via queries.

Please can I ask a question that might appear slightly dumb, but please can I ask a question regarding my db? I've been doing a lot of re-design as mine wasn't working due to normalisation issues etc and a lot of my tables are now in 4 and 5NF because of many-many relationships.

With my contact information, I have the following:

tblContact
idsContactID (PK)
chrContactType (lookup to tblContactType)
chrContactFName
chrContactSName
chrContactCompany (lookup to tblcompany)
chrContactAddress1
chrContactAddress2
chrContactAddress3
chrContactTown
chrContactCounty
chrContactPCode
chrContactCountry
memContactComments
blnContactMailList
blnContactStopList


tblContactTelNoLink
lngzContactID (pk) - lookup to tblContact
lngzTelNoID (pk) - lookup to tblTelNo


tblTelNo
idsTelNoID (pk)
numTelNo
lngzTelNoType (lookup to tblTelNoType)


tblTelNoType
idsTelNoTypeID (pk)
chrTelNoType

So when doing the contact form, I have a subform controlled by a query and this is placed into the main contact form so that the numbers change when the contact changes.

When it comes to data entry, however, i notice that in Pat's version, some field controls are disabled.

If I enter a new contact into the db and I want to add telephone number details, would I just need to create a button to open a form which directly controls the tblTelNo table and add the info there, then go back to the contact form and select it from the combo box? Or would I open a form based on the tblContactTelNoLink table, so that it links the telephone number to the particular contact?

It's this bit that I don't get about many-many relationships.

I look forward to hearing your comments. I hope the above made some form of sense.

Thanks in advance,
Simon

 

[georgedwilkinson]

Hi Simon,

I think you might need to refine a couple of things.

The table tblContact is not normalized. You need to separate out things that have nothing to do with defining a contact (address information, for instance).

I think the reason that some things are not updatable in Pat's sample is because they are purposefully read only to further define the artificial FK in the junction table (I haven't looked at the sample lately).

If you want to modify those data fields, you should create a main form that is "upside down" from the current form/subform. For instance, if your telephone number is currently in the main form and you are displaying data about all the potential contacts who share that phone number in the subform, you would need to create a main form based on tblContacts in order to modify the data about a contact. The junction table's sole purpose is to create a M:M relationship.

 

[Mr_Si]

yes, I did wonder about that. So I'd need to separate a new table called tblAddress. Yeah, that figures. Argh, stupid normalisation.

That aside, with regard to adding / editing a phone number, I would make a form: frmTelNo and in it, have a subform: fsubContact and I would enter a phone number in the main form and then select a contact in the subform who would us that phone.

Shame it seems so complicated!

What I was hoping was to be able to click on a button called "Add Tel / Fax" in the frmContact where the subform fsubTelNo exists. A little form would then pop up, I'd add the phone number and then click "Save" or "close" or something, it'd requery the subform, and then it would appear in the list of available phone numbers in subform of telephone numbers.

Is that at all possible? or would I have to do it the longwinded way of having an upside down form?

Thanks,

Si

 

[georgedwilkinson]

Well, since your tables SEEM to be 1:1, you should be able to do that on the subform. That's an aspect of your design you may want to look at.

But even with that, if you can base the subform on an updatable query, you should be able to go without all the clicking and opening and closing and stuff.

Another possibility is to look at the "not in list" event of a combo-box. There, you can define how to handle entering a record that is not on one of your "end" tables. No need for a seperate button to do this.

 

[Mr_Si]

Updatable queries, I'll have to research them...

Which tables seem 1:1 to you?

I was looking at the "not in list" event earlier on today - I can only find data on adding a new record to a single field. Hmmm more digging I think. I might play with this.

Thanks for the replies George, as always

 

[georgedwilkinson]

Which tables seem 1:1 to you?

I was totally off base on that comment. I re-evaluated and withdraw the comment.

I was looking at the "not in list" event earlier on today - I can only find data on adding a new record to a single field. Hmmm more digging I think. I might play with this.

Go wild with your imagination. You know you can do pretty much anything you want during an event, including bringing up another form (for instance to add data to a different table).

 

[Mr_Si]

I guess I was just wondering how I'd reference things and make it requery, but I think I know how to do it. I need to make sure that when i open a new form to enter the telephone number, it opens with reference to the correct contactID so that it'll then appear after a requery.

I've heard that opening a form in acDialog mode does this...?

I think I shall keep using the button method if I know my users, they seem like the type who like to click buttons. But I think just for good measure, I'll make it work via the not in list event too.

So much to do!!! (I'm slow at doing it)