Dynamic Creation of Combo boxes

[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.

 

[vbaInet]

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.

You would be looking to reading lines of code into the VBA editor and setting the BeforeUpdate method to "[Event Procedure]". If you're good with macros then it will be easier.

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

Unfortunately, you can't. Form events apply to the form and not the controls within it.

 

[timothyd]

Do you know how to set the macro in vba? The problem really is that I create and delete these combo boxes every time i open the form up, thus I have to be constantly resetting the event. I just am not sure how to edit the event.

 

[vbaInet]

I don't really use macros because I find them tedious so I can't help you with that unfortunately.

However, you could use an Expression instead. So an If block would be (for example):

=IIF([MyField] = 1, True Argument, False Argument)

Then you set the AfterUpdate method of the combo box to that expression.

Would be rather difficult to create if your original code is long.

 

[timothyd]

I figured it out. I just set the BeforeUpdate value equal to the method I wanted to call. Works like a charm.

ctrlCombo.BeforeUpdate = "=Form_Updated()"

By the way, thanks to all of you for helping me out on this project. I know it seems like it shouldn't work at first, but you can now say that it works and you have the code to do it. Not sure how often you would use this idea, but it may come in handy some day. Again, thanks for past and probably future help!

 

[vbaInet]

You're welcome, glad you got it working.

These sort of things are useful if you had a Report Creating tool where users are given a list of fields which they can choose and a report is created based on their selections.