Kevin
asked on
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 ?
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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
Is that the query that will be on my subform? I am not sure how to do this...
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.[ContD eptID] ),False,True) AS IsUsed
Is that the query that will be on my subform? I am not sure how to do this...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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 ...
ASKER
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
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
ASKER
ASKER
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.
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.
ASKER
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;
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;
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_s ub
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_s
ASKER
Thanks again. When I try to create the form, it prompts me for the value of:
qAllContactsDepartments.De ptID
qAllContactsDepartments.De
you're welcome
> "qAllContactsDepartments.D eptID "
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 ~
> "qAllContactsDepartments.D
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:
if the top of your module does not have a statement that says Option Explicit, then add this:
~~~~~~~~~ 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
drag the subform onto it from the Navigation Pane.
Set the Name property of the subform conctrol = the Source Object property (f_All_ContactDepartments_
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
~~~~~~~~~ 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
~~~~~~~~~ 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
ASKER
I am getting close - thanks! A couple of things..
1. When I made the form f_All_ContactDepartments_s ub 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...
1. When I made the form f_All_ContactDepartments_s
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.
You have done so much of the work on this, I'm going to bow out for now.
ASKER
ASKER
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
Kevin
ASKER
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.
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
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
ASKER
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
1. the cartesian query
2. the second query
and tell me the name of each
thanks
ASKER
The first query is named qAllContactsDepartments:
The second query id named qContactsWIthDepartments:
SELECT Contacts.ContactID, Departments.Department, Departments.DeptID
FROM Departments, Contacts
ORDER BY Departments.Department;
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;
the second query should be:
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
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;
... not sure the OrderBy clause will be taken into account with how it will be usedMake 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
ASKER
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.
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.
ASKER
ASKER
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 ...
__________________________
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)
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
ASKER
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...
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?
ASKER
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."
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.
if the top of your module does not have a statement that says Option Explicit, then add this:
~~~~~~~~~ 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
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
~~~~~~~~~ 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
~~~~~~~~~ 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.Contact ID AND q.DeptID = ContactDepartments.DeptID
should be
If only one department was wanted, the Departments table could have been joined in ;) ~
this:
ON q.ContactID = ContactDepartments.Contact
should be
ON q.ContactID = ContactDepartments.ContactID
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.
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, 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 ~
ASKER
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
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
ASKER
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 :)
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?
meanwhile, how about posting the code you have behind the subform?
ASKER
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
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
ASKER
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
'~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~
~~~~~ 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
'~~~~~~~~~~~~~~~~~~~~~~~~~
ASKER
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
-- then see about also triggering it with the checkbox CLICK once it works from command button
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
-- then see about also triggering it with the checkbox CLICK once it works from command button
ASKER
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...
ASKER
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
now it is a question to find out why the checkbox won't trigger it ... be sure that ENABLED is YES
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(ContactDepartme nts.[ContD eptID] ),False,True)
and illustrates one of the problems with Yes/No fields -- they can be coerced to Null
try this instead:
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(ContactDepartme
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)
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
ASKER
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
ASKER
SQL for the first query:
and for the second query:
SELECT q.Department, IIf(IsNull([ContactDepartm ents].[Con tDeptID]), False,True ) AS IsUsed, q.ContactID, q.DeptID
FROM qAllContactsDepartments AS q LEFT JOIN ContactDepartments ON q.ContactID = ContactDepartments.Contact ID
ORDER BY q.Department;
SELECT Contacts.ContactID, Departments.Department, Departments.DeptID
FROM Departments, Contacts
ORDER BY Departments.Department;
and for the second query:
SELECT q.Department, IIf(IsNull([ContactDepartm
FROM qAllContactsDepartments AS q LEFT JOIN ContactDepartments ON q.ContactID = ContactDepartments.Contact
ORDER BY q.Department;
ASKER
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Happy Thanksgiving to you too. Gonna try to set up the buttons like you said...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Dale also had some valuable input ~ it would have been fine to give him some points too
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.