Need help with "Binary field replaces 7 individual checkboxes"

[Tom]

Hello Experts,

I have a form that contains 7 checkboxes. All of the checkboxes cover the
"same subject". Currently, all of them are linked to their own field in a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my form...
specifically as it relates to saving the on/off values of the 7 checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2, Check3,
..... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I use
to get this to work?

Tom

P.S. I posted a sample file at:
http://www.savefile.com/files.php?fid=1831514

 

[Marshall Barton]

I have a form that contains 7 checkboxes. All of the checkboxes cover the
"same subject". Currently, all of them are linked to their own field in a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my form...
specifically as it relates to saving the on/off values of the 7 checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2, Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I use
to get this to work?

With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Tom]

Marshall,

thanks for the reply... ok, my goal is not to compress data. I simply want
to reduce the number of required fields from 7 to 1 (as part of streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null" error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?

Tom

I have a form that contains 7 checkboxes. All of the checkboxes cover
the
"same subject". Currently, all of them are linked to their own field in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7 checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I use
to get this to work?

With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Marshall Barton]

I guess I got lost amongst the explanation in youroriginal
post and used the wrong name for the table field. I now
think the field is named CheckAll. Are you sure you are
assigning the value of SecondaryTOA to the field?

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
Me.CheckAll = SecondaryTOA

With that assumption, my code would be modified to:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(Me.CheckAll, 0) And 2 ^ i)
Next i
--
Marsh
MVP [MS Access]

thanks for the reply... ok, my goal is not to compress data. I simply want
to reduce the number of required fields from 7 to 1 (as part of streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null" error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?

I have a form that contains 7 checkboxes. All of the checkboxes cover
the
"same subject". Currently, all of them are linked to their own field in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7 checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I use
to get this to work?

With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Tom]

Marshall,

sorry for the confusion... but now I'm confused about your response.

Ok, quick recap:

Table has 1 field: SecondaryTOA
- this field is the one that will store the binary value of the 7 checkboxes

Form has 7 checkboxes.
- their control names equal Check1, Check2, Check3, Check4, Check5, Check6,
Check7

Note: Nowhere do I use "CheckAll".... (that was just part of my
explanation)... sorry for the confusion.


So, how do I modify the >> Me.CheckAll = SecondaryTOA << since I
don't have the "CheckAll"?

Tom

I guess I got lost amongst the explanation in youroriginal
post and used the wrong name for the table field. I now
think the field is named CheckAll. Are you sure you are
assigning the value of SecondaryTOA to the field?

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
Me.CheckAll = SecondaryTOA

With that assumption, my code would be modified to:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(Me.CheckAll, 0) And 2 ^ i)
Next i
--
Marsh
MVP [MS Access]

thanks for the reply... ok, my goal is not to compress data. I simply
want
to reduce the number of required fields from 7 to 1 (as part of
streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null" error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?

Tom wrote:
I have a form that contains 7 checkboxes. All of the checkboxes cover
the
"same subject". Currently, all of them are linked to their own field
in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only
have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7
checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I
use
to get this to work?


With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Marshall Barton]

Ok, that's pretty definitive. I was thrown off by what's
probably the problem. You have the line Dim SecondaryTOA in
your code, which creates a local variable that overrides the
the field name, so get rid of that and initialize the
field's value instead:

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next

And the code in the Current event would go back to what I
originaly suggested:

Dim i As Long
For i = 0 To 6
Me("Check" & i+1)=CBool(Nz(Me.SecondaryTOA, 0) And 2^i)
Next i
--
Marsh
MVP [MS Access]

sorry for the confusion... but now I'm confused about your response.

Ok, quick recap:

Table has 1 field: SecondaryTOA
- this field is the one that will store the binary value of the 7 checkboxes

Form has 7 checkboxes.
- their control names equal Check1, Check2, Check3, Check4, Check5, Check6,
Check7

Note: Nowhere do I use "CheckAll".... (that was just part of my
explanation)... sorry for the confusion.

So, how do I modify the >> Me.CheckAll = SecondaryTOA << since I
don't have the "CheckAll"?

I guess I got lost amongst the explanation in youroriginal
post and used the wrong name for the table field. I now
think the field is named CheckAll. Are you sure you are
assigning the value of SecondaryTOA to the field?

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
Me.CheckAll = SecondaryTOA

With that assumption, my code would be modified to:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(Me.CheckAll, 0) And 2 ^ i)
Next i
--
Marsh
MVP [MS Access]

