I have a form liked to a table called tblIncidents, also on that form a I have a fixed group of checkboxes (department names)
that I need to link to another table called tblNotification, wherein lie: Incident ID | Dept_ID
I control access to the record base on the Users association with the Dept....

Q1. When the page loads how do I set the checkboxes to their current values, the form is tied to the tblIncident record, not the tblNotification record. Do I code into the onload event something like.. select Dept_ID from tblNotification and if there is a record for one of the checkboxes set to true? or something like that?

Q2. When different checkboxes are selected do I use the on_change event and code to Insert or Delete a record every time the individal box staus is changed?

Maybe I am on the right track? But I'd realy like some feedback just to confirm, as I always find myself making things harder than they need to be.

BTW.
Found this post but not sure its the right example for what I need to do:
Using Check Boxes in a many to many relationship
http://www.utteraccess.com/forums/showthre...amp;o=&vc=1

Thanks a lot!
Patrick

Is there anything wrong (potentially problematic) with updating the checked data on unload.

something like this...

Private Sub Form_Unload()
Dim ctl As Control
Dim strlist As String
Dim CountCheck As Integer
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strlist = strlist & ctl.Name & vbCrLf
End If
End If
Next

MsgBox "These are the names of boxes ticked , " & vbCrLf & strlist, _
vbExclamation, Me.Caption
'then run execute update sql.. if false delete, if true and doesn't exist, add...

End Sub

If your fixed 'group of checkboxes' are fields in a record then you have repeating groups and this data should be in a related table. From your questions, it would appear that this is what you have and if you normalize your structure then the DeptID should not be a problem.

hth,
Jack

the checkboxes are not fields in the record.
Heres what I have:

tblIncidents... ID, Date, Description.....
(actually its tblFalls, tblComplaints, tblAssaults...etc but for practical purposes I just pretend its one table for now...

tblDept... ID,Dept_Name
tblUser...ID,Name, login, password
tblUserDepts User_ID, Dept_ID
tblNotification Dept_Id, Incident_ID, Incident_Type

on my Incident form (be it for Falls, Complaints,, etc) I have a section of checkboxes for applicable departments. When the user checks them, I want to make sure a record is instered to the tblNotification... so then users assigned to that department are notified.

I just am not sure how to:

1. set the status of the checkboxes when the form loads...
somehow I think i need select Dept_Id from tblNotification where Incident_Id = currentrecord
and set the corresponding check boxes to true if the that dept comes up in the result.

2. add or remove the tblNotification record where the dept is subsequently checked or uncheked.

Make sense?

So I loop thu a recordset on load?
then loop thru the collection on unload and add and remove record?

Are the check boxes just unbound controls on your form? So when a checkbox is checked it is supposed to add a record to a table (tblNotification) and when it is unchecked it is to remove the record, right?

Would it work to have a datasheet subform based on tblNotification linked by IncidentID (or whatever) and then select the DeptID from a combo box and for each DeptID to be notified select that dept in a new record? So if there are 10 DeptID's there may be 3 selected (shown) for IncidentID 23 and7 selected for IncidentID 17. From this datasheet view it would be easy to delete a record (double click?), edit or review the selections.

Just a thought as this seems more logical rather than writing code to append and delete records to tblNotification... And the code to loop through the records to check or uncheck the appropriate check box on the form...

If you like you could add a Yes/No field to the tblNotification and then append all DeptID's and the IncidentID to the tblNotification and then check only those that apply.... Just another way to skin the cat, but with empty records....

What do you think of those suggestions?

Jack

hmm. thanks very interesting idea. i have yet to experiment with subforms... wil have to try.

my group of departments on each form can be static, as I want anyone who enters the incident to be able to assign it's notification dept, and a notified department able to assign or re-assign another dept untill it reaches the right person...

So I can make a little subform, that is liked to the tbl notification and the Incident Id... but i still want the static # of boxes for all departments... at least for now.

Or... use two combo boxes 1. with all unassigned depts and one with depts to be notified... then the user could send the assingment back and forth betwenn the boxes...

hmmm. hmmm.
see what you started.
thanks! = )

maybe i'll try using a subform and look for an example using two combo boxes.. i kinda like that idea.

Base your subform on tblNotificatiion. This may NOT be the right table, but from your description it seems to be. You might want to consider list boxes if you want to move data from one to the other. This is not hard to do, but can become complex as you add joined tables such as IncidentID...

I have attached a simple Listbox to Listbox demo so you can see what I am talking about...

I am glad I got something started as there is always more than one way to skin a cat....

Jack

Great! i'm gonna take this home and work on it there... offline.
Will let you know what happens tomorrow.

Already, I think I can just add two listboxes - and not even need a subform - right?

Listbox1: list of dept_names and deptID from a tblDepts deptID not in (select dept_ID from tblNotification where Incicident_ID = currentrecord)

Listbox2: list of dept_names and deptID where deptID In (select...)

then on a double click event... add or delete the tbl notification record.. everything should refresh and look great, right??

Way easier than the chekboxes and I can add Depts willy nilly now.

Thanks Jack. I'll look at your example tonight. This is cool.

btw.. you've got something on your tooth.. or is that one missing?

plocke -

I am not sure exactly what the purpose of the list boxes are, but the two list boxes may be all you need. Give it a shot and see if it will work for you...

I will not be here until after noon Pacific Time so if you come looking for me you will not find me until then...

Good luck!

Jack

oh.. because the end users are boffoons and likely to not pay any attention to what is selected in one list box... so i'll use two to make it clearer, whos been notified and who hasn't ...

thanks again!.. good night.

Thanks a million Jack! That example was perfect and I have it working like a charm in my application.
Way cool.