Format my multiselect listbox access form to show checkboxes, not just choices

I have a form that displays Contacts, and I want the user to be able to select whatever Department(s) the contact works in.  On the Contact form, I have a listbox that lists all Departments available (from a Table named Departments).  The Departments all appear, and I can select multiple Departments no problem by holding the Shift key and clicking on all that I want to select. They assign the correct values to the Department field in the table.

BUT, I would like to offer the user a checkbox in front of each department that s/he can click instead of the method I described above.  I have another form in the same database (named Companies) and the checkboxes appear fine for all "Industries" that may apply.  I have compared the two forms to try and see why one offers the checkboxes and the other doesn't, but I can't find out what the difference is.

What settings on my Contacts form should I set so that the user can use a checkbox to select multiple vales instead of using Shift+Click ?
KevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
instead of using a listbox, use a subform.

am assuming you have tables something like this:

Contacts
- ContactID, AutoNumber, PK (Primary Key)

Departments
- DeptID, AutoNumber, PK (Primary Key)
- DeptName, text

ContactDepartments
- ContDeptID, AutoNumber, PK (Primary Key)
- ContactID, Long Integer, FK (Foreign Key to Contacts)
- DeptID, Long Integer, FK (Foreign Key to Departments)
- etc, like Role

create a Cartesian Query (no join) between Departments.  Call this qAllContactsDepartments.

Then make another query using qAllContactsDepartments joining on ContactDepartments so all records from qAllContactsDepartments show.  LinkMasterFields and LinkChildFields will be ContactID if the mainform is based on Contacts, or DeptID if the mainform is based on Departments.

create a calculated field like

IIf(IsNull(ContactDepartments.[ContDeptID] ),False,True) AS IsUsed

While the subform will not be updateable, you can handle a CLICK procedure on the checkbox to add or remove a record in ContactDepartments.

Once you have the subform working (except actually changing data), post back for what to do next
0
Dale FyeCommented:
I totally agree with Crystal, if you want checkboxes, you should use either a continuous subform or a datasheet subform.

But to get back to your original question.  The difference between the two forms is likely that in one of the tables, you have created a "Multi-value" data field.  This is a bad idea because these types of fields are extremely difficult to query or generate reports upon.

To do this, you have to open your table in design view, go to the Lookup tab (also a bad idea on the part of Microsoft), and then change the Allow Multiple Values property to Yes.  This creates a hidden table in the background which you cannot see and cannot query.  Instead of this technique, if you need a one-to-many relationship, you should create your own table to store these multiple values, which you can control and query properly.
0
KevinAuthor Commented:
Thank you both.  I will be back at my laptop tonight (in about 10 hours) and will start making those changes.  I will get back after I try that...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KevinAuthor Commented:
I am rereading this and trting to implement the steps.  I am not sure what the ContactDepartments table is for.  All I need to do is know what Department people work in.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
It seems you want to pick multiple values for department for each contact. The best way to store multiple values is in another table.  ContactDepartments is the table that stores the combinations of Contact and Department.

If people will only have ONE department, then DeptID can be in Contacts and you do not need a related table.

If people will have one dept at a time and you want to track past departments and time ranges, those records could be in an archive table.
0
KevinAuthor Commented:
OK - thanks for helping me with some baby steps.  I can see that this is way beneath your pay grade!

I have the three tables made.
I have a query to show all Departments in that table.

Now I am at the step:

Then make another query using qAllContactsDepartments joining on ContactDepartments so all records from qAllContactsDepartments show.  LinkMasterFields and LinkChildFields will be ContactID if the mainform is based on Contacts, or DeptID if the mainform is based on Departments.

create a calculated field like

IIf(IsNull(ContactDepartments.[ContDeptID] ),False,True) AS IsUsed

Is that the query that will be on my subform?  I am not sure how to do this...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

first, make a cartesian query.  This shows all the combinations of Contacts and Departments.

Naturally, you won't usually want to see them all ... you will only want to see all the combinations for a single contact (or a single department).

So we create another new query using the cartesian and pull in whatever other table we are really interested in ... like ContactDepartments.  There are records there with existing combinations that have been identified, but not ones that haven't.  

So make another new query.  It will use 2 sources:
ContactsDepartments table
qAllContactsDepartments query (the cartesian)