thanks for the reply... ok, my goal is not to compress data. I simply
want
to reduce the number of required fields from 7 to 1 (as part of
streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null" error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?


Tom wrote:
I have a form that contains 7 checkboxes. All of the checkboxes cover
the
"same subject". Currently, all of them are linked to their own field
in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only
have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7
checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I
use
to get this to work?


With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Tom]

Marsh:

okay, I'm still not clear "what needs to go where"... sorry?!?! Both of
the 2 For loops don't go into the form's OnCurrent right? Where do the 6
lines go (initialization)? I placed the 4 lines into the OnCurrent?

Tom


Private Sub Form_Current()

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next


Dim i As Long
For i = 0 To 6
Me("Check" & i + 1) = CBool(Nz(Me.SecondaryTOA, 0) And 2 ^ i)
Next i

End Sub

Ok, that's pretty definitive. I was thrown off by what's
probably the problem. You have the line Dim SecondaryTOA in
your code, which creates a local variable that overrides the
the field name, so get rid of that and initialize the
field's value instead:

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next

And the code in the Current event would go back to what I
originaly suggested:

Dim i As Long
For i = 0 To 6
Me("Check" & i+1)=CBool(Nz(Me.SecondaryTOA, 0) And 2^i)
Next i
--
Marsh
MVP [MS Access]

sorry for the confusion... but now I'm confused about your response.

Ok, quick recap:

Table has 1 field: SecondaryTOA
- this field is the one that will store the binary value of the 7
checkboxes

Form has 7 checkboxes.
- their control names equal Check1, Check2, Check3, Check4, Check5,
Check6,
Check7

Note: Nowhere do I use "CheckAll".... (that was just part of my
explanation)... sorry for the confusion.

So, how do I modify the >> Me.CheckAll = SecondaryTOA << since I
don't have the "CheckAll"?

I guess I got lost amongst the explanation in youroriginal
post and used the wrong name for the table field. I now
think the field is named CheckAll. Are you sure you are
assigning the value of SecondaryTOA to the field?

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
Me.CheckAll = SecondaryTOA

With that assumption, my code would be modified to:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(Me.CheckAll, 0) And 2 ^ i)
Next i
--
Marsh
MVP [MS Access]


Tom wrote:
thanks for the reply... ok, my goal is not to compress data. I simply
want
to reduce the number of required fields from 7 to 1 (as part of
streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null"
error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?


Tom wrote:
I have a form that contains 7 checkboxes. All of the checkboxes
cover
the
"same subject". Currently, all of them are linked to their own
field
in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only
have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a
FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7
checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I
use
to get this to work?


With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Marshall Barton]

Right. Place your six lines that save the data in the
Form's BeforeUpdate event.
--
Marsh
MVP [MS Access]

okay, I'm still not clear "what needs to go where"... sorry?!?! Both of
the 2 For loops don't go into the form's OnCurrent right? Where do the 6
lines go (initialization)? I placed the 4 lines into the OnCurrent?


Private Sub Form_Current()

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next


Dim i As Long
For i = 0 To 6
Me("Check" & i + 1) = CBool(Nz(Me.SecondaryTOA, 0) And 2 ^ i)
Next i

End Sub

Ok, that's pretty definitive. I was thrown off by what's
probably the problem. You have the line Dim SecondaryTOA in
your code, which creates a local variable that overrides the
the field name, so get rid of that and initialize the
field's value instead:

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next

And the code in the Current event would go back to what I
originaly suggested:

Dim i As Long
For i = 0 To 6
Me("Check" & i+1)=CBool(Nz(Me.SecondaryTOA, 0) And 2^i)
Next i

sorry for the confusion... but now I'm confused about your response.

Ok, quick recap:

Table has 1 field: SecondaryTOA
- this field is the one that will store the binary value of the 7
checkboxes

Form has 7 checkboxes.
- their control names equal Check1, Check2, Check3, Check4, Check5,
Check6,
Check7

Note: Nowhere do I use "CheckAll".... (that was just part of my
explanation)... sorry for the confusion.

So, how do I modify the >> Me.CheckAll = SecondaryTOA << since I
don't have the "CheckAll"?

I guess I got lost amongst the explanation in youroriginal
post and used the wrong name for the table field. I now
think the field is named CheckAll. Are you sure you are
assigning the value of SecondaryTOA to the field?

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
Me.CheckAll = SecondaryTOA

With that assumption, my code would be modified to:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(Me.CheckAll, 0) And 2 ^ i)
Next i
--
Marsh
MVP [MS Access]


