Dynamic Creation of Combo boxes

[vbaInet]

You would set a tag for all your dynamic controls (i.e. combo and label), something like "temp_control" and check against that when deleting. Something like:

    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If ctrl.Tag = "temp_control" Then
            DeleteControl Me.Name, ctrl.Name
        End If
    Next

As you figured out, creating controls requires doing it from another form and in design view, so that same rule may also apply.

Found this too. http://www.freevbcode.com/ShowCode.asp?ID=2815

 

[timothyd]

Thanks,

I will try that. It seems like it will work great.

 

[vbaInet]

That should do the trick. As a suggestion as well, for to be able to easily identify and manage your dynamic combo boxes, combine the ID of the Rating with the text "Combo". This would mean looping through each Rating to perform the creation of your control and setting the right properties. So for example if you have three ratings as follows:

RatingID Rating
---------------------------
14 Excellent
15 Average

Your combo boxes would have names:

Combo14
Combo15

For your labels, you could even set the caption to be the actual rating if you've set a maximum string length.

 

[timothyd]

I'm not sure why but when I go through to delete the controls, it for some reason skips some of the combo boxes and never compares the tag. It checks the label and deletes it but not the combo box.

 

[SOS]

Just like any collection, when you delete something the indexes change and so you are skipping them if you are moving forward. You need to move BACKWARDS through the collection and do the deletes that way.

Think about it like this (sort of like an Excel file)

If you have rows 1, 2, 3 and you are on 1 and delete 1 then 2 becomes 1 and 3 becomes 2 and if you move to the next one you will have bypassed one of them.

 

[vbaInet]

Are certainly sure you're setting EACH combo box's tag property? Test that at the end of creating all the controls.

If everything's nicely setup, try setting the recordsource of each combo box control to zero length string before deleting.

SOS: he's actually using the tag property to identify the control.

 

[SOS]

And here's the code to do it.

Dim z As Integer

    For z = Me.Controls.Count - 1 To 0 Step -1
        If Me.Controls(z).Tag = "temp_control" Then
            DeleteControl Me.Name, Me.Controls(z).Name
        End If
    Next
End Sub

 

[SOS]

SOS: he's actually using the tag property to identify the control.

Yes, but again I will state it one more time. It doesn't MATTER that the tag property is being used as it never gets to test for that when you have more than one control with that tag.

The second you delete one the others move up in the index and the

For Each ctl in Me.Controls

has now just skipped one. Get it?

 

[timothyd]

Thanks for that. It makes sense since it skips every other control, but I'm unsure about how to achieve the backward delete. I won't know how many combo boxes there will be on the form otherwise I would delete by name.

Every combo box does have a tag set. I don't think there is anything in the recordsource but I will try.

 

[SOS]

Thanks for that. It makes sense since it skips every other control, but I'm unsure about how to achieve the backward delete. I won't know how many combo boxes there will be on the form otherwise I would delete by name.

Every combo box does have a tag set. I don't think there is anything in the recordsource but I will try.

I just posted the code for you. See above about two or three posts.

 

[timothyd]

My bad, didn't see that since there were so many posts being made at once.

 

[SOS]

No problem.

So, the skipping problem is a commonly misunderstood consequence of deleting something from a collection and most of the time most don't notice it because you are iterating trying to find one thing and delete it. But if you are trying to delete more than one and they happen to be side-by-side in the collection - well it bypasses one and goes on to the next.

It isn't a problem if you aren't deleting. Just when you are deleting and possibly deleting more than one object.

 

[timothyd]

It works well and has solved my problem. Thanks again.

 

[SOS]

It works well and has solved my problem. Thanks again.

Glad we could help.

Now you do know that you will not be able to have this database as an MDE or ACCDE file, don't you? It will only work as an MDB, ACCDE or ACCDR. Because you are making changes on the fly, people without the full version of Access will not be able to use this form. It will generate runtime errors if they try because the runtime and MDE or ACCDE files are not able to make changes to objects.

 

[timothyd]

That is ok, I guess. I shouldn't have to worry about too many people going in an changing the settings since I have no idea how many people will really use the final product. It's more for my sake that I am doing this. Which would be the best format of those that I could have it as?

 

[SOS]

Well, the formats are

.mdb is the format for Access 97, 2000, 2002, 2003

.ACCDB is the format for Access 2007

Access 2007 can run the previous version formats.

 

[vbaInet]

Yes, but again I will state it one more time. It doesn't MATTER that the tag property is being used as it never gets to test for that when you have more than one control with that tag.

The second you delete one the others move up in the index and the

For Each ctl in Me.Controls

has now just skipped one. Get it?

Ah yes, that's right. Same principle applies with the index of an item in a listbox/combo box. Good thinking!

 

[c_smithwick]

The problem with adding combo boxes (and you can do it by opening the form in hidden mode in design view and then saving it) is that you have to programatically position your combos as well as maintain a current count, in case you have too many and need to delete some. Better way would be to create a maximum number, position them at design time and set their visible property to false and them programatically populate them and unhide them based on need when you open the form. Maybe a better idea would be to create a subform based on a table that contained two fields - Subject (string) and rating (integer). Build the subform with a combobox bound to the rating field with options for 1 to 10 and a text box bound to the Subject, and set it to show in a multiple record view. Prior to opening the form, empty the table with a delete query (in case data was remaining from a previous run), populate it with a do loop drawing from your list of subjects (all this in code of course), setting the rating field value to null. When your students use the combo boxes on the subform to enter their ratings, grab the data from the table with another do loop that runs through the forms recordSource in a form close event and store it in your data table.

 

[vbaInet]

The problem with adding combo boxes (and you can do it by opening the form in hidden mode in design view and then saving it) is that you have to programatically position your combos as well as maintain a current count, in case you have too many and need to delete some.

I think the OP is aware of the difficulties. The OP was advised of a way of handling the removal of combo boxes (by using the tag property and the Controls collection) and from that you wouldn't need a count of how many. Setting a maximum number is a good idea.

Better way would be to create a maximum number, position them at design time and set their visible property to false and them programatically populate them and unhide them based on need when you open the form.

See previous posts. I had already advised the OP about this approach.

Maybe a better idea would be to create a subform based on a table that contained two fields - Subject (string) and rating (integer). Build the subform with a combobox bound to the rating field with options for 1 to 10 and a text box bound to the Subject, and set it to show in a multiple record view. Prior to opening the form, empty the table with a delete query (in case data was remaining from a previous run), populate it with a do loop drawing from your list of subjects (all this in code of course), setting the rating field value to null. When your students use the combo boxes on the subform to enter their ratings, grab the data from the table with another do loop that runs through the forms recordSource in a form close event and store it in your data table.

georgedwilkinson and myself suggested this approach (although in a slightly different context) to the OP. Our suggestion was the use of listboxes. The OP still wanted to go ahead with his original plan.

 

[timothyd]

Another strange question:

Is there a way to set an event for the dynamically created combo boxes. For instance, when one of the combo boxes changes values, I want it call a function that will switch a global variable from false to true. That way, when I change student it will popup a message box saying some values have changed and ask if the user wants to save.

I'm really not sure if this is possible and I may have to figure something else out.

Just an after thought, can I have the form itself call that function when one of its combo boxes are updated?

Thanks for any help.