Problem dynamically removing controls

[stevenblanc]

Hi folks,

So now we've having tons of fun. The folks here need to be able to add, edit, and remove records which relate to pedagogical items on a whim. There is no scenario where the number of these items will ever exceed 75.

These items are then used to create table columns on a separate table. This also means that the user entry form must be updated to reflect these fields.

Right now I'm able to pull the names and build the fields dynamically; however, I'm having a problem removing them:

        ' Remove old controls
        For Each ctl In Forms!form1.Controls
            If left(ctl.Name, 3) = "dyn" Then ' All dynamically created controls will be identified by "dyn"
                DeleteControl strForm, ctl.Name
            End If
        Next ctl

Most of the controls disappear but I have two columns of textboxes that just wont go, even though their names match the criteria. This creates a problem when creating the new controls.

The full code is as follows:

Dim i As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strForm As String
Dim frm As ACCESS.Form
Dim ctl, ctlLabel, ctlGuidePed, ctlNumPed, ctlTotalHrs, ctlNotePed As Control
Dim intDataX, intDataY, intDataHeight As Integer
Dim intLabelX, intLabelY As Integer

    strForm = "form1"

    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblGuidelines")
    
    ' Ensure loop starts at 0.
    i = 0
    
    ' Set positioning values for new controls.
    intLabelX = 100
    intDataX = 2500
    intDataY = 250
    intDataHeight = 315
    
    If booFormUpdated = False Then
        
        DoCmd.OpenForm strForm, acLayout
    
        ' Remove old controls
        For Each ctl In Forms!form1.Controls
            If left(ctl.Name, 3) = "dyn" Then ' All dynamically created controls will be identified by "dyn"
                DeleteControl strForm, ctl.Name
            End If
        Next ctl
        
        'MsgBox "done" ' This msgbox is used to check the offending controls 
        
        DoCmd.OpenForm strForm, acDesign
        
        ' Ensure loop starts at 1 so that i matches [pedGuide].
        i = 1
        
        ' Create and format new controls
        Do While i <= rs.RecordCount

            ' Create textboxes for data in detail section.
            Set ctlGuidePed = CreateControl(strForm, acTextBox, , "", "", intDataX, intDataY, 1700, intDataHeight)
            Set ctlNumPed = CreateControl(strForm, acTextBox, , "", "", ctlGuidePed.Width + ctlGuidePed.left, intDataY, 1700, intDataHeight)
            Set ctlTotalHrs = CreateControl(strForm, acTextBox, , "", "", ctlNumPed.Width + ctlNumPed.left, intDataY, 1700, intDataHeight)
            Set ctlNotePed = CreateControl(strForm, acTextBox, , "", "", ctlTotalHrs.Width + ctlTotalHrs.left, intDataY, , intDataHeight)
            
            ' Create child label control for text box and lookup Pedagogy Title.
            Set ctlLabel = CreateControl(strForm, acLabel, , ctlGuidePed.Name, DLookup("[pedTitle]", "[tblguidelines]", "[pedGuide] = " & i), intLabelX, intDataY)
            booFormUpdated = True
            
            ' Set names for each created control based on i
            ctlGuidePed.Name = "dyntxtGuide" & i
            ctlNumPed.Name = "dyntxtPed" & i
            ctlTotalHrs.Name = "dyntxtPed" & i & "Total"
            ctlNotePed.Name = "dyntxtNotePed" & i
            ctlLabel.Name = "dyntxtGuide" & i & "Title"
            
            ' Lookup pedagogy time guidelines
            ctlGuidePed.DefaultValue = DLookup("[timeMins]", "[tblGuidelines]", "[pedGuide] = " & i)
            ctlGuidePed.TextAlign = 2
            
            
            ' Adjust ctl formatting
            ctlGuidePed.BorderStyle = 0
            ctlTotalHrs.Enabled = False
            
    

            
            ' Set the next row to start right below the current
            intDataY = intDataY + ctlGuidePed.height
            
            i = i + 1
        Loop
        
        DoCmd.Close acForm, "form1", acSaveYes
        DoCmd.OpenForm "form1", acNormal
    End If

