Me.Controls.Add() method for UserForm in Access

[DonkeyKong]

This code works fine in Excel, but when I put it behind an Access DB it gives me a 'Run-time error '13': Type mismatch' error.

Dim ctrl as control
Dim ctrlName as string
ctrlName = "whatever"
Set ctrl = Me.Controls.Add("Forms.Label.1", ctrlName, True)

I know it doesn't like the "Forms.Label.1" but I don't know what an alternative to this would be. Any help is greatly appreciated.

 

[pbaldy]

It's pretty unusual to add controls at runtime. Typically with a normalized design the fields (columns) are static, so can be accounted for in the initial design of the form. What is the reasoning behind adding controls at runtime?

 

[JHB]

Here is a link to how you create a control in MS-Access from VBA.
http://msdn.microsoft.com/en-us/library/office/aa221167(v=office.11).aspx
But as pbaldy mention it's pretty unusual to add controls at runtime, and you can only add them a certain amount of time in the same form!

 

[DonkeyKong]

Thanks for the replies. Since posting this, I have decided to abandon it and keep the code in a .xla file.

I'll try not to talk too much... What I was trying to do was use a UserForm, not a Form. UserForms are not often used in Access but it can be done. From what I gathered, though, runtime creation of UserForm controls is not supported in Access or if it is, it's probably more work than it is worth to me.

This is supposed to be a small stand-alone utility that consists of only userforms. Basically, user runs script that launches UserForm and hides workbook/database. The UserForm uses values queried from an Access DB. There is NO Excel functionality besides the UserForms so I thought I could just put them behind the Access DB and eliminate some complexity. I have changed my mind though as it seems to be more complex to put UserForms behind Access.