If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
updating check box totals
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? |
#2
|
|||
|
|||
updating check box totals
|
#3
|
|||
|
|||
updating check box totals
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 |
#4
|
|||
|
|||
updating check box totals
On 12 Nov 2006 19:25:56 -0800, wrote:
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 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) Subdatasheets are another possible problem; they can seriously degrade performance. month - lookup column linked to table with month (subdatasheet of above table) 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 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 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 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. 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] |
Thread Tools | |
Display Modes | |
|
|