What's the best way to handle categories?

[thenextguy]

I'm developing a contact database and I'd like to have multi-select listbox (on a subform) on my forms to select which categories they belong to (friends, business contacts, christmas card mailing list, etc.).

I have three tables to accomplish this.
tblContact
tblCategoryType
tblCategoryDetail

...where tblCategoryDetail acts as the bridge to create the many-to-many relationship between tblContact & tblCategoryType.

My question is how can I pass the data selected in the listbox to the tblcategorydetail table (or from the tblCategoryDetail to the listbox when the record changes on the main form).

Right now I'm planning on using a query to populate the listbox everytime the record changes on the main form, and then using a delete and/or update query everytime the listbox is changed or when the form is saved.

This seems like a rather complicated way to do it. Are there any better ways?

Thanks for reading and to anyone who might offer suggestions.

 

[Andrew Fergus]

Do you really need the many-to-many relationship? I realise that in some instances one person could belong to many 'categories' but realistically, how many? Most categories will be mutually exclusive and something like a 'Christmas Card List' could be stored as a yes/no box in tblContact. I don't think this is the answer you are seeking but it will simplify your forms and queries.

However, in answer to this question :

My question is how can I pass the data selected in the listbox to the tblcategorydetail table (or from the tblCategoryDetail to the listbox when the record changes on the main form).

base the subform (or form depending on how you have set it up) on a query that is based on tblCategoryDetail - you can still use combo boxes but the results of the selection would be stored in the query / tblCategoryDetail. And to re-populate or refresh a drop down list then you need to do a 'requery' after a new record is added.

HTH, Andrew.

 

[SydneyGeek]

You can do it using a subform based on tblCategoryDetails. Here's how:

1. Fields for tblCategories --
CategoryID [Autonumber] [Primary key]
Category [Text]

2. Fields for tblContacts --
ContactID [Autonumber] [Primary key]
ContactName [Text]
[and other fields as required]

3. Fields for tblCategoryDetails --
ContactID [Number]
CategoryID [Number]
[make the above 2 fields into a compound Primary Key by selecting both in Design view, and clicking the Key icon on the toolbar]

4. fsubCategoryDetails form --
Create an Autoform from tblCategoryDetails. Switch to Design view and it Properties, Change Default View to Datasheet.
Right - click CategoryID and Change To | Combo Box.
Set up the combo as follows:
Row Source is SELECT tblCategories.CategoryID, tblCategories.Category FROM tblCategories;
Column Count is 2
Column Widths are 0,3
Leave Bound Column as 1
Switch to Datasheet view, select ContactID and View | Hide.
Save the subform as fsubCategoryDetails, and close it.

5. Connecting to frmContacts --
Open frmContacts in Design view
{F11} to bring database container to front
Drag the fsubCategoryDetails form onto frmContacts, let go.
Save frmContacts, switch to Datasheet view.
To include a contact in a category, select the category from the combo box. Because ContactID and CategoryID are a compound primary key, duplicates won't be permitted.
Resize the subform to fit, once you're happy that it works

HTH
Denis

 

[thenextguy]

andrew93

Thanks for your response. The explanation I gave with simplified a bit for illustration purposes. There are many more categories and nearly all of the contacts will belong to multiple categories.

SydneyGeek
And thank you for your response. I did some research and the method you describe seems to be the preferred way to have Access handle a many-to-many relationship (ex. sales orders to items ordered).

I guess my issue with this method is that I'd like for all of my categories to be displayed on the form and to just be able to check or uncheck something (listbox, check box, etc.) to add or remove a person from a category.

Do you know of any ways to do this? It may be that the only way to do something like this is the way I described in my original post. I've poked around online and through some Access books quite a bit, so I might just be SOL, but I thought I'd run it by here in case anyone has ever done something like this.

 

[SydneyGeek]

Finally back to this -- Easter intervened. If you want to always have all categories for each contact, here's one method that builds on what I posted previously.
Additions:
tblCategoryDetail needs a new field -- GroupMember, Yes/No data type. You'll tick these to indicate membership of any group.
fsubCategoryDetail needs the new field added to the subform.

2 new queries --
qryMember,
SELECT tblCategoryDetail.CategoryID, tblCategoryDetail.ContactID
FROM tblCategoryDetail
WHERE (((tblCategoryDetail.ContactID)=[Forms]![frmContacts]![ContactID]));

qryAddToMember,
SELECT tblCategories.CategoryID
FROM qryMember RIGHT JOIN tblCategories ON qryMember.CategoryID=tblCategories.CategoryID
WHERE (((qryMember.CategoryID) Is Null));

1 new module --
basAddMembers, with this function:

Function WriteCategories()
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  Dim rst2 As DAO.Recordset
  Dim prm As DAO.Parameter
  
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs("qryAddToMember")
  For Each prm In qdf.Parameters
    prm.Value = CInt(Eval(prm.Name))
  Next prm
  Set rst = qdf.OpenRecordset
  Set rst2 = dbs.OpenRecordset("tblCategoryDetail")
  With rst
    .MoveFirst
    Do Until .EOF
      With rst2
        .AddNew
        ![CategoryID] = rst![CategoryID]
        ![ContactID] = Forms![frmContacts]![ContactID]
        .Update
      End With
      .MoveNext
    Loop
  End With
  
  rst.Close
  rst2.Close
End Function

frmContacts needs a new Command Button called cmdCategory, with this code behind it:

Private Sub cmdCategory_Click()
  basAddMembers.WriteCategories
  [tblCategoryDetail].Requery
End Sub

To add this code to the form, right-click the button to select Properties. Click the Events tab. Double-click the blank On Click line. Click the Builder (...) button. Paste the 2 lines of code into the empty subroutine. Save and close the form.
To run, click the button. The contact gets a full set of categories. Only those that were MISSING previously are added.

Hope this does the trick ---

Denis

 

[thenextguy]

Thanks, SydneyGeek!

I will let you know how it turns out when I get a chance to play with it.