Update a description field from data entered into a form

[Guest]

I have a form with 16 checkboxes. Each one Q01 through Q016 represent a
quarter of a quarter of a square mile. They are layed out in a square with 4
checkboxes in each quarter of the grid. The description will be built for
each quarter of the grid. For example, the upper quarter is the north east
quarter of the square mile. It is layed out like this:

2 1
3 4

If only Q01 is checked the description is "NE 1/4 - NE 1/4"
If only Q02 is checked the description is "NW 1/4 - NE 1/4"
If only Q03 is checked the description is "SW 1/4 - NE 1/4"
If only Q04 is checked the description is "SE 1/4 - NE 1/4"
If Q02 and Q01 are checked the description is N "1/2 - NE 1/4"
If Q03 and Q04 are checked the description is S "1/2 - NE 1/4"
If Q02 and Q03 are checked the description is W "1/2 - NE 1/4"
If Q01 and Q04 are checked the description is "E 1/2 - NE 1/4"
If all four are checked the description is "NE 1/4"

I'm a VBA novice. I am thinking that I need to assign a value of 1 through
9 to each of these circumstances. I have created a table with the thirty six
allowable values and descriptions. I'm thinking that I can do a dblookup in
that table and use the related to update the "Land Description" field on my
main form.

The Quarter Selection form has a button named Update Description. This
module is triggered by the button's click event.

Please help.

 

[Marshall Barton]

I have a form with 16 checkboxes. Each one Q01 through Q016 represent a
quarter of a quarter of a square mile. They are layed out in a square with 4
checkboxes in each quarter of the grid. The description will be built for
each quarter of the grid. For example, the upper quarter is the north east
quarter of the square mile. It is layed out like this:

2 1
3 4

If only Q01 is checked the description is "NE 1/4 - NE 1/4"
If only Q02 is checked the description is "NW 1/4 - NE 1/4"
If only Q03 is checked the description is "SW 1/4 - NE 1/4"
If only Q04 is checked the description is "SE 1/4 - NE 1/4"
If Q02 and Q01 are checked the description is N "1/2 - NE 1/4"
If Q03 and Q04 are checked the description is S "1/2 - NE 1/4"
If Q02 and Q03 are checked the description is W "1/2 - NE 1/4"
If Q01 and Q04 are checked the description is "E 1/2 - NE 1/4"
If all four are checked the description is "NE 1/4"

I'm a VBA novice. I am thinking that I need to assign a value of 1 through
9 to each of these circumstances. I have created a table with the thirty six
allowable values and descriptions. I'm thinking that I can do a dblookup in
that table and use the related to update the "Land Description" field on my
main form.

The Quarter Selection form has a button named Update Description. This
module is triggered by the button's click event.

