Loop Help

[DS]

I need help with a Loop statement.

Tag 9 is a Yes/No CheckBox
Tag 99 is a label

Privileges is a Table
SecureID is a field in the table
and Me.TxtID is the field on the form that tells
you which ID Nimber to run the Loop on.
The Loop should run once and then stop.


Dim Ctl As Control
Loop through "Privileges" where SecureID = Me.TxtID
If Privileges.Active = -1 Then
Ctl.Tag 9 = -1 and
Ctl.Tag 99 = True
Else
If Privileges.Active = 0 Then
Ctl.Tag 9 = 0 and
Ctl.Tag 99 = False
End If
End Loop

Any help appreciated
Thanks
DS

 

[Ken Snell \(MVP\)]

Not sure if this is what you're seeking, but this is a general example. Note
that I have no idea what Ctl is being used for in your posted code, nor what
a "Tag 9" or a "Tag 99" means?


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Privileges WHERE SecureID=" &
Me.TxtID", dbOpenDynaset)
With rst
If .EOF = False And .BOF = False Then
Me.NameOfControl.Tag = !Active.Value
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[DS]

Not sure if this is what you're seeking, but this is a general example. Note
that I have no idea what Ctl is being used for in your posted code, nor what
a "Tag 9" or a "Tag 99" means?


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Privileges WHERE SecureID=" &
Me.TxtID", dbOpenDynaset)
With rst
If .EOF = False And .BOF = False Then
Me.NameOfControl.Tag = !Active.Value
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Ken I have 50 Checkboxes that need to be either checked or unchecked
depending on the Active field, So I used the "Tag Property" and put 9 in
it. The other field is a label field also using the "Tag Property" with
99. So the only part that still confuses me is this...
Me.NameOfControl.Tag = !Active.Value How do I refer to all of the
controls that have a "Tag Property" of 9 and the Labels that have the
"Tag Property" of 99?

Thank You
DS

 

[DS]

Ken I have 50 Checkboxes that need to be either checked or unchecked
depending on the Active field, So I used the "Tag Property" and put 9 in
it. The other field is a label field also using the "Tag Property" with
99. So the only part that still confuses me is this...
Me.NameOfControl.Tag = !Active.Value How do I refer to all of the
controls that have a "Tag Property" of 9 and the Labels that have the
"Tag Property" of 99?

Thank You
DS

This is what I have and I'm getting a "Type Mismatch"
on the Setdbs = CurrentdB

Dim Ctl As Control
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SecurityDetails WHERE
SecurityID = " & Me.TxtID & ", dbOpenDynaset")
With rst
If .EOF = False And .BOF = False Then
If Ctl.Tag = "9" Then
Ctl.Value = -1
End If
If Ctl.Tag = "99" Then
Ctl.Visible = True
End If
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[DS]

This is what I have and I'm getting a "Type Mismatch"
on the Setdbs = CurrentdB

Dim Ctl As Control
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SecurityDetails WHERE
SecurityID = " & Me.TxtID & ", dbOpenDynaset")
With rst
If .EOF = False And .BOF = False Then
If Ctl.Tag = "9" Then
Ctl.Value = -1
End If
If Ctl.Tag = "99" Then
Ctl.Visible = True
End If
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

OK it's closer!
The only problem left is that it's not setting the value of the
checkboxes if the Active Field is -1...
Thanks
DS

 

[DS]

The Value of the Check Boxes all Seem to either get set to -1 or 0.
Any help to finalize this problem apreciated.
Doesn't Ctl !Active.Value set the value of the Checkbox to the value od
the Active field in the SecurityDetails table?

Thanks
DS

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SecurityDetails WHERE
SecurityDetails.SecurityID = " & Me.TxtID & "", dbOpenDynaset)

With rst
If .EOF = False And .BOF = False Then
Dim Ctl As Control
For Each Ctl In Me.Controls
If Ctl.Tag = "9" Then
Ctl = !Active.Value
End If
Next Ctl
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[Douglas J. Steele]

The Value of the Check Boxes all Seem to either get set to -1 or 0.

