Requeried Checkboxes Connected to Expressions Won't Stay Checked

[staciann]

Hi. I have an Access Form (2007) that captures all of the user, hardware, and software information for a certain computer.

This form has 4 tabs:
(1)Manufacturer Information
(2)Specifications
(3)Software
(4)Additional Information

On the Software tab (which is part of the main form), there is a list of all available software programs the company has licenses for. There are checkboxes next to program. I have queries and expressions set up so that Access can calculate how many licenses are still open out of the number currently available based upon the which checkboxes are checked off for each record. I have the calculation set up so it will subtract 1 license (if checked) or add 1 license(if unchecked)This calculation works perfectly and displays as "3 of 27 licenses available."

The problem is, it will only requery the total available (the first number - 3) after closing and reopening the form. I would like it to update the number available as soon as a checkbox is checked or unchecked.

I am not that good with VBA code, but I tried putting in a DoCmd.Requery; however, this does not keep the checkbox checked, it only subtracts 1 from the number available - therefore I can click the same checkbox 20 times on the same record and it will subtract 20 (which is not helpful if software is being removed from the computer and I want it to add 1).

I would appreciate any suggestions or coding samples you can offer.

Thanks,
Staci

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

Can you share your significant table and field names as well as data types?

I expect your tables might not be normalized.

Duane
Hook'D on Access
MS Access MVP

 

[staciann]

Thanks Duane. The main table involved on this form is tbl_CompInfo. The fields / data types are:
(I put asterisks next to the fields on this tab that are affected.)

Field Data Type

ID AutoNumber
CompMan Text
CompModel Text
CompType Text
CompName Text
ServiceTag Text
Location Text
Asset Yes/No
Asset# Text
AssetLife Text
DateService Date/Time
DateDecom Date/Time
PurchaseDate Date/Time
WarrantyStart Date/Time
WarrantyEnd Date/Time
AddWarranty Yes/No
AddWarStart Date/Time
AddWarEnd Date/Time
WindowsEd Text
ServicePk Number
OpSys Text
Processor Text
RAM Text
System Text
HardDrive Text
OfficeVer Text
IE Text
FF Text
MonMan Text
MonSize Text
MonSpeaker Text
FlashDrive Yes/No
Access* Yes/No
Excel* Yes/No
Outlook* Yes/No
PP* Yes/No
Project* Yes/No
Pub* Yes/No
Word* Yes/No
Visio* Yes/No
Java* Yes/No
AdobeReader* Yes/No
AdobeFlash* Yes/No
CrystalReports* Yes/No
CrystalViewer* Yes/No
Monarch* Yes/No
Quickbooks* Yes/No
Toad* Yes/No
SAS* Yes/No
WinNolyn* Yes/No
AdobeCS* Yes/No
Solomon* Yes/No
Skype* Yes/No
Other Text
Other2 Text
Other3 Text
RemoteAbility Yes/No
BatteryBackup Yes/No
Comments Memo
AssignedTo Text
SeatsAvailable Number
MemoryCardReader Yes/No
DVDWriter Yes/No

The other table involved is tbl_Software. This table has fields for each type of software and then has only one record that holds the number of licenses for each. The fields are:

Field Data Type

Access Number
Excel Number
Outlook Number
PP Number
Project Number
Pub Number
Word Number
Visio Number
Java Number
AdobeReader Number
AdobeFlash Number
CrystalReports Number
CrystalViewer Number
Monarch Number
Quickbooks Number
Toad Number
SAS Number
WinNolyn Number
AdobeCS Number
Solomon Number
Skype Number

I then have two queries:
One query (qry_SumInUse) calculates the number of "Yes" records in of each of the asterisked fields of tbl_CompInfo and the other query (qry_SumSoftware) calculates how many total licenses there are from tbl_Software.

The calculations work correctly, it just doesn't update right away...

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

IMO, I would change the table structure to normalize but I expect you don't want to change the tables.

We don't know the expressions you are using in your calculations. You may need to save a record after the check boxes are updated so the requery works as expected. Just changing the value of a field doesn't save the record to the table.

Duane
Hook'D on Access
MS Access MVP

 

[staciann]

Thanks Duane.
I'm not quite sure what you mean by "normalize".

The requery works fine with the calculations, the checkboxes just don't stay checked. It subtracts one and then remains unchecked. Is there a VBA line I can put in to simply make the checkbox stay checked or unchecked?

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[staciann]

OK, so strangely enough, I just figured out that if I put the requery in as a macro instead of the VBA, it works just fine. I generally try not to use macros...can you think of any reason why the macro would work but the VBA wouldn't?

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

I suggest you google "normalization". Assume you want to add another software application (SnagIt) to your database. With a normalized database you would not be adding fields to tables/queries and controls to forms and reports. An un-normalized database can be a maintenance nightmare.

If you don't understand why your table is (IMO) wrong, you might want to post your structure to the "Access Tables and Relationships" forum with a question of "how should this be structured".

We still don't know the expressions you are using in your calculations or the code you tried etc.



Duane
Hook'D on Access
MS Access MVP