I think having a table of legal descriptions is a good idea
(even if I can't see how you came up with 36 of them).

Here's an idea on how to translate the check boxes to an
index number. Set each check box's value to a unique
**integer** value instead of just true/false. For example,
set Q01 to the hex value &H11, Q02 to &H12, Q03 to &H14 and
Q04 to &H18. Similarly for Q05 to &H21, Q06 to&H22 and so
on. (The first hex digit is the quarter mile and the second
hex digit is quadrant in the quarter.)

Note that if the check boxes are bound to fields in a table,
the table fields will have to be Integer type fields, not
Yes/No fields. Each check box's AfterUpdate event procedure
would have to be used to set the value using code like:
Me.Q01 = -Me.Q01 * &H11

This way you can just OR all the check box values together
to calculate the index number of all 16 check boxes. For
example, if Q02 and Q03 were checked, the OR calculation
would have an index of &H16 and if all four are checked the
index would be &H1F.

Then the button's code would use code like this to calculate
the index:

Dim k As Integer
Dim ndx As Integer
Dim desc as Variant
For k = 1 to 16
ndx = ndx OR Me("Q0" & k)
Next k
desc = DLookup("Description", _
"Descriptions", "DescNdx = " & ndx)
If IsNull(desc) Then
MsgBox "Invalid combination"
Else
Me.[Land Description] = desc
End If

 

[Tim Ferguson]

=?Utf-8?B?QmlsbCBDdW5uaW5naGFt?=

First of all, let me say that I don't understand the problem, but let me
add some comments along the way...

I have a form with 16 checkboxes. Each one Q01 through Q016

.... wouldn't {Q01 to Q16} or {Q001 to Q016} be a bit more sensible?

represent

a quarter of a quarter of a square mile. They are layed out in a
square with 4 checkboxes in each quarter of the grid. The description
will be built for each quarter of the grid. For example, the upper
quarter is the north east quarter of the square mile. It is layed out
like this:

2 1
3 4

Do the numbers refer to the little square or the big sqares... is
NorthEast square 3 close to the middle or out on a corner?

If only Q01 is checked the description is "NE 1/4 - NE 1/4"
If only Q02 is checked the description is "NW 1/4 - NE 1/4"
If only Q03 is checked the description is "SW 1/4 - NE 1/4"
If only Q04 is checked the description is "SE 1/4 - NE 1/4"
If Q02 and Q01 are checked the description is N "1/2 - NE 1/4"

Now I am lost completely. Why is this suddenly a half rather than a
quarter? Why does 1 refer to a single square, but the rest refer to two
squares?

I'm a VBA novice.

I think this is a maths problem first, and a programming one much later.

I am thinking that I need to assign a value of 1
through 9 to each of these circumstances.

Nine what? Surely there are sixteen possible answers?

I have created a table with
the thirty six allowable values and descriptions.

I can see that 36 is four times nine, but I don't see where any of these
numbers come from. Also bear in mind that having sixteen independent
checkboxes means you have 256 possible combinations, so you'll need a
robust method of preventing your users from entering impossible ones.
What is impossible, anyway?

What about alternative input methods: 36 radio buttons; a list box;
something graphical? What about a bitmap in a picture box and trapping
the MouseDown event?

Another way only uses four checkboxes:
8 northern hemisphere
4 eastern hemisphere
2 on the northern or southern edge
1 on the eastern or western edge

i.e.

11 10 14 15
9 8 12 13
1 0 4 5
3 2 6 7

There are, of course, many other possible numbering sequences.

I'm thinking that I
can do a dblookup in that table and use the related to update the
"Land Description" field on my main form.

Yes you can... it's a simple matter to turn any number of check box
values into a single integer.

The Quarter Selection form has a button named Update Description.
This module is triggered by the button's click event.

Okay.

Hope that helps, a bit...

Tim F

 

[Guest]

Marsh,

Thanks, It looks like this just might work with some slight variation. The
form with a checkboxes is oppened with a click button on the main form. The
main form remains opened but without focus. It looks like I can replace the
Me with the open main form object. I'll give it a try.

Thanks again,

Bill

I have a form with 16 checkboxes. Each one Q01 through Q016 represent a
quarter of a quarter of a square mile. They are layed out in a square with 4
checkboxes in each quarter of the grid. The description will be built for
each quarter of the grid. For example, the upper quarter is the north east
quarter of the square mile. It is layed out like this:

2 1
3 4

If only Q01 is checked the description is "NE 1/4 - NE 1/4"
If only Q02 is checked the description is "NW 1/4 - NE 1/4"
If only Q03 is checked the description is "SW 1/4 - NE 1/4"
If only Q04 is checked the description is "SE 1/4 - NE 1/4"
If Q02 and Q01 are checked the description is N "1/2 - NE 1/4"
If Q03 and Q04 are checked the description is S "1/2 - NE 1/4"
If Q02 and Q03 are checked the description is W "1/2 - NE 1/4"
If Q01 and Q04 are checked the description is "E 1/2 - NE 1/4"
If all four are checked the description is "NE 1/4"

I'm a VBA novice. I am thinking that I need to assign a value of 1 through
9 to each of these circumstances. I have created a table with the thirty six
allowable values and descriptions. I'm thinking that I can do a dblookup in
that table and use the related to update the "Land Description" field on my
main form.

The Quarter Selection form has a button named Update Description. This
module is triggered by the button's click event.

I think having a table of legal descriptions is a good idea
(even if I can't see how you came up with 36 of them).

Here's an idea on how to translate the check boxes to an
index number. Set each check box's value to a unique
**integer** value instead of just true/false. For example,
set Q01 to the hex value &H11, Q02 to &H12, Q03 to &H14 and
Q04 to &H18. Similarly for Q05 to &H21, Q06 to&H22 and so
on. (The first hex digit is the quarter mile and the second
hex digit is quadrant in the quarter.)

Note that if the check boxes are bound to fields in a table,
the table fields will have to be Integer type fields, not
Yes/No fields. Each check box's AfterUpdate event procedure
would have to be used to set the value using code like:
Me.Q01 = -Me.Q01 * &H11

This way you can just OR all the check box values together
to calculate the index number of all 16 check boxes. For
example, if Q02 and Q03 were checked, the OR calculation
would have an index of &H16 and if all four are checked the
index would be &H1F.

Then the button's code would use code like this to calculate
the index:

Dim k As Integer
Dim ndx As Integer
Dim desc as Variant
For k = 1 to 16
ndx = ndx OR Me("Q0" & k)
Next k
desc = DLookup("Description", _
"Descriptions", "DescNdx = " & ndx)
If IsNull(desc) Then
MsgBox "Invalid combination"
Else
Me.[Land Description] = desc
End If

 

[Guest]

Tim,
Thanks, sorry for the partial communication. The 16 checkboxes represent
quarter/quarters of a square mile. I layed out only the NW quarter mile. I
was thinking that the code would be the similar for the other 3 quarters.
The logic would remain the same. So in the northeast quarter there are four
checkboxes. The user must enter contigous quarter/quarters. So the
allowable(not Possible) options are;
1
2
3
4
1&2
2&3
3&4
1&4
all four.

That's nine acceptable selections, times the four quarters is 36.

Just thought I would clear up the math issue.

Thanks again,

Bill

 

[Tim Ferguson]

=?Utf-8?B?QmlsbCBDdW5uaW5naGFt?=

The user must enter contigous quarter/quarters. So the
allowable(not Possible) options are;
1
2
3
4
1&2
2&3
3&4
1&4
all four.

That's nine acceptable selections, times the four quarters is 36.

Aha: got it now. Just for the sake of argument, what about 1&2*3, or
NorthWest(1) & NorthEast(2), which are also contiguous? Okay, I don't
rally need to know this, it just seems like an intereting mathematical
puzzle. Something inside tells me that there is a simple method using the
bit-patterns to identify offending patterns. Basically, though, Marshall
and I are coming from the same place, so I'll leave you to him!

All the best


Tim F