That's absolutely correct. Check boxes apply to boolean fields. -1 is True,
0 is False.

What were you expecting instead?

 

[DS]

That's absolutely correct. Check boxes apply to boolean fields. -1 is True,
0 is False.

What were you expecting instead?

I don't think I was clear. It doesn't account for individual records.
It's setting every checkbox to either -1 or 0. Regardless of the real
value in the table. I should have some boxes checked and some not.
There are 50 boxes.
Thanks
DS

 

[DS]

DS wrote:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SecurityDetails WHERE
SecurityDetails.SecurityID = " & Me.TxtID & "", dbOpenDynaset)

With rst
If .EOF = False And .BOF = False Then
Dim Ctl As Control
For Each Ctl In Me.Controls
If Ctl.Tag = "9" Then
If !Active.Value = -1 Then
Ctl = -1
ElseIf !Active.Value = 0 Then
Ctl = 0
End If
End If
Next Ctl
End If
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[John Vinson]

Doesn't Ctl !Active.Value set the value of the Checkbox to the value od
the Active field in the SecurityDetails table?

Yes. And that value will be the same every time you go through the
loop; you're looking at the value of [Active] in the first record of
the recordset, and not changing that. You'll loop through all the
controls on the form, and if the Tag value is 9, set that checkbox to
the value of this Active field.

Since there's only one field named Active, you'll set all the
checkboxes to the same value.

Could you explain what you WANT to happen? What are these fifty
checkboxes supposed to represent for the user (other than massive
visual confusion)?

John W. Vinson[MVP]

 

[DS]

Doesn't Ctl !Active.Value set the value of the Checkbox to the value od
the Active field in the SecurityDetails table?

Yes. And that value will be the same every time you go through the
loop; you're looking at the value of [Active] in the first record of
the recordset, and not changing that. You'll loop through all the
controls on the form, and if the Tag value is 9, set that checkbox to
the value of this Active field.

Since there's only one field named Active, you'll set all the
checkboxes to the same value.

Could you explain what you WANT to happen? What are these fifty
checkboxes supposed to represent for the user (other than massive
visual confusion)?

John W. Vinson[MVP]

OK 50 does seem like a lot, but to my credit I have them organized and
spread over 3 pages! These checkboxes are user privileges, such as
"Open New Check", "Sign Out", etc.
Each user has a different combination of these privileges. So,
according to the user I then need to cycle thru each of the records and
set the value to either -1 or 0. Whatever the Value is for that
corresponding record in the "SecurityDetails" Table.

Thanks,
DS

 

[DS]

DS wrote:
Ok This seems to be working better...
I'm getting the message box to pop up for the appropiate number of
records with the right value.

I still can't set the value of the checkbox though...it blows right
through that part of the code. The MsgBoxis only there to test.

Thanks
DS

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Ctl As Control

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SecurityDetails WHERE
SecurityDetails.SecurityID = " & Me.TxtID & "", dbOpenDynaset)

With rst
Do Until .EOF
If !Active.Value = -1 Then
MsgBox "Active"
For Each Ctl In Me.Controls
If Ctl.Tag = !PrivID.Value Then
MsgBox "Active PrivID"
Ctl.Value = -1
Else:
End If
Next Ctl
ElseIf !Active.Value = 0 Then
MsgBox "Not Active"
For Each Ctl In Me.Controls
If Ctl.Tag = !PrivID.Value Then
MsgBox "Not Active PrivID"
Ctl.Value = 0
Else:
End If
Next Ctl
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[DS]

This is the Culprit!!!!

For Each Ctl In Me.Controls
If Ctl.Tag = !PrivID.Value Then
Ctl.Value = -1
Else:
End If
Next Ctl

If I change it to this....it works.
For Each Ctl In Me.Controls
If Ctl.Tag = 1 Then
Ctl.Value = -1
Else:
End If
Next Ctl

The problem is I need !PrivID.Value because the Value changes.
Having a fixed number kinda defeats the idea of a loop.

Any help appreciated.
Thanks
DS

 

