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

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.

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...

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.

OK - thanks for helping me with some baby steps.  I can see that this is way beneath your pay grade!


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...

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.

hopefully you can take over now, Dale, I have a training call ...

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...

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

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...

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!

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.

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;

Select allOpen in new window

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;

Select allOpen in new window

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

Thanks again.  When I try to create the form, it prompts me for the value of:

qAllContactsDepartments.DeptID

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 ~

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

Select allOpen 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

Select allOpen 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

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.

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...

Crystal,

You have done so much of the work on this, I'm going to bow out for now.

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...

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

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...

@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.

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

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


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)

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

The first query is named qAllContactsDepartments:

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

Select allOpen 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;

Select allOpen in new window

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;

Select allOpen 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

Never mind - I misspelled the subform...  Moving forward...

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.

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...
 

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?

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 ...

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

Select allOpen in new window

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...

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?

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."

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

Select allOpen 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

Select allOpen 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

FIX BAD INFORMATION for Query 2
this:
ON q.ContactID = ContactDepartments.ContactID AND q.DeptID = ContactDepartments.DeptID
should be

ON q.ContactID = ContactDepartments.ContactID 

Select allOpen in new window

 so all the departments are listed ...

If only one department was wanted, the Departments table could have been joined in ;) ~

> "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.

> "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 ~

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.

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

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 :)

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?

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

Select allOpen in new window

does the property sheet say [Event Procedure] in the CLICK event? sometimes code gets unhooked

also be sure that the code compiles and is saved

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...

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
 
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks so much Crystal.  I cannot even step through the code or compile it.  F8 gives me a beep...

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

Select allOpen in new window

 -- then see about also triggering it with the checkbox CLICK once it works from command button

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...

And the table ContactsDepartments populates when I click on the buttons...

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

These are the properties of the checkbox chkIsUsed:

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) 

Select allOpen 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

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

Select allOpen in new window

SQL for the first query:

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

Select allOpen 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;

Form in form view.

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...

Happy Thanksgiving to you too.  Gonna try to set up the buttons like you said...

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...)

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