Referencing a Field in a Table

[DSTR3]

I'm trying reference a field in a Table. I think the problem is this...

!Active.Value

I'm not sure if this is the way to refernce the field.
The field is Active in the SecurityDetails Table.

If Active is -1 then Ctl is -1
If Active is 0 then Ctl is 0

Ctl is a CheckBox.

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

 

[DSTR3]

This seems to be working better, at least I have a MsgBox popping up as a test. I still can't get the Checkbox to be checked off though. I put the PrivID in the Tag Property of the control.
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

 

[TheAceMan1]

How are ya DSTR3 . . .

You've posted [blue]two different secenario's[/blue] here! [purple]Be more specific[/purple] about what you want to accomplish . . .

See Ya! . . . . . .

 

[rjoubert]

Look at the line of code where you're trying to check the checkbox (Ctl.Value = -1). You have that line commented out...that's why your checkbox is not being "checked.

 

[DSTR3]

The reason there are 2 is that I'm movingtowars a solution here. I've isolated the problem.

ctl.tag = !PrivID.Value

It seems that if I do ctl.Tag = 1 it works, but the minute I refernce a field in the table. It doesn't take. Can you not refernce a field in a table. You can't reference a varaible? I don't understand, any help appreciated.
Thanks
DS

 

[rjoubert]

Try changing your field reference to .Fields("PrivID")

I'm not if it will help at all, but it's worth a shot.

 

[TheAceMan1]

DSTR3 . . .

The [blue]Tag[/blue] property is a string data type . . . if your trying to ping against numeric . . . well . . .

Perhaps something like:

[blue]    If [purple][b]Val([/b][/purple]Ctl.Tag[purple][b])[/b][/purple] = !PrivID.Value Then[/blue]

See Ya! . . . . . .

 

[DSTR3]

The Val() Thing works!
Thanks Everyone!
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