Can't Create Any More Controls

[escoyne]

I'm building my database and now I'm getting an error message when I'm trying to add more check boxes, text boxes, etc.

Its says "Microsoft Office Access can't create any more controls on this form or report. If you have deleted controls from this form or report in the past, you may be ableto rename the form or report and then add more controls to it."

I have I reached my limit on controls or can I fix this problem?

Jason

 

[Scooterbug]

How many controls do you currently have on the form? Have you deleted and added a lot of controls? Access keeps track of all the controls that have been added, including those that have been deleted.

If it's the later, you can try to copy the form and see if that resets the number.

 

[boblarson]

The limit, over the lifetime of the form or report, is 754 controls. If you have exceeded that (including creating and then deleting them) the best way to reset is to import everything into a new, blank Access mdb/accdb file. That will definitely reset it all.

I don't trust the part about renaming or copying a form, but it could help. But first, make sure Name AutoCorrupt (I mean Name AutoCorrect) is turned off.

 

[escoyne]

Where do I find Name AutoCorrect?

 

[boblarson]

Where do I find Name AutoCorrect?

Access (pre-2007) -

Tools > Options > GENERAL > TRACK Name AutoCorrect Info (uncheck that box)

Access 2007 -

Round Office Button > Access Options > Current Database > Track Name AutoCorrect Info

The Access 2007 is a guess based on past experience. I don't have it here to verify. I believe it is where I said, but it could be somewhere else under Access Options.

 

[escoyne]

Well nothings working. Guess I need some more help!

 

[Scooterbug]

Can you post your database? Strip out the data, compact and repair, (zip if necessary).

 

[escoyne]

Can i email it?

 

[Scooterbug]

I've sent you a PM with my email. I'll take a look at it when I get it.

 

[Scooterbug]

Escoyne,
I took a quick look and yea...you got quite a few controls on your form. There are a couple of things that you can do.

1. Use additional forms. I know that it might look better with everything on one form and divided up by tabs, but you can obtain the same thing with forms and subforms.

2. Combine labels. Instead of using 5 labels for 5 check boxes, you can use one label. By using Ctrl + Enter, it will drop things down one line.

I am assuming that you have put a lot of work into this database....but after looking at it, I think you should really rethink your table structures. the table lpkCase Info has 179 fields in it. I know it may seem easier to find data if everything is in the same table, but it actually makes filtering things harder. Also, you can run into issues where you wont have a field for things you may need. For example, I noticed that you have fields for 4 suspects per record. What happens if there are five suspects? Correct normalization would dictate that you have a tblSuspect. You can then refer to each suspect by an ID number, and by using a separate table you can record each CaseID along with a SuspectID. You can then run a query to find out which suspects are involved in which Case.

Just some food for thought.

 

[escoyne]

Thanks Scooterbug. I'm completely new to building a database and really jumped in it. I'll look at changing a few things around.

 

[Scooterbug]

I get plenty of free time here at work...so if you dont mind, i'll go through and make some changes to the table structures. I'll document all of it so that you can see what is going on.

 

[escoyne]

Wow...Thanks

 

[Scooterbug]

I've gone through and redid the main table. All the new tables I created start with tbl. I'll let ya digest it...getting ready to go home, so can answer any questions tomorrow if you have any

 

[escoyne]

Tables look good. I guess there was too much in that one table. Now I just got to put it all back together..lol

 

[Scooterbug]

With the new table structures, it shouldn't be too difficult. One other thing that I think will make things easier for you is the Risk Assessments. I noticed that when calculating it, the actual risks aren't saved anywhere. I would create a few more tables:

tblRiskCatagory
RiskCatagoryID Primary Key
RiskType (ie Criminal History, Weapons Assessment, etc)
AssociatedObject (ie Suspect, Location)

tblRiskFactors (Used to store Risk factors)
RiskAssessmentID Primary Key
RiskCatagoryID Foreign Key
Risk Factor The actual factor being checked
RiskFactorValue Numeric value which can be used to calculate Risk Level

tblRiskFactorJunction
RiskFactorJunctionID Primary Key
RiskCatagoryID Foreign Key
AssociatedObjectID Foreign Key (Store the recordID number of the associated Object: ie SuspectID, LocationID)

This way, you can save the risk factors for each object. As you have it now, once the form is closed, the risk factors aren't saved. You would have to recheck them everytime.

 

[escoyne]

Yeah, the risks were what I was working on when the error message came up.