it will also have a calculated field to indicate if there is a record in ContactDepartments for the combination in the cartesian.  If it is there, then IsUsed is set to true.  If is does not yet exists, there will be no records in ContactDepartments for the combination and IsUsed will be false.

Bu default, when you use 2 tables that are joined, Access defaults to an INNER JOIN, which only shows you records that are in each table.  This join type gets changed to show all records from the cartesian and only records from ContactDepartments where there is a value.

ContactID and DeptID will be put on the grid from the cartesian

create a calculated field:
IsUsed: IIf(IsNull(ContactDepartments.[ContDeptID] ),False,True) 

Open in new window

 Add the primary key from ContactDepartments (ContDeptID)

You will probably want to show the department name too, so you would add the departments table to the query for the form RecordSource (which we haven't assigned yet -- still lining up the data) and put the field with department name on the grid too.

see how far you get with this and then we'll continue ...
0
Dale FyeCommented:
In case you are not familiar with a Cartesian query, it involves every combination of records from two or more tables.  The query would look like:

SELECT Contacts.SomeField, Departments.SomeOtherField
FROM Contacts, Departments

So if there were 10 records in Contacts and 10 records in Departments, then the Cartesian join would contain 100 records.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hopefully you can take over now, Dale, I have a training call ...
0
KevinAuthor Commented:
I think that I am up to speed now.  The first query has 51,606 results (made up of 5734 Contacts and 9 Departments) and the second query has zero results, because I have not added any Departments to any contacts...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the second query should also have results -- you didn't set the join to show everything from the cartesian ...

right-click on the join line in the query design to set it join type
join on ContactID if you will use this as a subform for contacts
0
KevinAuthor Commented:
I drew a line between the two tables in the second query, When I click on the properties of the line, I chose #1 "Only include rows where the joined fields from both tables are equal".

When I run this second query, there are still no results...

Design view of second queryDesign view of second query
0
KevinAuthor Commented:
Okay, I think that I have both queries made OK.  I also made a form named Departments, and have inserted it into the Contacts form.  I am not sure now what to say the source of the subform is, how to get the checkboxes, etc. Thanks again for helping me with small steps!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
great! ... and you're welcome

What is the SQL for the 2nd query?

To see that, go to the design view of the query, then change to SQL view

You might want to leave the departments form there as well as a subform, and not link it so you have an easy way to see and edit departments.  Changing that form will not be the best way to create the next one.
0
KevinAuthor Commented:
My second query is:

SELECT ContactDepartments.ContDeptID, ContactDepartments.ContactID, ContactDepartments.DeptID, qAllContactsDepartments.ContactID, qAllContactsDepartments.Department, IIf(IsNull([ContactDepartments].[ContDeptID]),False,True) AS IsUsed
FROM qAllContactsDepartments LEFT JOIN ContactDepartments ON qAllContactsDepartments.ContactID = ContactDepartments.ContactID;

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Good! I formatted the SQL you pasted so it is easier to read, rearranged columns, and modified it.  Do not get a field with the same name from more than one table unless you have a good reason to do so.
SELECT IIf(IsNull([ContactDepartments].[ContDeptID]),False,True) AS IsUsed 
 	, qAllContactsDepartments.DeptID
 	, qAllContactsDepartments.ContactID
 	, qAllContactsDepartments.Department
 	, ContactDepartments.ContDeptID
 FROM qAllContactsDepartments 
 	LEFT JOIN ContactDepartments 
 		ON qAllContactsDepartments.ContactID = ContactDepartments.ContactID;

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Using this (2nd) query, make a form.

1. Close the query if it is open.

2. Select this new query in the Navigation pane.
(one click only, just to highlight it)

3. Click on the Create ribbon

4. in the Forms group, choose More Forms > Multiple Items

5. save this form as: f_All_ContactDepartments_sub
0
KevinAuthor Commented:
Thanks again.  When I try to create the form, it prompts me for the value of:

qAllContactsDepartments.DeptID
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome  

> "qAllContactsDepartments.DeptID "

Close everything.

go to the design view of qAllContactsDepartments and add DeptID.  Save the query.

--  have training call so I'll be gone for awhile ~ see what you can figure out by yourself.

Hint -- use the CLICK event of the checkbox to add a record to the ContactDepartments table using SQL if the box is unchecked when it runs ~ then refresh the form and the box should appear checked.

If it is already checked, then run SQL to delete that record.

Be sure that you have a UNIQUE index set on the combination of DeptID and ContactID (whatever you call those fields) in the ContactDepartments table.

The PK in that table should be an auto number ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
once you have the form that will be used as the subform done (enough), go to the design view of your contacts form.

drag the subform onto it from the Navigation Pane.

Set the Name property of the subform conctrol = the Source Object property (f_All_ContactDepartments_sub)

Set LinkMasterFields and LinkChidlFields (DATA tab of property sheet) for the subform control to --> ContactID

on the Current event of the contacts main form:
me.f_All_ContactDepartments_sub.form.requery

Open in new window

~~~~~~~~~ Add Option Explicit ~~~~~~~~~

if the top of your module does not have a statement that says Option Explicit, then add this:

Option Explicit  ' require variable declaration

Open in new window


~~~~~~~~~ Compile ~~~~~~~~~
 
Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing.
 
from the menu in a VBE (module) window: Debug, Compile
(Alt-F11 to switch to the code window)
 
Fix any errors on the yellow highlighted lines.
Add needed references and remove missing references if necessary
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save


also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up
0
KevinAuthor Commented:
I am getting close - thanks!  A couple of things..

1.  When I made the form f_All_ContactDepartments_sub and look at it, the Finance Department shows up repeated in the textbox - I cannot see why that would be.
Form for Departments (Finance Department is repeated)
2.  How do I add checkboxes on the form that go along with each record in the form?  When I used the wizard, no checkboxes appeared on the form.  Once I get my checkboxes, I will try to follow your other instructions...
0
Dale FyeCommented:
Crystal,

You have done so much of the work on this, I'm going to bow out for now.
0
KevinAuthor Commented:
Well, I poked around a bit in the departments table and CHANGED the Finance Department to the "WHy is this Department repeated" Department, and I ADDED a Finance Department.  Now BOTH those Departments appear...
Twp Departments appear in one box
0
KevinAuthor Commented:
Thank you Dale!  You help is certainly appreciated.  I have a feeling that you will have many more opportunities as I get deeper into this project :)

