updating check box totals

  • Thread starter Thread starter myspareone
  • Start date Start date
M

myspareone

Hi,

I am designing a form that keeps track of days that support has been
provided to different organisations and have created a checkbox for
each day of the month and have them laid out in a calendar type array.

because there are three organisations we regularly work with i have
created specific boxes for them (ie Company1_1 thru Company1_31,
Company2_1 thru Company2_31 etc) and am showing them all on the page.

im using the following rule to update totals:
=================================
In the AfterUpdate event of each of the checkboxes, put:

Me.txtTotal = Abs(Me.chkbox1 + ... ... + Me.chkbox7)


where txtTotal is the name of the text box where you want to show the
total.


Because a checkbox returns 0 or -1, you need Abs() to give a positive
result. Because you probably want the total to change as you
check/uncheck boxes, you need to put the code in each checkbox's code.
==============================
This works perfectly when I only have these three companies showing.
When i try to create another monthly calendar that will allow us to add
in the name of another organisation (Org1 thru Org31) the script will
not recognise it.

Is there another more efficient way of doing this?
 
Hi,

I am designing a form that keeps track of days that support has been
provided to different organisations and have created a checkbox for
each day of the month and have them laid out in a calendar type array.
Click to expand...

It sounds likeyou're starting the design with your Form rather than
with the Table. That's the wrong way about!
because there are three organisations we regularly work with i have
created specific boxes for them (ie Company1_1 thru Company1_31,
Company2_1 thru Company2_31 etc) and am showing them all on the page.
Click to expand...

Forget the form for the moment: we can get back to it once you have
your table design established (or explained).

If you have 93 fieldnames, or textboxes, it's going to be
nightmarishly difficult for the user. And if you have to add a fourth
or fifth company... sheesh! You'll be out of luck!

What is the structure OF YOUR TABLES, where the data will be stored?

John W. Vinson[MVP]
 
John,

I suspect that as you have hinted at, i have not designed my table as
well as i could have.
I currently have over 130 fields (the majority of which are check
boxes). Definately didnt intend to get that big but ended up that way
after getting carried away.

current table structure is:
ID - autonumber
Unit - lookup column linked to table identifying specific units
year - lookup column linked to table with years (subdatasheet of above
table)
month - lookup column linked to table with month (subdatasheet of above
table)
unit1 - linked to lookup column
year1 - linked to year column
month1 - linked to month column
AvailabilityDays - number
MaintenanceDays - number
DaysTasked - number
CancelledByThem - number
CancelledByUs - number
Comments - memo
doc1 thru doc31 - checkboxes
total_doc - textbox
nzcs1 thru nzcs31 - checkboxes
total_nzcs - textbox
mfat1 thru mfat31 - checkboxes
total_mfat - textbox
org1 thru org31 checkboxes
Total_org - textbox

good news is that i can easily erase the lot and start again once i
know a more efficient way of displaying the information.

Steve
 
John,

I suspect that as you have hinted at, i have not designed my table as
well as i could have.
I currently have over 130 fields (the majority of which are check
boxes). Definately didnt intend to get that big but ended up that way
after getting carried away.

current table structure is:
ID - autonumber
Unit - lookup column linked to table identifying specific units
Click to expand...

Well... that's a problem right there. See
http://www.mvps.org/access/lookupfields.htm for a critique of the
Lookup feature.

A Lookup Field in a table is NEVER necessary, and can be confusing. If
you create a query searching or sorting on this Unit field it will
give unexpected results, because the table does NOT contain the unit -
even though it appears to do so.
year - lookup column linked to table with years (subdatasheet of above
table)
Click to expand...

Subdatasheets are another possible problem; they can seriously degrade
performance.
month - lookup column linked to table with month (subdatasheet of above
table)
Click to expand...

Month is a reserved name, as is Year. You should REALLY consider just
storing a Date/Time field. Just use the first of the month as the
(required) day.
unit1 - linked to lookup column
year1 - linked to year column
month1 - linked to month column
Click to expand...

This is a one (ID) to many (unit) relationship. Model it as a one to
many relationship - with TWO TABLES related one to many.
AvailabilityDays - number
MaintenanceDays - number
DaysTasked - number
CancelledByThem - number
CancelledByUs - number
Comments - memo
doc1 thru doc31 - checkboxes
Click to expand...

again... a one to many relationship. If the checkboxes are days in
year and month, a table with ID, the date, and a yes/no field might be
much better.
total_doc - textbox
Click to expand...

The total should be calculated as needed - NOT stored in any table
field.
nzcs1 thru nzcs31 - checkboxes
total_nzcs - textbox
mfat1 thru mfat31 - checkboxes
total_mfat - textbox
org1 thru org31 checkboxes
Total_org - textbox

good news is that i can easily erase the lot and start again once i
know a more efficient way of displaying the information.
Click to expand...

Store the data in properly normalized tables, and use an unbound Form
with VBA code to move the data in and out of the form.

John W. Vinson[MVP]
 
Back
Top