The controls offending controls are all dyntxtGuide(i) and dytxtNotePed(i).

Any ideas?

 

[boblarson]

A couple of items to note:

1. Forms have a limit of 755 controls over the lifetime of the form.

2. You will never be able to use a compiled version (MDE, ACCDE).

Can you upload a screenshot or two to show us why you would need to do this process? It is confusing me.

 

[stevenblanc]

Bob,

Users should be able to add and rearrange the pedagogical items in the event that definitions change or if down the line there is an idealogical change and new items need to be added. This could be manually done if I were to still be in my current position, but it is likely that I will not be around to tamper with forms so I need everything to be handled by vba.

The current sample data entry form:

As you can see the items under marking and labs need to be updated if the administrators decide to add or remove items. I stress that this should happen rarely. However, changes made to the guidelines:

should be reflected on the entry/review form.

If there's a much simpler way to do this let me know. I considered having a series of invisible placeholders and showing/hiding, but that's about the sloppiest way I can think to go about it.

EDIT: Obviously the code I have above is on a test form and does not currently support the level of formatting necessary to sit on the user entry form. Also I intend that the categories, i.e. Marking, Labs etc, each reside on a page of a tab control.

Cheers,

Steven

 

[boblarson]

I can't see your pics. Why did you not just upload them to this site? Photobucket is a site blocked by my workplace so I can't see them.

 

[stevenblanc]

There you go. Attached to the previous post.

 

[stevenblanc]

Bob, when you say "lifetime" of the form, you mean the current instance? As in if I were to add remove/add controls once each time the form is loaded there should be no problems.

What am I losing by not being able to compile the db?

 

[boblarson]

Bob, when you say "lifetime" of the form, you mean the current instance? As in if I were to add remove/add controls once each time the form is loaded there should be no problems.

You are opening the form and doing the deletions and then saving. So, each time you do that you are CONTINUOUSLY upping the number of controls it has used. So, it isn't each time the form is loaded, but I do mean over the COMPLETE lifetime of the form.

What am I losing by not being able to compile the db?

You can't use the MDE or ACCDE to protect your code, form design, or report design. If someone can get to them, they can change them. If you compile to an MDE or ACCDE then they cannot modify the forms, reports, or code in any way at all, nor can they even see the code if they were to get that far.

 

[stevenblanc]

So its better to create a maximum number of items, say 10-15 rows of textboxes for each category, hide them all, and use lookups and loops to cycle through them, assign them names, bindings, and visibility?

 

[spikepl]

So what exactly warrants all this control manipulation?

Your screen shots do not show anything that cannot be handled by the normal parent/child form relation. Course data and course detail. Each detail item apparently consists of AssignmentType specification (i.e. 5 columns) and a category - all this can be handled by the built-in mechanisms without any exotic facilities. But yes, the layout may perhaps differ from what you have shown.

For manipulation, I would do a form with 2 subforms. The record of the form corresponds to a course. The first subform lists, for each category, the category name and the sum of hours (and perhaps other relevant info - number of items?).
The second subform lists the items of the category selected in the first subform. All this you can do in 2 minutes without messing around with the controls.

For review/reporting, you can easily make a report more or less akin to your current screenshot, again without resorting to any kinky stuff

 

[stevenblanc]

I'm not entirely sure that would work for my purposes. If there are 4 pedagogical items and the user creates a fifth, I still need a new row to be automatically created.

Also, as each course can have multiple sections within one semester, and the same section across multiple semesters the course would not be a suitable recordsource.

The current recordsource is tblcourserecords which contains an auto number field for the primary key. This table will then require three columns to be added for each additional pedagogical item added to tblguidelines. One column for number of items, one column for the total time (which is necessary in case guidelines change in the future), and one column for notes.

Simply I require three columns in tblCourseRecords for each row in tblGuidelines. If this is easily done and the controls for the input are automatically created then I'm game. I've never had to developed such a relationship like that before.

 

[boblarson]

This table will then require three columns to be added for each additional pedagogical item added to tblguidelines. One column for number of items, one column for the total time (which is necessary in case guidelines change in the future), and one column for notes.

