Which data type for createobject?

[BRV]

Sorry, I mis-named this thread. It's actually for the CreateControl function, not CreateObject



I'm running into a type mismatch problem here:

'now use the coverage pricing table to fill the form with the proper options for coverages
DoCmd.OpenForm "frmCoverage", acNormal, , , , acHidden 'open the form in the background
Dim db As DAO.Database 'identify the database
Dim rs As DAO.Recordset 'indicate use of a recordset
Set db = CurrentDb 'assign the current database to db
Set rs = db.OpenRecordset("SELECT PriceLvl, Low, Mid, Description FROM tblPriceCoverage") 'get the price coverage options from the table
Dim NewOptionButton As Object 'THIS IS THE VARIABLE THAT I CANNOT SEEM TO FIGURE OUT
Dim OptionsCaption As Control
Dim x As Integer
Dim intLevel As Integer
Dim intLow As Integer
Dim intMid As Integer
Dim strDescription As String
Dim myForm As Form
Set myForm = Forms![frmCoverage]
Application.VBE.MainWindow.Visible = False
Do While Not rs.EOF
intLevel = rs![PriceLvl]
intLow = rs![Low]
intMid = rs![Mid]
strDescription = rs![Description]
'create the different option buttons based on the user defined options from the tblPriceCoverage
Set NewOptionButton = CreateControl(myForm, acOptionButton, acDetail, myForm![Frame]) 'THIS IS WHERE IM GETTING THE COMPILE ERROR- TYPE MISMATCH
With NewOptionButton
.Name = "Option" & intLevel
.Top = 20 + (14 * intLevel)
.Height = 14
End With
Set OptionsCaption = CreateControl(myForm, acLabel, acDetail, myForm![Frame]) 'I HAVE NOT TESTED THIS YET AS IT WONT COMPILE PAST THE OPTION BUTTON ERROR
With OptionsCaption
.Name = "Label" & intLevel
.Caption = "Level " & intLevel & ":" & Format(intLow, "currency") & " and " & Format(intMid, "currency")
.Top = 20 + (14 * intLevel)
.Left = NewOptionButton.Left + 2
.Width = 90
.Height = 14
.Font.Size = 12
.Parent = NewOptionButton
End With
rs.MoveNext
Loop
DoCmd.OpenForm "frmCoverage", acNormal, , , , acDialog 'now reopen the form as dialog
End Sub

The problem is that I've tried many data types for the NewOptionButton variable, but always receive a "Type Mismatch" Error.
I've tried:
Object
AccessObject
Control
OptionButton
CustomControl
Variant
and several others...

No Luck with any of these.
Does anybody know what the proper data type would be?

[taxidev]

CreateControl(myForm -- here is the error
myForm is an object, the function is expecting a string

[orange]

Brv,

Do you really want to create these controls on the fly?
You could, in design view, create a form with all the controls you need. Then, in your program logic, depending on what the user has selected/opted for, make some controls visible, some hidden etc. Perhaps, your requirements are such that on the fly controls is the only scheme possible, but I thought I'd at least suggest an (untested) option.

Also, Mid is a poor choice for a field name --Mid() is a function in Access.

[BRV]

CreateControl(myForm -- here is the error
myForm is an object, the function is expecting a string

Thank you! This fixed the problem (with a few other changes).

Brv,

Do you really want to create these controls on the fly?
You could, in design view, create a form with all the controls you need. Then, in your program logic, depending on what the user has selected/opted for, make some controls visible, some hidden etc. Perhaps, your requirements are such that on the fly controls is the only scheme possible, but I thought I'd at least suggest an (untested) option.

Also, Mid is a poor choice for a field name --Mid() is a function in Access.

I've contemplated this. I still am.

Thanks for the tip on mid. I have a hard time keeping track of all of the words that access uses (there are a lot of them).

[orange]

Reserved words
http://allenbrowne.com/AppIssueBadWord.html

[BRV]

Reserved words
http://allenbrowne.com/AppIssueBadWord.html

Thanks for that. Turns out there's only 2,641 reserved words!

[orange]

If you develop or adopt a naming convention, you can avoid most of the reserved word issues.
Little things like prefixes t_ or tbl... for Tablename
s... for string variable
i... for integer variable
dte for Date variable

You can find many of these via google