Kevin
0
KevinAuthor Commented:
I noticed that for some reason the Primary Key in the Departments table was no longer there.  I fixed it and now there is no duplication of Departments on the form.  So my next step would be how to put a checkbox on the form that is associated with each record...
0
Dale FyeCommented:
@girbeaud,

Ok, so I didn't fully bow out, but I will be out of the office all day today, so I would stick with Crystal and see what she has to say.

The way I generally do it is to create a temporary table that contains an integer field (I usually call this [IsChecked]);  you can use Yes/No, but I prefer integer.  Then I would also add two additional fields and fill them with the values of the PKs from your two tables (ContactID, DeptID).  This gives you the ability to join the temp table to the query and have the [IsChecked] field updateable.
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the ContactDepartments table should have a UNIQUE INDEX on the combination of ContactID and DeptID so they cannot be duplicated.

When LinkMasterFields and LinkChildFields for the subform control are set to ContactID, this will eliminate records for other contacts.

When the subform is requeried, records will show up for the current contact displayed on the main form.

The checkbox is done with the calculated control (IsUsed).

It is not editable -- but does have a click event, which can be used to add or delete the related record in ContactDepartments using SQL.

Is it possible for you to make a blank database and import the relevant objects?  For tables, import definition only and then create a few sample records in Contacts and Departments to use for testing.

thanks
0
KevinAuthor Commented:
I dragged the form f_All_ContactDepart,ment_sub onto my Contact form.  When I go to assign the master and Child fields, I set them to the following - hope that is correct

SubForm
When I go to the "Form View" I see the word Finance 9 times (I have 9 Departments), so I think that I am close but no cigar (yet)

Contact Form showing SubForm
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the second query has ContactID twice ... it should only be there once, from the cartesian query.  After you fix that, post the SQL statements for
1. the cartesian query
2. the second query

and tell me the name of each

thanks
0
KevinAuthor Commented:
The first query is named qAllContactsDepartments:

SELECT Contacts.ContactID, Departments.Department, Departments.DeptID
FROM Departments, Contacts
ORDER BY Departments.Department;

Open in new window


The second query id named qContactsWIthDepartments:

SELECT qAllContactsDepartments.Department, 
IIf(IsNull([ContactDepartments].[ContDeptID]),False,True) AS IsUsed, 
ContactDepartments.ContactID
FROM qAllContactsDepartments 
LEFT JOIN ContactDepartments 
ON qAllContactsDepartments.ContactID = ContactDepartments.ContactID
ORDER BY qAllContactsDepartments.Department;

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the second query should be:
SELECT q.Department, 
IIf(IsNull([ContactDepartments].[ContDeptID]),False,True) AS IsUsed, 
q.ContactID,
q.DeptID
FROM qAllContactsDepartments as q
LEFT JOIN ContactDepartments 
ON q.ContactID = ContactDepartments.ContactID AND q.DeptID = ContactDepartments.DeptID 
ORDER BY q.Department;

Open in new window

 ... not sure the OrderBy clause will be taken into account with how it will be used

Make sure LinkMasterFields and LinkChildFields say ContactID

Make sure ContactID is on both the mainform and the subform (Visible can be No) and the control Name on each is also ContactID.

Also added ALIAS of q for the query to make it easier to read ...

the only reason ContactDepartments is needed is to see if it has a record.  The subform RecordSource will not be updateable -- that is ok.  Let's get it displaying properly and then code can be written to add or remove a record from ContactDepartments
0
KevinAuthor Commented:
Never mind - I misspelled the subform...  Moving forward...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi girbeaud (what is your name?)

great!  To make it easier on yourself, set the Name property of the subform control to be the same as the name of the object in the SourceObject property

So also on the Data Tab are LinkMasterFields and LinkChildFields.

These are all data properties set for the container that holds the subform.    This is the subform control itself ... where Left, Top, Width, Height, and other properties that describe the container are also set.

The second click on a container control gets you INTO what is in the container (subform) ... the form itself.  Presumeable changing things in the design like this, from the main form, is the same as editing that subform directly from the navigation pane (which is my preferred way to do it).

If you are changing several things on the subform, then close the main form and edit the form that is being used as a subform directly.  When you change important properties such as where its data comes from (RecordSource) and what controls are bound to (ControlSource), it is a good idea to edit directly, in my opinion.

When you are changing things about how the form being used as a subform is connected to the main form, this is set for the subform control on the main form so these changes, of course, must be made in the design view of the main form.
0
KevinAuthor Commented:
Ok - I think that I am close.  But for some reason now, the "Operations" department appears twice for every contact and I don't see scrollbars to look at all nine Departments...
 Contact Form
0
KevinAuthor Commented:
OK - I checked my Table and Operations was there twice,  So now it looks good to go.  What do I do to tie a checkbox to "Department" so that the user can click to add or delete from the table?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in the time I wrote this, you saw it yourself ... great!

______________________________
Check the Departments table ... Operations must be in there twice.

Set a UNIQUE INDEX on Department in the Departments table

1. Go to the design view of the Departments table
2. select the Department field
3. set the SIZE property to be something reasonable, like 20, 30 or 50 -- give it thought
4. set the INDEXED property to be Yes (No Duplicates)
5. Save the table

If Access won't save, that means you have records that violate the index.  Fix that and try again ...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you also have a record with no department name.  If you want to keep that

1. set the Index property "Ignore Nulls" to true (lightening bolt icon to turn on Indexes window)
2. filter out Null departments in the cartesian
     after the FROM clause  (including join stuff)
WHERE (qAllContactsDepartments.Department) Is Not Null

Open in new window

0
KevinAuthor Commented:
My name is Kevin. (my first cat was named Girbeaud, because the lady that sold it to us told her daughter if she bathed the kitten and made it look really nice and we bought it, the daughter would get a new pair of Girbeaud jeans...)  Anyway, here is how my form looks.  I just added an empty checkbox in front of each Department so that the user could click something.  I am guessing that when the form loads, it would need to check each isUsed value to see if it show should the checkbox as checked or not?

Here is what it looks like now...

Contact form with checkboxes
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Kevin, is the checkbox bound to IsUsed?  Is there a true or false value for every isUsed when you go to the Datsheet view of the (sub) form RecordSource in the builder?
0
KevinAuthor Commented:
What I did in the IsUsed expression in the second query was set the Format of IsUsed to Yes/No.  (Maybe I shouldn't have done that , but was trying to think about how to work the boxes.

So on the form, I just bound a checkbox to IsUsed so that it would be checked if True and Unchecked if False.  But when I click it it beeps and says that "This RecordSet is not updateable."
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the SourceObject of the subform should be a form based on the second query, so the RecordSource could be its SQL and you wouldn't need to store the second query as a query

the ControlSource of the checkbox should be IsUsed.

"This RecordSet is not updateable" -- this is true.

So on the CLICK event of the checkbox, if it is already checked, then it needs to be cleared.  If it is not checked, it needs to be checked.  This is done by adding or removing a record from ContactDepartments.
   dim sSQL as string 
      ,nDeptID as long _
      ,nContactID as long

   nDeptID  = me.DeptID
   nContactID  = me.contactID

   if me.IsUsed = true then
      'remove record
      sSQL = "DELETE t.* FROM ContactDepartments  as t " _
         & " WHERE DeptID = " & nDeptID _
         & " AND ComtactID = " & nComtactID
   else
      'add record
      sSQL = "INSERT INTO ContactDepartments (ContactID, DeptID) " _
         & " SELECT " & nComtactID _
         & ", " & nDeptID 
   end if

   with currentdb
      .execute sSQL
     ' ------ remove once this is working ok
      debug.print sSQL
      msgbox "~~~ " & .recordsaffected  & " records affected" 
   end with

   'if change doesn't show, more can be done
   me.refresh

Open in new window

~~~~~~~~~ Add Option Explicit ~~~~~~~~~

if the top of your module does not have a statement that says Option Explicit, then add this:

Option Explicit  ' require variable declaration

Open in new window


~~~~~~~~~ Compile ~~~~~~~~~
 
Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing.
 
from the menu in a VBE (module) window: Debug, Compile
(Alt-F11 to switch to the code window)
 
Fix any errors on the yellow highlighted lines.
Add needed references and remove missing references if necessary
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
FIX BAD INFORMATION for Query 2
this:
ON q.ContactID = ContactDepartments.ContactID AND q.DeptID = ContactDepartments.DeptID
should be
ON q.ContactID = ContactDepartments.ContactID 

Open in new window

 so all the departments are listed ...

If only one department was wanted, the Departments table could have been joined in ;) ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "But when I click it it beeps and says that "This RecordSet is not updateable." "

Checkbox properties:

set the Locked property of the checkbox to True, along with all the other controls because the recordset is not updateable.

Locked = True and Enabled = True allows information to be copied but not changed.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "I totally agree with Crystal..."

thanks, Dale ~ although now you probably didn't mean to say "totally" ;) ~

Your multi-value point should be heeded by all readers!

using a temp table is certainly an option too.  If there were a lot of departments, that method would be better.  And most likely it is better for performance too.  

... and thanks for the Cartesian explanation too

> "done so much of the work "

you are welcome to say anything you like anytime ~ goal is the best solution and I don't always think of everything ... or think of it wrong and have to correct myself if someone else doesn't see it first ~

> "Thank you Dale!  Your help is certainly appreciated."

yes, always ~

warm regards,
crystal

~ have an awesome day ~
0
KevinAuthor Commented:
Thanks so much for all this help Crystal - you are an early Christmas present!  I copied all the code and fixed the query, but probably have one last hurdle...  When I click the checkbox whose ControlSource is IsUsed, my computer just beeps, I see the "Recordset is not updateable" message in the lower left corner, and the status of the box doesn't change.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome and thank you, Kevin ~

Did you set LOCKED to true?

is the CLICK event executing?  Put a MsgBox in there so you can know -- or set a breakpoint if you know how to do that.

When you construct SQL in code, it is a good idea to include:

   debug.print sSQL

until the program is working -- then you can delete this or comment it

** debug.print ***
 
debug.print sSQL
 
--> this prints a copy of the SQL statement to the debug window (CTRL-G)
 
After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement
 
If the SQL statement has an error
 
1. Make a new query (design view)
 
2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
(or right-click on a blank area in the query design and choose --> SQL View)
 
3. cut the SQL statement from the debug window
(select, CTRL-X)
 
4. paste into the SQL window of the Query
(CTRL-V)
 
5. run !  from the SQL window
-- Access will tell you where the problem is in the SQL
 
'~~~~~~~~~~~~~~
 
the debug window, also called the immediate window, is another good resource.  When you are executing code, you can query the value of any variable, field, control, ...
 
