Custom roles with Checkbox verification

[Ryan Tisserand]

Im not really sure how to ask this question so I will do my best to explain
what Im am trying to achieve. I have two tables, Users and Locations. What
I would like to happen is when I create a new user, I would like all
locations to show up on the create new user form with a checkbox to select
them. I am an experianced access user, but for the life of me I can not
figure out how to get the checkbox involved. The purpose of all this is to
simulate the creation of a new user in the ASP.net configuration tool and
assign them roles. Each location would be a role. Im sorry for the louzy
description of my question, but any help would be apprieciated.!

 

[dch3]

Are you using a subform to display the locations?

 

[Ryan Tisserand]

I would prefer to.

Are you using a subform to display the locations?

 

[dch3]

My first instinct is to say that you'll need to add YES/NO field to the table
that contains the locations. However, I seem to recall seing a listBox
control somewhere that has a property setting the displays a checkBox next to
the items in the listBox which is used to select the item.

 

[Ryan Tisserand]

Adding the yes/no field to the locations table only allows you to select that
location, not that location for a spacific user. I have tried every way I
can think of to join just those two tables, but to no avail.

 

[dch3]

DUH! Stupid! Stupid! Stupid! ::BANGING HEAD IN WALL::

I forgot to mention you'll need a third table.

Tbl 1 - Users
Tbl 2 - Locations
Tbl 3 - Locations Assigned to Users
-Primary Key
-Foreign Key - tblUser
-Foreign Key - tblLocations

 

[dch3]

Oh and when I did this back a millenium ago, I used a listBox to select the
locations to be assigned to a user and then appened/deleted the records to
tblLocationsAssignedToUser to manage them. Other wise, you'd have to query
tblLocations to get the records to be added and the append them. Not too
difficult, but if the table is Global you could run in to conflicts if
another person is trying to add a table. Admin 1 - will select 3 locations
for User 1 whil Admin 2 will then select 4 different locations and it all
gets jacked up. Go with a list box or a temp table.

 

[Ryan Tisserand]

What triggers the append event? The "on click" of the checkbox or listbox?
After many failed attempts to do this with queries, my current attempt is in
code. Looking at the way you designed the three tables, the two foreign keys
would be text lookup fields, not a checkbox.

 

[mscertified]

You do not want to do this via checkboxes. If the number of locations
changes, you will need to chenge your form. It is a poor design. You need a
cross-reference table that relates users to locations. You will populate this
table via a subform.

-Dorian

 

[Ryan Tisserand]

Let me try to draw what I want. The only reason I want to use checkboxes is
for visual representation and end user ease of use.

USER SETUP/EDIT FORM
User - John Doe
Address - 1111 Fun Street

Locations of service
Oklahoma []
Texas [x]
Kansas []
Arkansas [x]
------------------------------------
USER LOGED IN FORM
Welcome back John Doe

Your locations
Texas
Arkansas

 

[John W. Vinson]

Let me try to draw what I want. The only reason I want to use checkboxes is
for visual representation and end user ease of use.

USER SETUP/EDIT FORM
User - John Doe
Address - 1111 Fun Street

Locations of service
Oklahoma []
Texas [x]
Kansas []
Arkansas [x]

One way to do this is to use a multiselect Listbox; this will display all the
locations and highlight those which are selected. You still *DO* need three
tables (I would suggest *NOT* using Lookup Fields at all, however).

Here's some VBA code I use in an animal shelter app, using a multiselect
listbox to enter multiple medical conditions for animals. It's run from the
Click event of a button named cmdProcess on the form - the user selects the
needed rows and clicks Process to fill the table.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is now
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

 

[Ryan Tisserand]

Thanks for the code, I was working on something simmilar without looping
using unbound checkboxes. The checkboxes are requested by the user, so I
will continue to figure out how to make them work. Your code will help alot
though. Can you update an animales conditions? If so, do you populate the
conditions of an existing animal in the listbox with the "On Open" event of
the form? If so can I see that code as well please? I think I can use what
you have given me to do the same things with unbound checkboxes, so thank you
very much.

Let me try to draw what I want. The only reason I want to use checkboxes is
for visual representation and end user ease of use.

USER SETUP/EDIT FORM
User - John Doe
Address - 1111 Fun Street

Locations of service
Oklahoma []
Texas [x]
Kansas []
Arkansas [x]