Tom wrote:
thanks for the reply... ok, my goal is not to compress data. I simply
want
to reduce the number of required fields from 7 to 1 (as part of
streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null"
error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?


Tom wrote:
I have a form that contains 7 checkboxes. All of the checkboxes
cover
the
"same subject". Currently, all of them are linked to their own
field
in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only
have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a
FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7
checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I
use
to get this to work?


With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Tom]

Marsh...

it's still not working.

Tom

P.S. I'm not sure if you look at sample files, but if you do, I posted it
at:
http://www.savefile.com/files.php?fid=2356399

Any ideas what I'm still missing?

Right. Place your six lines that save the data in the
Form's BeforeUpdate event.
--
Marsh
MVP [MS Access]

okay, I'm still not clear "what needs to go where"... sorry?!?! Both of
the 2 For loops don't go into the form's OnCurrent right? Where do the 6
lines go (initialization)? I placed the 4 lines into the OnCurrent?


Private Sub Form_Current()

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next


Dim i As Long
For i = 0 To 6
Me("Check" & i + 1) = CBool(Nz(Me.SecondaryTOA, 0) And 2 ^ i)
Next i

End Sub

Ok, that's pretty definitive. I was thrown off by what's
probably the problem. You have the line Dim SecondaryTOA in
your code, which creates a local variable that overrides the
the field name, so get rid of that and initialize the
field's value instead:

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next

And the code in the Current event would go back to what I
originaly suggested:

Dim i As Long
For i = 0 To 6
Me("Check" & i+1)=CBool(Nz(Me.SecondaryTOA, 0) And 2^i)
Next i


Tom wrote:
sorry for the confusion... but now I'm confused about your response.

Ok, quick recap:

Table has 1 field: SecondaryTOA
- this field is the one that will store the binary value of the 7
checkboxes

Form has 7 checkboxes.
- their control names equal Check1, Check2, Check3, Check4, Check5,
Check6,
Check7

Note: Nowhere do I use "CheckAll".... (that was just part of my
explanation)... sorry for the confusion.

So, how do I modify the >> Me.CheckAll = SecondaryTOA << since
I
don't have the "CheckAll"?



I guess I got lost amongst the explanation in youroriginal
post and used the wrong name for the table field. I now
think the field is named CheckAll. Are you sure you are
assigning the value of SecondaryTOA to the field?

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
Me.CheckAll = SecondaryTOA

With that assumption, my code would be modified to:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(Me.CheckAll, 0) And 2 ^ i)
Next i
--
Marsh
MVP [MS Access]


Tom wrote:
thanks for the reply... ok, my goal is not to compress data. I
simply
want
to reduce the number of required fields from 7 to 1 (as part of
streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null"
error
disappeared.

But it doesn't save the checked checkboxes into my field
"SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?


Tom wrote:
I have a form that contains 7 checkboxes. All of the checkboxes
cover
the
"same subject". Currently, all of them are linked to their own
field
in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I
only
have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a
FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a
binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7
checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify
my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could
I
use
to get this to work?


With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

 

[Marshall Barton]

it's still not working.

Just post a Copy/Paste of the form's Current and
BeforeUpdate event procedures.

 

[Tom]

Marsh,

thanks for your help on this... here's the code.

Tom


Private Sub Form_Current()

Dim i As Long
For i = 0 To 6

Me("Check" & i + 1) = CBool(Nz(Me.SecondaryTOA, 0) And 2 ^ i)
Next i

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next

End Sub

 

[Marshall Barton]

I don't see anything wrong with the code Tom.

What about it is not working?

Did you double check all the names to make sure there are no
spelling differences?

 

[Tom]

I did check the 7 unbound control names... Their names are Check1, Check2,
Check3, ... Check7.

The OnCurrent and BeforeUpdate are properly referenced -- [Event
Procedure] -- in the form.

Opening up one record, checking some boxes, moving onto 2nd record, checking
boxes, returning to 1st record... the previously checked checkboxes are not
checked any longer.

Thank you for your support... but I'm giving up on this... it's not worth
pursuing it further.

Tom

I don't see anything wrong with the code Tom.

What about it is not working?

Did you double check all the names to make sure there are no
spelling differences?
--
Marsh
MVP [MS Access]

thanks for your help on this... here's the code.

Private Sub Form_Current()

Dim i As Long
For i = 0 To 6

Me("Check" & i + 1) = CBool(Nz(Me.SecondaryTOA, 0) And 2 ^ i)
Next i

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim i As Long
Me.SecondaryTOA = 0
For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
Me.SecondaryTOA = Me.SecondaryTOA Or 2 ^ i
End If
Next

End Sub


"Marshall Barton" wrote