Updating Third (Linked) Table 1

[KerryL]

I'm trying to do this application the right way, but I'm confused about how to update a separate table with info from two different ones.

When I create a new record in tblContacts, I select an organization category from tblCategory. For sorting and mail merging capabilities, I want to update a third table (tblContactCats) with the ContactID (from tblContacts) and the CategoryID (from tblCategories) but I can't seem to get it to work.

On the form I use to create new contacts, I created a cbo using tblCategories.CategoryName as the record source (CatID = key field) but all that does is populate that field with the Category Name. Which is fine, but I also need to create a corresponding record in tblContactCats that lists the ContactID & CategoryID for each of the categories the contact is associated with. (There could be many.)

Do I need to insert an expandable sub-form into the "Add New Contact" form in order to accomplish this?

Thanks in advance,
KerryL

 

[dhookom]

You shouldn't have a field in your contacts table for CategoryID. Create a subform based on tblContactCats. Add a combo box as the only control on the subform and set its properties:
Name: cboCatID
Row Source: SELECT CategoryID, CategoryName FROM tblCategories ORDER BY CategoryName;
Column Count: 2
Bound Column: 1
Column Widths: 0",1"
Add the subform to the Contacts form. Set the Link Master Child properties to ContactID.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

I've created the subform, added the cbo and pasted the subform into the Contacts form. I can select one category for each contact (need to fix that) but when I save the record, tblContactCats doesn't get updated with the matching ContactID & CategoryID for that record.

 

[dhookom]

Did you set the Linke Master Child properties of the subform?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Yes, the sfrmContactCats properties are:

Record Source = tblContactCats
Link Child Fields = ContactID
Link Master Fields = ContactID


cboCatID (on sfrmContactCats) properties are:

Name = cboCatID
Row Source = SELECT [tblCategories].[Category ID], [tblCategories].[Category Name] FROM tblCategories ORDER BY [tblCategories].[Category Name];
Column Count = 2
Column Widths = 0";1"
Bound Column = 1

There is no code behind the "AfterUpdate" property. Shouldn't the selection of a category in cboCatID force tblContactCats to be updated with the corresponding ContactID and CategoryID?

 

[KerryL]

Duane, I have a question for you.

Since all I'm going to be using this database for is to create mailing labels, mail merges, name tags, etc., can I just put all the info in one table?

For instance, the table will include the contact's name, address, city, st, zip, etc. That's standard info.

Could I include the category info of each contact in the same table by simply adding a Y/N column for each of the categories? Then the user could simply click the box of the category(s) each contact belongs to.

The question is, will I be able to query the information in the same way? How would I query the table for more than one category (all records with Y in CatA or CatD)?

Or would putting everything into one table make querying inefficient and slower? (The table will contain approx 10K records.)

 

[dhookom]

You could create a "spreadsheet" table but I wouldn't. The method that I suggested is similar to the Orders and Order Details tables in Northwind. A Contact is similar to an Order and the Categories are similar to the Products in the Order Details. Setting your tables up like this means that you would never add a new field or change a form/query/report if you have to add a new category.

If you have the subform embedded in the main form and the link master/child properties set, then your ContactID should be automatically updated in all records added in your subform.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Thank you. That was my original concern; that I wouldn't be able to make it possible for users to add or change categories without me having to go into the table to do it.

As for the embedded subform, I can't get tblContactCats to be updated by the data in the subform. I'm sure I have all the properties set as you suggested (see earlier post).

Perhaps when I created the cbo I clicked on the box that saves the data for later use instead of selecting the option to save the data to a table? I'll check.

Thanks again.

 

[dhookom]

The embedded subform must have a Record Source that includes tblContactCats. What is the Control Source of cboCatID? It should be CategoryID.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Duane,
Just wanted to let you know that I was able to figure out the problem. The cbo I created (cboCatID) was not saving the data to tblContactCats because of how I created the cbo.

I was able to fix it, and now when a category is selected in the subform, the contact table is updated with the person's info while tblContactCats is updated with an ID (autonumber), the Contact ID and the Category ID.

When I get all the forms finished I may have a question or two again regarding your QBF Applet that I'm using with this database. Hope you don't mind.

Thank you for your help; I really appreciate your patience with me as I try to do this the correct way.

Kerry