I have a table of contacts, a table of categories and a junction table that relates both (so I can have multiple contacts related to multiple categories). However, on the form that goes to the junction table, if the user wants to associate the contact with more than one categories, he would have to create more than one record and select more the contact name more than once correct? If so, here is my question, when the user wants to associate the contact with all the categories, if would become tedious and troublesome to create so many records and select the same contact again and again. Therefore I would like to create a check box in the form that goes into the juction table, and by clicking that check box, the user associates the contact with all of the categories, so in the future when the user wants to send a product to the contacts of one category, the name will be there for him/her to see with their contact information. Since I have not worked a lot with VBA I'm not too sure how exactly I can achieve this, can some one show me how exactly I should write the code? (Or if there is an easier way to do so as long as the contact name can be associated with all of the categories by one click). Thanks!

op66fantasie,

Here's one strategy, though I don't think it's necessarily the best one.

You might want to create a special category called "All" and link those special contacts to this category. One advantage is that your special contacts would be included even if you add new categories to your category table down the road.

This would happen because your queries would need to be designed to include the "All" category along with whatever's already been selected to be included with the query. For example:

WHERE (strCategory = "Salesman") OR (strCategory = "All")

The downside: if even ONE of your categories is an exception to this rule - where an "All" contact would NOT be included - this is not the way to go. In this case, it just makes more sense to create five records associated with a contact when you have five categories. It may seem tedious to do this, but it would NOT be troublesome. Besides, once you write some VBA to handle creating the records if the user clicks on a "Select All" command button, the tedious bit is handled by the computer, not the user - and computers are really good at tedious tasks - it's what they are best at.

I'll put together some code for a "Select All" button for you, it's not too bad.

Hope this helps,

Dennis

So, here's how you can create a record in your junction table for each category when the user presses a "Select All" command button.

First, here's the basic table structure I'm assuming you're using:

tblContacts
ContactID
strFirstName
strLastName

tblCategories
CategoryID
strCategoryName

tblContactCategories
ContactCategoryID
intContactID
intCategoryID

Private Sub cmdSelectAll_Click()

strSQL = "INSERT INTO tblContactCategories ( intContactID, intCategoryID ) " & _
"SELECT " & Me.ContactID & " AS ThisContact, tblCategories.CategoryID " & _
"FROM tblCategories;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

That's it. It's just a customized APPEND query that inserts the form's ContactID value into the SQL statement before running. Basically, for every record in the tblCategories table, a new record will be created in the tblContactCategories junction table. Each of those records will include the ContactID value from the form's current record.

Hope this helps,

Dennis

Hi Dennis,

That helps a lot. I have one question though. Would it be better to use a check box for 'Select All Categories' rather than a button? The thing is if the user wishes to undo that selection that button/function would not help right? So I was thinking a check box would work better (so when the user checks it all categories are selected and records created, but when its unchecked the associated records are deleted?). I don't have a problem with using a button, its just there has to be a way for the user to undo the select all and the records be deleted. I am assuming that involves more code, if so could you let me know how exactly I should write that? Thanks!

Brian

Brian,

You didn't mention the ability to "undo" the Select All action in your original post.

It doesn't really matter whether the control is a checkbox, a toggle button or a command button. The code will essentially be the same. To enable the "undo" ability, you'd have to write some VBA to count how many records are associated with the current contact, using the DCount function. If it's the same as the number of categories, then all records have been selected. In that case, you'd run the exact same Update query, only with "FALSE" in the place where it says "TRUE".

The only difference would be that you could check the state of a toggle or checkbox to decide whether to set all records to TRUE or FALSE. But the danger is this: What if the user selects ALL the records, then un-checks a few people because they wanted to check "most" of them? And then they want to check them all after all, so they click the "Select All" control again, only this time it un-checks the records. That's why I'd recommend counting the associated records in the junction table, rather than depending on a two-state control on the form.

Hope this helps,

Dennis

Ok, so I create a new query and use the WHERE (strCategory = "Salesman") OR (strCategory = "All")? That makes sense, just change the strCategory the field name I have in my table right? Is there something like a SELECT before that? And where exactly to I put the line in the query? And thanks for explaining, I haven't used access much.

op66fantasie,

Sorry, that was an incredibly bad post on my part. I'm apparently trying to do too many things at once this morning. (IMG:style_emoticons/default/crazy.gif)

Okay, when you want to DE-select a set of records from the Junction table, you'd use a DELETE query instead of an INSERT query. So, after determining that there are, say, eleven categories and eleven records related to the form's current ContactID value, you'd run this:

strSQL = "DELETE tblContactCategories.intContactID " & _
"FROM tblContactCategories " & _
"WHERE tblContactCategories.intContactID=" & Me.ContactID & ";"

By the way, this whole scenario is based around the idea of NOT having an "All" category - just a "Select All" control on the form.

Hope this helps,

Dennis

Hi Dennis,

Sorry if I confused you a bit, but the question on my last post was actually regarding to your first post where you said I can just create a special group named 'all categories' and put that in the category table so it can be an option for users to select. And then you said I can use something like WHERE (strCategory = "Salesman") OR (strCategory = "All") in a query too see how many people are in what categories. Thats looks fairly simple and would solve the problem, so I decide to try that before working with the codes.

Now back to the question I had, so I create a new query and the purpose of that query is to show me how many people are in one category + all categories, using some expression like WHERE (strCategory = "Salesman") OR (strCategory = "All"). The only thing is that where exactly to I put the line in the query? Is there something like a SELECT before that?

Thanks again (IMG:style_emoticons/default/smile.gif)

op66fantasie,

I see.

The best thing to do would be to create a practical query that you're actually going to use. Then, go into the SQL view of your query. You'll find a line of text that reads "WHERE fieldname = 'Salesman'". What you'd do is change it to "WHERE fieldname = 'Salesman' OR fieldname = 'All'".

If you're more comfortable working on the query grid, you'd have 'Salesman' in Criteria slot. You'd place 'All' in the spot just below 'Salesman' (you'll notice that the row heading is "or:").

Both of those accomplish the same thing.

Hope this helps,

Dennis

OK, thanks a lot!