One way to do this is to use a multiselect Listbox; this will display all the
locations and highlight those which are selected. You still *DO* need three
tables (I would suggest *NOT* using Lookup Fields at all, however).

Here's some VBA code I use in an animal shelter app, using a multiselect
listbox to enter multiple medical conditions for animals. It's run from the
Click event of a button named cmdProcess on the form - the user selects the
needed rows and clicks Process to fill the table.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is now
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

 

[dch3]

Depends on how you design the form. If you use a list box showing the
available locations, then add a button 'ADD TO USER' in the ATU onClick add
the code to append the record to the table. If you go with a temp table and a
subform showing the locations, you could use the onClick event.

 

[dch3]

I have come up with an design that I've warmed up to that might help...

One the left side of the form is a list box displaying users, on the right
is a subform with a single comboBox displaying continuous records with the
subform linked to the value in the listBox. As i need to add or delete
records, I just do that in the subform.

Thanks for the code, I was working on something simmilar without looping
using unbound checkboxes. The checkboxes are requested by the user, so I
will continue to figure out how to make them work. Your code will help alot
though. Can you update an animales conditions? If so, do you populate the
conditions of an existing animal in the listbox with the "On Open" event of
the form? If so can I see that code as well please? I think I can use what
you have given me to do the same things with unbound checkboxes, so thank you
very much.

Let me try to draw what I want. The only reason I want to use checkboxes is
for visual representation and end user ease of use.

USER SETUP/EDIT FORM
User - John Doe
Address - 1111 Fun Street

Locations of service
Oklahoma []
Texas [x]
Kansas []
Arkansas [x]

One way to do this is to use a multiselect Listbox; this will display all the
locations and highlight those which are selected. You still *DO* need three
tables (I would suggest *NOT* using Lookup Fields at all, however).

Here's some VBA code I use in an animal shelter app, using a multiselect
listbox to enter multiple medical conditions for animals. It's run from the
Click event of a button named cmdProcess on the form - the user selects the
needed rows and clicks Process to fill the table.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is now
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

 

[dch3]

I can send you a screen shot if you'd like to see it.

Thanks for the code, I was working on something simmilar without looping
using unbound checkboxes. The checkboxes are requested by the user, so I
will continue to figure out how to make them work. Your code will help alot
though. Can you update an animales conditions? If so, do you populate the
conditions of an existing animal in the listbox with the "On Open" event of
the form? If so can I see that code as well please? I think I can use what
you have given me to do the same things with unbound checkboxes, so thank you
very much.

Let me try to draw what I want. The only reason I want to use checkboxes is
for visual representation and end user ease of use.

USER SETUP/EDIT FORM
User - John Doe
Address - 1111 Fun Street

Locations of service
Oklahoma []
Texas [x]
Kansas []
Arkansas [x]

One way to do this is to use a multiselect Listbox; this will display all the
locations and highlight those which are selected. You still *DO* need three
tables (I would suggest *NOT* using Lookup Fields at all, however).

Here's some VBA code I use in an animal shelter app, using a multiselect
listbox to enter multiple medical conditions for animals. It's run from the
Click event of a button named cmdProcess on the form - the user selects the
needed rows and clicks Process to fill the table.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is now
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

 

[John W. Vinson]

Thanks for the code, I was working on something simmilar without looping
using unbound checkboxes. The checkboxes are requested by the user, so I
will continue to figure out how to make them work. Your code will help alot
though. Can you update an animales conditions? If so, do you populate the
conditions of an existing animal in the listbox with the "On Open" event of
the form? If so can I see that code as well please? I think I can use what
you have given me to do the same things with unbound checkboxes, so thank you
very much.

I would use the form's Current event (which fires as you navigate to each
record). If you really want checkboxes (being aware of their serious
disadvantages, first of which is that you need to redesign your form whenever
one changes) you can do so by adapting the logic - it might be simplest if you
come up with a naming convention for the Name property of the checkbox
controls such as Chk1, Chk2, Chk3 so you can use code like

For i = 1 to 13
Me.Controls("Chk" & i) = <some true or false value>
Next i

Hmm... let's see if I can find that Current event... sorry, that's not
available right now. You'ld just need to create a Recordset on the related
table using the linking field; set all the checkboxes to False, and loop
through the child table records setting the appropriate checkboxes to True.