? aSQL
and then press ENTER
 
You can also use the debug window to get help on a topic -- type or paste a keyword into the window and press F1
0
KevinAuthor Commented:
Both Locked and Enabled on the IsUsed checkbox are set to Yes.

I tried a msgbox in the OnClick code, but it never gets to there when I click a box.  As far as I can tell the OnClick code never gets executed...

Also, I will post more questions on this database separately - just giving you a heads-up in case you still have energy :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
perhaps someone else can help you implement it -- I will check back later on but it will be awhile ~

meanwhile, how about posting the code you have behind the subform?
0
KevinAuthor Commented:
Thanks Crystal - here is the code on the subform.  The OnClick Event for chkIsUsed never gets launched...

Option Compare Database
Option Explicit  ' require variable declaration
Private Sub chkIsUsed_Click()
 Dim sSQL As String _
      , nDeptID As Long _
      , nContactID As Long

   nDeptID = Me.DeptID
   nContactID = Me.ContactID
   
   If Me.IsUsed = True Then
      'remove record
      sSQL = "DELETE t.* FROM ContactDepartments  as t " _
         & " WHERE DeptID = " & nDeptID _
         & " AND ComtactID = " & nContactID
   Else
      'add record
      sSQL = "INSERT INTO ContactDepartments (ContactID, DeptID) " _
         & " SELECT " & nContactID _
         & ", " & nDeptID
   End If

   With CurrentDb
      .Execute sSQL
     ' ------ remove once this is working ok
      Debug.Print sSQL
      MsgBox "~~~ " & .RecordsAffected & " records affected"
   End With

   'if change doesn't show, more can be done
   Me.Refresh
End Sub

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
does the property sheet say [Event Procedure] in the CLICK event? sometimes code gets unhooked
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
also be sure that the code compiles and is saved
0
KevinAuthor Commented:
Yes, the OnClick value in the list is [Event Procedure].  I have compiled the code before, but now when I check it the "Compile Database" option on the menu bar is ghosted...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
set a breakpoint in the click event and then press F8 to single-step through it.  Here is some info on debugging ... this will be my last post for awhile

~~~~~ setting a breakpoint ~~~~~
 
To Set a Breakpoint with the mouse, click in the shaded gray region just to the left of the white margin area on the same line as the statement where you want the code to stop.
 
If a breakpoint is set, a RED DOT will appear in the gray area just to the left of the white margin area for that line.
 
F9 --> toggle Breakpoint on current line
 
from the menu --> Debug, Toggle Breakpoint
 
To clear a breakpoint, simply click on the red dot, press F9, or Toggle Breakpoint from the menu.
 
When your program executes, it will stop on each Breakpoint line and highlight it
 
While you are in this mode of suspended execution, you can:
 
 -- F8 to execute the next statement --
 
F8 will stop on every line -- even if code branches to another procedure
 
-- SHIFT-F8 to execute next statement --
-- in current procedure --
 
This is handy if you know that the external sub or function call does not have errors and you have no need to examine any variables.
 
-- F5 to continue running normally --
 
(from the menu: Run, Run)
 
--- skipping code ---
if you see that you need to fix things and want to just end the program,
 
right-click on the line where your "exit code" starts
choose SET NEXT STATEMENT from the shortcut menu
 
~~~~~ Stop vs Breakpoint ~~~~~
 
Breakpoints are not saved.  The next time you open your database, you will have to set them again.  Using STOP as a statement in your code is saved if you save your code while it is there.  
 
~~~~~ manually Breaking a program ~~~~~
 
press CTRL-BREAK during execution to suspend code.  The problem with this is that the program is not always paying attention to the keyboard.  You can force it to pay attention by using DoEvents in your code.
 
DoEvents
 
~~~~~ DoEvents ~~~~~
 
DoEvents is used to make VBA pay attention to what is currently happening and look to see if the OS (Operating System) has any requests.

i.e.: if you have a loop and want to be able to BREAK it with CTRL-BREAK (as opposed to whenever the processor decides to acknowledge the keyboard interrupt), put DoEvents into the loop
 
Sometimes DoEvents is necessary to make changes show up right away -- like if you are writing to a form from code that is not behind the form (code in a general procedure or behind another form or report), or refreshing table definitions after changing data with an SQL statement.
 