Your design is not properly normalized. You should almost never require new columns. You should add items using ROWS in tables. So, if you feel you have to add columns, then I would say that you probably need to have us look at the table structure to see where we can fix that.

 

[stevenblanc]

I was just thinking that. Whats the best way to go about that? Post the database?

 

[boblarson]

I was just thinking that. Whats the best way to go about that? Post the database?

Probably. And make sure to

1. Run Compact and Repair first.
2. No real data, only fake data.
3. Right-click on the file and select SEND TO > COMPRESSED FOLDER
4. Upload the zip file.

 

[stevenblanc]

Bob,

Here it is. Forgive my interface, it's still crude and needs a lot of refinement, a lot of whats there is placeholder. Also, the black bar has the program logo in it so its not as terrible as it seems.

I can see creating another table linked to the tblcourserecord primary key for each item. The table relationship is fairly simple, but I still see the same problem for form construction. Unless you have another "Add Item" button for each pedagogical item. I suppose that would work but its not the most elegant solution and increase the number of clicks required to get things entered.


Steven

EDIT: In case its not clear, the interface starts with frmMain. Let me know when you have it so I can pull it back down.

 

[boblarson]

I'll have to take a look when I get home. 2007 doesn't like it since some 2010 stuff has been used, so I have to wait until I get home where I have 2010 installed. Anyone else is free to chime in if you have 2010.

 

[stevenblanc]

Thanks a lot Bob.

 

[boblarson]

Okay, so your structure is generally sound EXCEPT for the tblCourseRecords table. You've managed to use spreadsheet thinking in that one instead of relational database concepts. You should not have numPed1, numPed2, etc. and that goes for any repeating fields.

You should have a table to add Pedagogical items as a lookup table and then you use a JUNCTION table to add/associate those items. So, for example you would have the lookup table (and I don't mean using lookups at table level but a table where the lookup values reside for use on the form):

tblPedagogicalItems
PedagogicalItemID - Autonumber (PK)
PedagogicalItemDesc - Text


tblCourseRecordPedItems
CourseRecordPedID - Autonumber (PK)
CourseRecordID - Long Integer (FK)
PedagogicalItemID - Long Integer (FK)
NumberOfPedagogicalItems - Long Integer
PedagogicalItemTime - Not sure of best datatype for your purpose


And then you can have a notes table which will let you have zero, one, or more notes associated with any of those items

tblPedagogicalNotes
PedagogicalNoteID - Autonumber (PK)
PedagogicalItemID - Long Integer (FK)
PedagogicalNote - Memo


then in the tblCourseRecords you would only have these items:

RecordID
SemesterID
CourseID
SectionID
FacultyID
dateEntry
dateModify
numEnrollment

(and I would suggest using the NUMBER ID for FacultyID, Departments, and all like that instead of the text which you have currently set in place. It is more efficient using numbers and it will not require fixing lots of records if the spelling gets off for an item. It would just need to be corrected in the lookup table.)

I hope that helps.

 

[stevenblanc]

Ok so I like these recommendations in terms of table structure. However, this would mean having to create an add pedagogical item button to the form, rather than have all available pedagogical items displayed. It's a design I don't think is the most user/time friendly but doable.

The next issue this would create is reporting. The staff here love excel, its been an uphill battle to get them to accept the need for a database to manage 9 departments and upwards of 200 courses each semester. That being said, they still want to be able to manipulate the data in excel. I'm going to make the changes and see if I can figure out how to produce an export model that quickly gives allows them to create a pivot table to get the data they want.

Exporting now (using the excel button on frmSubRecordReview - accessible through frmMain) creates a spreadsheet which allows them to quickly create a pivot table which allow them to pull all the averages and summarizations by department, faculty member, faculty type etc. Which I could do with reports but it seems like unnecessary duplication of effort.

I'll see what can be done, but if you have any suggestions I'd be glad to hear them.

 

[boblarson]

Believe me, once you get this concept down, you can use code to create whatever they want to see but it will actually simplify reporting and pulling things together.

 

[stevenblanc]

I expect so. I'm just playing with the SQL now to see what a joined record would look like.