[John Vinson]

Each user has a different combination of these privileges. So,
according to the user I then need to cycle thru each of the records and
set the value to either -1 or 0. Whatever the Value is for that
corresponding record in the "SecurityDetails" Table.

But there is NOTHING in your code which links any particular record or
security detail value to a particular checkbox.

Why not have a more standard table design, modeling the Users to
Privileges many to many relationship as a many to many?

Users
UserID
LastName
FirstName
<other bio info>

Privileges
PrivilegeID Autonumber Primary Key
Privilege Text

UserPrivileges
UserID
PrivilegeID


So if Joe has privileges X, Y, K and J, there would be four records in
the UserPrivileges table; this could be displayed on a Subform or a
multiselect Listbox showing the actual names of the privileges, rather
than fifty checkboxes spread across three pages!

John W. Vinson[MVP]

 

[DS]

But there is NOTHING in your code which links any particular record or
security detail value to a particular checkbox.

Why not have a more standard table design, modeling the Users to
Privileges many to many relationship as a many to many?

Users
UserID
LastName
FirstName
<other bio info>

Privileges
PrivilegeID Autonumber Primary Key
Privilege Text

UserPrivileges
UserID
PrivilegeID


So if Joe has privileges X, Y, K and J, there would be four records in
the UserPrivileges table; this could be displayed on a Subform or a
multiselect Listbox showing the actual names of the privileges, rather
than fifty checkboxes spread across three pages!

John W. Vinson[MVP]

Hi John,
The tables are setup that way. I will consider your idea of a listbox
to show the Privileges assigned.
In the meantime why isn't the ctl.Tag = !PrivID.Value workin, I tried
assigning the !PrivID.Value to a TxtBox and then did Tag.Ctl = Me.TxtBox
but that disn't work either, is it because !PrivID is a Variable. if so
how do you fix that! Thank you for your input.
DS

 

[DS]

Hi John,
The tables are setup that way. I will consider your idea of a listbox
to show the Privileges assigned.
In the meantime why isn't the ctl.Tag = !PrivID.Value workin, I tried
assigning the !PrivID.Value to a TxtBox and then did Tag.Ctl = Me.TxtBox
but that disn't work either, is it because !PrivID is a Variable. if so
how do you fix that! Thank you for your input.
DS

Sorry John! The Tag in each Checkbox is the same number as the PrivID
that it is connected to.
PS I tried the Listbox thing and it looks nice. But I would also have
the option on doing it the other way as well.
Thank You
DS

 

[John Vinson]

Sorry John! The Tag in each Checkbox is the same number as the PrivID
that it is connected to.
PS I tried the Listbox thing and it looks nice. But I would also have
the option on doing it the other way as well.
Thank You

I'd suggest removing the .Value from the recordset reference. I've
never used anything other than

= rs!fieldname

to return the value in the field.

John W. Vinson[MVP]

 

[DS]

I'd suggest removing the .Value from the recordset reference. I've
never used anything other than

= rs!fieldname

to return the value in the field.

John W. Vinson[MVP]

Thanks John I'll give it a try. BTW I set up another form under your
suggestion with the ListBox and it looks and works smashingly well,
still pursuing the other option as well! Thanks for the input.
DS

 

[DS]

DS wrote:
This works...

The Solution was Val(Ctl.Tag) = !PrivID.Value

Thank you!
DS

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Ctl As Control
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Test WHERE Test.TestID = " &
Me.TxtID & "", dbOpenDynaset)
With rst
Do Until .EOF
If !Active.Value = -1 Then
MsgBox "Active"
For Each Ctl In Me.Controls
If Val(Ctl.Tag) = !PrivID.Value Then
MsgBox "Active PrivID"
Ctl.Value = -1
Else:
End If
Next Ctl
ElseIf !Active.Value = 0 Then
MsgBox "Not Active"
For Each Ctl In Me.Controls
If Val(Ctl.Tag) = !PrivID.Value Then
MsgBox "Not Active PrivID"
Ctl.Value = 0
Else:
End If
Next Ctl
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing