Runtime Error 2165 - Hide a control that has the focus

[SpiritedAway]

I have a couple of questions regarding database/form design.

I have a table that has 212 fields (majority of these are simple yes/no tick boxes) - is that number of fields in a table to store data ok or could it be a problem?

(eventually I will break these down when I need to run query's or reports - I just wanted to keep all the data in one table instead of loads of smaller tables)

From that table I have made a form. I have divided the 212 would-be fields into 4 groups, IQ tickboxes, OQ tickboxes, PQ tickboxes, LV tickboxes.

I have 4 buttons, IQ, OQ, PQ, LV, depending on which button I press determines which tickboxes are visible or invisible.

However, I'm getting an error message that says, "Runtime Error 2165 - You can't hide a control that has the focus".

I have a field called "txtfieldSystem" that is always visible - regardless of which button I press. Is it possible that which ever IQ,OQ,OQ,LV button I press "txtfieldSystem" will have the focus - so that the focus won't be on control that is about to be hidden but on the field that is always visible?

Any help on this would be great.

I'm using Access 2007

 

[RainLover]

I think you would be better off using Excel.

 

[missinglinq]

Yes, just set focus to this control before the code that makes controls invisible:

txtfieldSystem.SetFocus

As to a table with 212 fields, that's well within the Access limit of 255, but I have to tell you that this is usually a sign of a non-normalized, poorly designed database. I say usually because, IMHO, there are very, very few rules that should never be broken. But most experienced developers consider a table with more than 25-35 fields to be a sign of trouble.

Perhaps we could better advise you if you could explain exactly what you're trying to do here.

 

[SpiritedAway]

I am trying to create a validation form

txtSystem
txtLine
txtEquipment
txtAsset
txtModel
txtSerial

These fields are always visible.

For each bit of equipment I have to answer these questions broken into four groups.

Group 1 - IQ (Installation Qualification
Equipment Identification has 3 yes/no questions + Finding and resolution fields
TxtEquipmentCheck1
TxtEquipmentCheck2
TxtEquipmentCheck3
TxtLevel2EquipFinding
TxtLevel3EquipFinding
TxtLevel3EquipResolution
Document Verification has 5 yes/no questions + Finding and resolution fields
TxtDocumentCheck1
TxtDocumentCheck2
TxtDocumentCheck3
TxtDocumentCheck4
TxtDocumentCheck5
TxtLevel2DocFinding
TxtLevel3DocFinding
TxtLevel3DocResolution
Drawing Checks has 5 yes/no questions + Finding and resolution fields
TxtDrawCheck1
TxtDrawCheck2
TxtDrawCheck3
TxtDrawCheck4
TxtDrawCheck5
TxtLevel2DrawFinding
TxtLevel3DrawFinding
TxtLevel3DrawResolution
Utilities Checks has 4 yes/no questions + Finding and resolution fields
TxtUtilityCheck1
TxtUtilityCheck2
TxtUtilityCheck3
TxtUtilityCheck4
TxtLevel2UtilityFinding
TxtLevel3UtilityFinding
TxtLevel3UtilityResolution
Maintenance Checks has 4 yes/no questions + Finding and resolution fields
TxtMaintenanceCheck1
TxtMaintenanceCheck2
TxtMaintenanceCheck3
TxtMaintenanceCheck4
TxtLevel2MainFinding
TxtLevel3MainFinding
TxtLevel3MainResolution
Product Checks has 4 yes/no questions + Finding and resolution fields
TxtProductCheck1
TxtProductCheck2
TxtProductCheck3
TxtProductCheck4
TxtLevel2ProductFinding
TxtLevel3ProductFinding
TxtLevel3ProductResolution
Calibration Checks has 3 yes/no questions + Finding and resolution fields
TxtCalibrationCheck1
TxtCalibrationCheck2
TxtCalibrationCheck3
TxtLevel2CalibrationFinding
TxtLevel3CalibrationFinding
TxtLevel3CalibrationResolution
Spare Parts Checks has 3 yes/no questions + Finding and resolution fields
TxtSpareCheck1
TxtSpareCheck2
TxtSpareCheck3
TxtLevel2SpareFinding
TxtLevel3SpareFinding
TxtLevel3SpareResolution
Software Checks has 4 yes/no questions + Finding and resolution fields
TxtSoftwareCheck1
TxtSoftwareCheck2
TxtSoftwareCheck3
TxtSoftwareCheck4
TxtLevel2SoftwareFinding
TxtLevel3SoftwareFinding
TxtLevel3SoftwareResolution

Then general information fields for IQ Group

IQDocumentID
IQReferenceID1
IQReferenceID2
IQReferenceID3
IQCompletedBy
IQCompletionDate
IQNotes

Group 2 - OQ (Operational Qualification)
Process Checks has 3 yes / no questions + Finding and resolution fields
TxtProcessChecks1
TxtProcessCheck2
TxtProcessCheck3
TxtLevel2ProcessFinding
TxtLevel3ProcessFinding
TxtLevel3ProcessResolution
SOP Checks has 6 yes / no questions + Finding and resolution fields
TxtSOPCheck1
TxtSOPCheck2
TxtSOPCheck3
TxtSOPCheck4
TxtSOPCheck5
TxtSOPCheck6
TxtLevel2SoftwareFinding
TxtLevel3SoftwareFinding
TxtLevel3SoftwareResolution
Power Checks has 4 yes/no questions + Finding and resolution fields
TxtPowerCheck1
TxtPowerCheck2
TxtPowerCheck3
TxtPowerCheck4
TxtLevel2PowerFinding
TxtLevel3PowerFinding
TxtLevel3PowerResolution
Automation Checks has 3 yes/no questions + finding and resolution fields
TxtAutoCheck1
TxtAutoCheck2
TxtAutoCheck3
TxtLevel2AutoFinding
TxtLevel3AutoFinding
TxtLevel3AutoResolution
Function Checks has 3 yes/no questions + finding and resolution fields
TxtFunctionCheck1
TxtFunctionCheck2
TxtFunctionCheck3
TxtLevel2FunctionFinding
TxtLevel3FunctionFinding
TxtLevel3FunctionResolution
Security Checks has 3 yes/no questions + finding and resolution fields
TxtSecurityCheck1
TxtSecurityCheck2
TxtSecurityCheck3
TxtLevel2SecurityFinding
TxtLevel3SecurityFinding
TxtLevel3SecurityResolution
Setting Checks has 3 yes/no questions + finding and resolution fields
TxtSettingCheck1
TxtSettingCheck2
TxtSettingCheck3
TxtLevel2SettingFinding
TxtLevel3SettingFinding
TxtLevel3SettingResolution

Then general information fields for OQ Group

OQDocumentID
OQReferenceID1
OQReferenceID2
OQReferenceID3
OQCompletedBy
OQCompletionDate
OQNotes

Group 3 - PQ (Performance Qualification)
SOPPQ Checks has 4 yes/no questions + finding and resolution fields
TxtSOPPQCheck1
TxtSOPPQCheck2
TxtSOPPQCheck3
TxtSOPPQCheck4
TxtLevel2SOPPQFinding
TxtLevel3SOPPQFinding
TxtLevel3SOPPQResolution
Sampling Checks has 3 yes/no questions + finding and resolution fields
TxtSamplingCheck1
TxtSamplingCheck2
TxtSamplingCheck3
TxtLevel2SamplingFinding
TxtLevel3SamplingFinding
TxtLevel3SamplingResolution
Performance Checks has 3 yes/no questions + finding and resolution fields
TxtPerformanceCheck1
TxtPerformanceCheck2
TxtPerformanceCheck3
TxtLevel2PerformanceFinding
TxtLevel3PerformanceFinding
TxtLevel3PerformanceResolution
SettingPQ Checks has 3 yes/no questions + finding and resolution fields
TxtSettingPQCheck1
TxtSettingPQCheck2
TxtSettingPQCheck3
TxtLevel2SettingPQFinding
TxtLevel3SettingPQFinding
TxtLevel3SettingPQResolution

Then general information fields for PQ Group

PQDocumentID
PQReferenceID1
PQReferenceID2
PQReferenceID3
PQCompletedBy
PQCompletionDate
PQNotes

Group 4 - LV (Legacy Qualification)
Identification Checks has 3 yes/no questions + finding and resolution fields
TxtLVIDCheck1
TxtLVIDCheck2
TxtLVIDCheck3
TxtLevel2LVIDFinding
TxtLevel3LVIDFinding
TxtLevel3LVIDResolution
Process Checks has 3 yes/no questions + finding and resolution fields
TxtLVprocessCheck1
TxtLVprocessCheck2
TxtLVprocessCheck3
TxtLevel2LVprocessFinding
TxtLevel3LVprocessFinding
TxtLevel3LVprocessResolution
Calibration Checks has 3 yes/no questions + finding and resolution fields
TxtLVCalibCheck1
TxtLVCalibCheck2
TxtLVCalibCheck3
TxtLevel2LVCalibFinding
TxtLevel3LVCalibFinding
TxtLevel3LVCalibResolution
Maintenance Checks has 3 yes/no questions + finding and resolution fields
TxtLVMainCheck1
TxtLVMainCheck2
TxtLVMainCheck3
TxtLevel2LVMainFinding
TxtLevel3LVMainFinding
TxtLevel3LVMainResolution
Procedures Checks has 3 yes/no questions + finding and resolution fields
TxtLVProCheck1
TxtLVProCheck2
TxtLVProCheck3
TxtLevel2LVProFinding
TxtLevel3LVProFinding
TxtLevel3LVProResolution
Then general information fields for LV Group

LVDocumentID
LVReferenceID1
LVReferenceID2
LVReferenceID3
LVCompletedBy
LVCompletionDate
LVNotes



I have 4 buttons:

IQBttn
OQBttn
PQBttn
LVBttn

Depending on which of the IQ,OQ,PQ,LV buttons I press the others become invisible.

I know I could have used a standard tabbed form to do this - but I prefer to make control appear and disappear on a click of a button.


Regards setting the focus on a visible form when I press either IQ, OQ, PQ, LV button would it be on that buttons before update or after update?

 

[Atomic Shrimp]

You definitely have a normalzation issue. Instead of storing (say) three check values as three fields, a better way is to store them as three records (each with check name and check value) in a child table, with a key to link to the parent record.

The same holds true for nearly all of your list of fields - they're similar values, they should be a vertical list, not a horizontal array.