Delete All controls from A Form

[stinger]

Form Name is MyForm
i want to delete all text box from form and make new, if there are not any controllers code work fine and make new controllers(text box) but when i want to run it second time its not work, this code delete 1 text box and give error controller name already in use

Sub RemoveOldControlsAndAddNew()

Dim newControl As Control
Dim valuesArray() As Variant
Dim totalWidth As Single
Dim controlWidth As Single
Dim currentLeft As Single
Dim i As Integer
Dim frm As Form
Dim ctl As Control

valuesArray = Array("Value1", "Value2", "Value3", "Value3")
totalWidth = 5 * 1440
controlWidth = totalWidth / (UBound(valuesArray) + 1)



DoCmd.OpenForm "MyForm", acDesign

Set frm = Forms("MyForm")


For Each ctl In frm.Controls
DeleteControl frm.Name, ctl.Name


Next ctl


DoCmd.Close acForm, frm.Name, acSaveYes
DoCmd.OpenForm "MyForm", acDesign
Set frm = Forms("MyForm")


currentLeft = 0


For i = LBound(valuesArray) To UBound(valuesArray)

Set newControl = CreateControl(frm.Name, acTextBox, acDetail, "", "", currentLeft, 1000, controlWidth, 500)


newControl.Name = "TempControl" & (i + 1)


currentLeft = currentLeft + controlWidth
Next i


DoCmd.Close acForm, frm.Name, acSaveYes
DoCmd.OpenForm "MyForm", acNormal


End Sub

 

[MajP]

If you are trying to add and remove controls at runtime, DO NOT DO THIS!
Start with hidden controls and then show as many as you need and format them to the correct size and location.

 

[Gasman]

Ditto.
And please use code tags whe posting code more than a few lines.

 

[stinger]

Ditto.
And please use code tags whe posting code more than a few lines.

Sub RemoveOldControlsAndAddNew()

Dim newControl As Control
Dim valuesArray() As Variant
Dim totalWidth As Single
Dim controlWidth As Single
Dim currentLeft As Single
Dim i As Integer
Dim frm As Form
Dim ctl As Control

valuesArray = Array("Value1", "Value2", "Value3", "Value3")
totalWidth = 5 * 1440
controlWidth = totalWidth / (UBound(valuesArray) + 1)

DoCmd.OpenForm "MyForm", acDesign
Set frm = Forms("MyForm")


For Each ctl In frm.Controls
    DeleteControl frm.Name, ctl.Name
Next ctl

DoCmd.Close acForm, frm.Name, acSaveYes
DoCmd.OpenForm "MyForm", acDesign
Set frm = Forms("MyForm")

currentLeft = 0

For i = LBound(valuesArray) To UBound(valuesArray)
    Set newControl = CreateControl(frm.Name, acTextBox, acDetail, "", "", currentLeft, 1000, controlWidth, 500)


    newControl.Name = "TempControl" & (i + 1)
    currentLeft = currentLeft + controlWidth
Next i

DoCmd.Close acForm, frm.Name, acSaveYes
DoCmd.OpenForm "MyForm", acNormal

End Sub

 

[GPGeorge]

Please explain the problem that this code is intended to solve.

As the previous two responders have indicated, deleting and adding controls at runtime is a sledge hammer approach. Unless the problem being addressed is the size of a boulder, it might be wise to look for a more appropriate tool to solve the problem.

 

[MajP]

See technique with hidden controls.

 

[Gasman]

See technique with hidden controls.

Very nice, but you have your columns and rows mixed up.

 

[MajP]

Very nice, but you have your columns and rows mixed up.

Updated with new labels.

 

[stinger]

Please explain the problem that this code is intended to solve.

As the previous two responders have indicated, deleting and adding controls at runtime is a sledge hammer approach. Unless the problem being addressed is the size of a boulder, it might be wise to look for a more appropriate tool to solve the problem.

I simply want to delete all controls like text box from a form named MyForm through code

 

[The_Doc_Man]

Just to explain why you are being told to not dynamically add and remove controls, there is a limit on how many controls you can create on a form and it has nothing to do with how many controls you have deleted. Internally, Access remembers how many controls you have created. There is a limit of just over 750 controls after which you would be unable to create any more new controls, even if the form is currently blank.

I understand that you might wish for controls to be invisible and disabled at times in order to simplify what the user sees. But look to enable/disable controls. Or make them not visible. Don't create controls dynamically.

 

[MajP]

We try not to encourage dumb behavior. When I see someone running with scissors I usually try to encourage them not to run with scissors instead of showing them a faster way to run.

The first time it runs it deletes "value1" to "value3"
and adds TempControl1 to TempControl3
Next time it runs it tries to delete "Value1" to "Value3" which do not exist
It tries to add "TempControl1" but that already exists since it was never deleted and thus an error

Fixing it does not make it a less dumb idea.

 

[GPGeorge]

I simply want to delete all controls like text box from a form named MyForm through code

I get that.

But WHY do you think that's a good idea? What problem will be solved by deleting controls?

 

[GPGeorge]

Just to explain why you are being told to not dynamically add and remove controls, there is a limit on how many controls you can create on a form and it has nothing to do with how many controls you have deleted. Internally, Access remembers how many controls you have created. There is a limit of just over 750 controls after which you would be unable to create any more new controls, even if the form is currently blank.

I understand that you might wish for controls to be invisible and disabled at times in order to simplify what the user sees. But look to enable/disable controls. Or make them not visible. Don't create controls dynamically.

I believe Colin has demonstrated that more recent versions of Access do support more than 754 lifetime controls. Nonetheless it is still a finite number.

 

[isladogs]

Agree with all previous comments

When I last tested this, the lifetime limit was about 1038 in 32-bit A365 and 1167 in 64-bit A365

Access Specification issues

Microsoft publishes a list of Access specifications and limits. Most of the information is correct. This article discusses some specification items that are misleading, out-of-date or inaccurate

www.isladogs.co.uk

So as already stated, even though you'll hit the limit a bit later, at some point you will do so.

 

[MajP]

The limit on controls is just part of the problem.
1. Does not support accde or runtime
2. Unnecessary complexity. I can show and hide hundred of controls without problem. Easy code. No need to open in design view modify and close.
3. Good chance of causing corruption.

So there is an easy simple and safe way
or a more complex, limited, inefficient way

I do not know, but I think I will go with the first option.

 

[stinger]

trying to manage results of student, i need specific format for result card, I am attaching my db and images, a form image where we should add exams in list box like assessment-1 and so on, also attaching the result card, there should be dynamic number of exams and subjects, just add first 4 assessmnets and 1 semester then click on button , its work fine but what if we add 3 assments and 1 semester, I have manage the query but how to manage the design

 

[olxx]

I don't know what that database suppose to do, but it seems to me that you have design and normalization issue, not the form issue.

 

[Pat Hartman]

trying to manage results of student, i need specific format for result card,

Access is not a web page. It works very differently. As the others have all said - Your current tactic IS A SERIOUSLY POOR solution. The solution to the problem is to use a subform. the Subform is bound to a table or query that displays the Classes and assessments.

Also, your form looks like a spreadsheet rather than a relational database form which means that you are conflating how data is stored with how it is displayed. Please spend more time with the database normalization topic before continuing with the assignment. You can create a report that looks like what you are trying to use for a form by using a crosstab query that pivots the student classes/assessments tables but the crosstab solution will not be updateable so it doesn't work as a data entry form. It only works as a report. I've attached a link to a database that will show you how to create a "denormalized" pivot using queries rather than code. This is an advanced topic. Even though it only requires a couple of lines of code, it does require an understanding of database design.

As far as your schema goes. It needs much work before you can even begin to create forms/reports.
You need
tblStudents
StudentID (autonumber PK)
RollNo (unique ID from the schools other applications)

tblSubjects
SubjectID (autonumber PK)
SubjectName

tblExams
ExamID (autonumber PK)
ExamNum

tblSemesters
SemesterID (autonumber PK)
SchoolYear (uniqueID field 1)
SemesterNum (uniqueID field 2)

tblStudentSemesters
StudentSemesterID (autnumber PK)
StudentID (FK to tblStudents)
SemesterID (FK to tblSemesters)

tblSemesterSubjects
SemesterSubjectsID (autonumber PK)
StudentSemesterID (FK to tblStudentSemesters)
SubjectID (FK to tblSubjects)

tblSubjectExams
SubjectExams (autonumber PK)
SemesterSubjectsID (FK to tblSemesterSubjects)
ExamID (FK to tblExams)
ScheduledDT
TakenDT
Grade

You are trying to make the 2nd-4th tables do double duty by embedding the 5th -7th table's data. The last three tables are commonly referred to as junction tables. In a relational database these tables are used to connect tables that are related many-many. A student studies for multiple semesters. each semester the student takes several subjects. Each subject has multiple exams.

PS - do NOT use "ID" as the name of the PK for every table. All it does is confuse the issue and make it impossible for observers looking at the schema to "see" the relationships. StudentID should be the PK for the student table and StudentID should be the name of the FK in the Student Semesters table

PPS - NEVER include an autonumber in a table UNLESS it is being used as the PK. That is its only use. It has no other reason for being.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

 

[Mark_]

As this is for a school, there are an extra couple table that would need to be included in addition to those that Pat has identified.
For each subject, you will need to have a child table for "Course". This is the specific semester long course of instruction that is being provided. For Maths, that may be "Maths-1", "Maths-2", or something similar.
A child off of the "Course" is then "Section". This is a junction table that links a specific course to a specific semester at minimum. Normally this also includes the portion of the day (period) that the instruction is given and also links in the Instructor who is teaching the students.
This is then one of the two parents to tblSemesterSubjects along with the tblStudentSemester to show which specific segment of instruction a student is in.

This then lets you not only provide the given reports that you are looking for, but also answers the questions the school administration starts asking when some students perform poorly and others do very well; "Who is their teacher?". It also makes it far easier to report on what portions of a curriculum are working well and which are not.

NOTE: This becomes more complicated if each section has more than one instructor.

I've a feeling your school will want similar reporting on student progress as the college I work for.

 

[sonic8]

The first time it runs it deletes "value1" to "value3"

It doesn't.

For Each ctl In frm.Controls
DeleteControl frm.Name, ctl.Name


Next ctl

Inside your loop based on the Controls collection you remove items from the collection. - This is a bad idea because it will produce unpredictable and/or undesired results.

If you need to remove items from a collection use an index based loop counting from the last to first element:

Dim i as long
For i = Controls.Count to 0
   DeleteControl frmName, Controls(i).Name
Next i

Beyond the immediate problem, I agree with others that this approach is not ideal and should be replaced.