Creating Checkboxes on subform via VBA Module programming

[Mark Rock]

I should not be confused with someone that knows what they are doing and I only pretend to know what I want.

I am attempting to create a form entirely with VBA in Microsoft Access. On this form I am creating a listbox and a subform with a checkbox (actually several thus the Dim with an array of Controls below).

Listbox works. Checkboxes work without a subform. However, with the subform related code shown, the subform is created, but I never see the checkboxes appear within the subform (or anywhere else for that matter). I get no error messages.

Suggestions?

Relevant code below:

Dim myfrm As New Form
Dim mysubfrm As Control
Dim myctlbox As Control
Dim mychkbox(500) As Control
Dim num as Integer

num = 1

Set myfrm = CreateForm
Set myctlbox = CreateControl(myfrm.Name, acListBox, acDetail, "", "", 60, 60, 5100, 600)
Set mysubfrm = CreateControl(myfrm.Name, acSubform, acDetail, "", "", 5350, 60, 6000, 6000)
Set mychkbox(num) = CreateControl(mysubfrm.Name, acCheckBox, acDetail, "", "", 5500, num * 275 + 45)
Set mychkbox(num).Name = "Checkbox_" & num​

I searched for a similar posting (here and elsewhere) and have not found anything. I would appreciate the help if someone sees what I am doing wrong.

 

[boblarson]

Probably because you aren't referencing the newly created subform properly. The syntax for referring to a subform (top level) is:

Forms!YourMainFormNameHere.Form.YourSubFormContainerNameHere

To refer to a control on the subform:

Forms!YourMainFormNameHere.YourSubformContainerNameHere.Form.YourControlNameHere

IMPORTANT NOTE: To refer to a subform on a main form you are referencing the name of the CONTAINER that houses the subform and not the subform name itself (unless they are named the same). I don't know if, when your code creates the subform on the main form, the container is named the same. It could be named something like "ActiveXCtl3" or something, which would make it hard to determine the name.

I hope that helps you on your quest.

 

[Mark Rock]

Thank you for the help Bob!

I am attempting to understand what you are telling me. The result is that I tried modifying the code as follows,

Dim myfrm As New Form
Dim mysubfrm As Control
Dim myctlbox As Control
Dim mychkbox(500) As Control
Dim num As Integer

num = 1

Set myfrm = CreateForm
Set myctlbox = CreateControl(myfrm.Name, acListBox, acDetail, "", "", 60, 60, 5100, 600)
Set mysubfrm = CreateControl(myfrm.Name, acSubform, acDetail, "", "", 5350, 60, 6000, 6000)
Set mychkbox(num) = CreateControl(Forms![myfrm.Name].Form.[mysubfrm.Name], acCheckBox, acDetail, "", "", 5500, num * 275 + 45)
Set Forms![myfrm.Name].[mysubfrm.Name].Form.mychkbox(num).Name = "Checkbox_" & num

If I captured that correctly, it still did not result in the Checkboxes being displayed. Listbox and Subform outline show as expected, but the subform is blank.

Any further suggestions?

 

[boblarson]

I think it has to do with trying to use Object.Name within the reference to the form. I haven't done any of this dynamic building, so I'm not really sure how to refer to the object like that.

 

[Mark Rock]

I thought that too after debating on whether to use the square brackets ([])or not. I ended up trying it both as shown above and by replacing the Object.Name nomenclature with a string variable -- Didn't help. I also put in a msgbox() function and found out the actual names being assigned (Form1 and Child1). I tried using those in place of the Object.Name/string variable approaches. Didn't help.

Anyone else Help?

 

[Bat17]

never tried this but I suspect you have to work back-to-front. First create your subform, then create your main form and add the prepared subform to the main form.

HTH

Peter

 

[Mark Rock]

Thanks for all the help guys.

I admit though that nothing I tried that day works so I eventually gave up on trying to use a subform and just split the controls across the header section and the details section as I mostly just wanted to be able to vertically scroll the checkboxes without also scrolling the listbox.

That seems to have worked for my purposes. I'll save the subform effort for another fight another day!