Using DoEvents does slow down execution and can cause other problems (such as forgetting what file the system is on when looping through a directory using Dir(filespec) and then Dir() to find next file for the same spec.

A DoEvents is done when you use MsgBox, or are stepping through code (since it has to pay attention to the keyboard)
 
It is a good way to say, "Wake Up!"
 
Here is some generic code for an error handler, a set of statements that reports errors and, which is not done here, handles them according to their value.  For instance if you are deleting a table before you make a table in code and it is not there to delete, a certain error number will report this.  If you want to skip that error, you can use Resume Next (go to the line after the line with the problem)

~~~~~ Exit Code and Error Handler ~~~~~

  '-----  put this line at top of procedure:
 
   On Error GoTo proc_Error
   
 
   '... other statements
 
 
    '------ add these lines to the bottom of the procedure
 
 proc_Exit:
 
   'if everything executed normally, exit the procedure
   Exit sub
 
   'Exit Function -- use if the procedure is a function
   
 
proc_Error:
     MsgBox Err.Description, , _
          "ERROR " & Err.Number & "  procedurename"
 
     'press F8 to step through lines of code
     'comment next line after debugged
      Stop : Resume
 
      resume proc_Exit
 
 End Sub/Function
 
'~~~~~~~~~~~~~~~~~~~~~~~~~
 
If, during execution, an error flag is raised, execution will go to the error handler, which is denoted with a line label
   
proc_Error:
   
--- Line Label ---
 
A line label is a word (that is not a statement) before a colon at the beginning of the line.  Line Labels are used for branching code execution.
   
You can change the label before the colon but it cannot contain spaces.
 
A colon can also be used to separate executable statements so that they can be written on the same line, as with
Stop : Resume
 
--- Error Handler Comments ---
 
When an error is raised and execution goes to the error handler, a message box is issued describing the problem (with the MsgBox  statement), and then the code is STOPped with
 
Stop
 
Press the F8 key ( which is the shortcut key to single-step through code) once to go to the next line
 
Resume
 
tells the process to go back to the line that caused the problem.
 
press F8 again to go to the actual offending line
 
You can then fix the problem, save your code, and then resume the code
(right-click on the line where you want to resume
and choose "Set Next Statement" from the shortcut menu)
 
then, either F5 to RUN (from the menu bar: Run, Run)
or F8 to continue stepping through code
 
OR
to stop the procedure
(from the menu bar: Run, Reset)
 
if, at any point during execution, you want to STOP the code, press CTRL-BREAK -- you may have to hold the Control key and repeatedly press the Pause/Break key until Access pays attention to the keyboard.  In your code, you can put in a line that MAKES it pay attention to the keyboard:
 
DoEvents
 
If I have a loop, I often put DoEvents into the loop while I am developing.  

When you are chasing errors

~~~~~ Immediate Window ~~~~~
 
In the Immediate Window, you can get immediate results to questions -- such as
 
? myVariablename [ENTER]
 
On the next line will be printed the current value of a variable named "myVariablename"
 
You can also set variables to a different value
 
myVariablename = "something_else"
 
you can type keywords such as OpenReport or Resume
and press F1 to get immediate help (without navigating!) to that keyword
 
~~~~~ Debug Window (CTRL-G) ~~~~~
 
The term "Debug Window" is often used to refer to the Immediate Window.
 
You can print to the debug window in code by using the Print method of the Debug object in VBA code
 
Debug.Print myVariablename
 
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0
KevinAuthor Commented:
Thanks so much Crystal.  I cannot even step through the code or compile it.  F8 gives me a beep...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

you can't compile it either? What line causes the problem? Add a blank line and take it away so Compile is available ... and save after compile

form property RecordsetType -- try: Snapshot (although this shouldn't make a difference)

also try triggering the code with a command button CLICK till you get the checkbox to work
CALL chkIsUsed_Click

Open in new window


 -- then see about also triggering it with the checkbox CLICK once it works from command button
0
KevinAuthor Commented:
Crystal - you are not only a good programmer but also a good detective!  If I make a button to call chkIsUsed_Click() it DOES launch the code.  And it gave a messagebox that said 1 record updated...
0
KevinAuthor Commented:
And the table ContactsDepartments populates when I click on the buttons...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
great ~ and thank you

now it is a question to find out why the checkbox won't trigger it ... be sure that ENABLED is YES
0
KevinAuthor Commented:
These are the properties of the checkbox chkIsUsed:

checkbox properties
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
wow, how did you get such a long screenshot? thanks.

Now click in the [Event Procedure] for On Click and click the Builder button ... and post the code for that.

Also, post the latest versions of the SQL for the cartesian query and Query 2.

And finally, post a screen shot of the form in form view again.  I am thinking that those checkboxes should be checked or not ... not Null.  They show Null in the last screen shot.  IsUsed needs more ... this is NOT enough
IsUsed: IIf(IsNull(ContactDepartments.[ContDeptID] ),False,True)

and illustrates one of the problems with Yes/No fields -- they can be coerced to Null

try this instead:
IsUsed: IIf(ContactDepartments.ContDeptID Is Null,False,True) 

Open in new window

and if that is not enough to NOT show Nulls, a better way to know if the record is there will have to be used ... perhaps a subquery
0
KevinAuthor Commented:
IsUsed_Click (and everything else from that form) is:

Option Explicit  ' require variable declaration
Private Sub chkIsUsed_Click()
MsgBox ("Code is working now")
 Dim sSQL As String _
      , nDeptID As Long _
      , nContactID As Long

   nDeptID = Me.DeptID
   nContactID = Me.ContactID
   
   If Me.IsUsed = True Then
      'remove record
      sSQL = "DELETE t.* FROM ContactDepartments  as t " _
         & " WHERE DeptID = " & nDeptID _
         & " AND ComtactID = " & nContactID
   Else
      'add record
      sSQL = "INSERT INTO ContactDepartments (ContactID, DeptID) " _
         & " SELECT " & nContactID _
         & ", " & nDeptID
   End If

   With CurrentDb
      .Execute sSQL
     ' ------ remove once this is working ok
      Debug.Print sSQL
      MsgBox "~~~ " & .RecordsAffected & " records affected"
   End With

   'if change doesn't show, more can be done
   Me.Refresh
End Sub

Private Sub Command45_Click()
Call chkIsUsed_Click
End Sub

Open in new window

0
KevinAuthor Commented:
SQL for the first query:

SELECT Contacts.ContactID, Departments.Department, Departments.DeptID
FROM Departments, Contacts
ORDER BY Departments.Department;

Open in new window


and for the second query:

SELECT q.Department, IIf(IsNull([ContactDepartments].[ContDeptID]),False,True) AS IsUsed, q.ContactID, q.DeptID
FROM qAllContactsDepartments AS q LEFT JOIN ContactDepartments ON q.ContactID = ContactDepartments.ContactID
ORDER BY q.Department;
0
KevinAuthor Commented:
Form in form view.

Contact form
0
KevinAuthor Commented:
When I click on the boxes, the windows status bar says "Calculating" but I can tell that anything happens.  When I click on the button, it runs the code...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
on the cartesian query, remove the ORDER BY clause -- don't sort by anything.

At least all the checkboxes seem to be false and not null

Another thing you can do, since the button runs the code ...

1. resize the button and put it on top of your checkbox ... make it a bigger target than the checkbox too
2.  set the TRANSPARENT property of the button to be NO (or False) so it becomes invisible

so, what the user is really clicking on is the command button but they don't know that.  To them they are toggling the checkbox ... and that is what happens ~

happy thanks-giving ~
0
KevinAuthor Commented:
Happy Thanksgiving to you too.  Gonna try to set up the buttons like you said...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks and sounds good, Kevin.  Keep in mind that everything is layered -- the transparent button needs to be on top.  This will make it impossible to click on your checkbox and select it.  You can use the droplist at the top of the Property Sheet to select the checkbox by its Name when it is behind the command button.

It is helpful to add Bring to Front and Send to Back to the Quick Access Toolbar (QAT).  You can also select the command button, send it to the back, then select the checkbox and when done with it, send it to the back.

Here is a video explaining how to get these and other useful icons on the QAT

https://www.youtube.com/watch?v=ZF4pb7e1QSc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinAuthor Commented:
Thanks for all the help & patience Crystal - you are not only great with access , but a super teacher and trainer too!  (I selected a bunch of your answers that helped, since I needed help a little at a time...)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Kevin, and you're welcome ~ happy you learned (that is the real test of appreciation)
 
Dale also had some valuable input ~ it would have been fine to give him some points too
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.