Trying to add *Combo Box* through VBA instead of text-only 2

[Dor100]

Hi, I've been scouring the web to find the sql to add a combo box, or change a text box to a combo box. So far, I can add a text column in a vba module like so:

CurrentDb.Execute "alter table TableName add EE text"

But...I need the field to have combo box properties. I've also been trying to modify the text field like so:

CurrentDb.Execute "alter table TableName alter column EE ?????"

I'm surprised to not have found this through Google yet. I also looked at MSDN a bit.

If anyone has the answer it would be much appreciated. Also, if anyone has a link to a *complete* list of column types available for Access sql that would also be great.

 

[PHV]

Search your local drives for files named jet*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dor100]

Thanks. Will do, and hopefully find something that works...

 

[Dor100]

Ok, back to the drawing board now. Looks like it's a no go with Jet sql, so now I'm going to check out these "DAO Methods for Microsoft Jet Workspaces" and see if it's possible there.

The tip about the .chm files is excellent, however, so PHV deserves a commendation. Wish I had known about these excellent resources before - it's a bit like buried treasure.

 

[Dor100]

Well, it's looking like I may have reached a limitation with Access XP, unless someone knows a way to programmatically copy a combo box field in a table (to the same table) and then immediately rename it.

I tried, for instance, to set the DisplayControl property for a field object in vba, but it won't work and this is what you get when you F1 the DisplayControl keyword: "You have requested Help for a language element that is hidden, and therefore unavailable for programmatic access."

Here's a Google search on that message which I'm about to follow up on:

http://www.google.com/search?hl=en&...+for+programmatic+access."&btnG=Google+Search

 

[Dor100]

Followed up on the link in that Google search pertaining to the Object Browser, but it's looking like a dead end. So...if anyone out there knows of a way to programmatically add or activate combo box properties for a field (e.g., DisplayControl, RowSourceType, etc.) after the "currentdb.execute 'alter table X add column Y text'" it would be much appreciated.

 

[gol4]

I guess as usual I am a bit confused, what are you trying to do here? Are you trying to add this to a form or tryng to create a lookup field directly to the table.
if trying to do in the table look up the DisplayControl property.

 

[Dor100]

It all has to occur programmatically through vba and sql at the click of a button, otherwise you can be sure this thread would not exist. "DisplayControl" in a code window results in the "hidden" message. So far the best attainable has been a text field without any ability to work with Lookup properties.

 

[gol4]

are you trying to create this or change it

I can manipulate my tables lookups using code similar to the following

CurrentDb.TableDefs("bdate").Fields("names").Properties("DisplayControl") = 110 ' if I set this to 109 then it is a textbox
'.Properties ("RowSourceType")= have not played with this
CurrentDb.TableDefs("bdate").Fields("names").Properties("RowSource") = "Clients"
CurrentDb.TableDefs("bdate").Fields("names").Properties("BoundColumn") = 1
CurrentDb.TableDefs("bdate").Fields("names").Properties("ColumnCount") = 2
CurrentDb.TableDefs("bdate").Fields("names").Properties("ColumnHeads") = True

since I added them manually at first I did not have to do createproperty method but should work the same. I will play with and post code shortly (Hopefully)

 

[Dor100]

You've given me renewed hope, so by all means please post that code when you can, and I'll check into what you've added already. Thanks...

 

[Dor100]

It's looking like a no go unless the field was already created manually. Both of the following leave me high and dry, so a work around would also be good, such as being able to copy a field through code and rename it as desired.

Dim P as Property

Set P = CurrentDb.TableDefs("Table").Fields("EE").CreateProperty("DisplayControl", , 111)

CurrentDb.TableDefs("Table").Fields("EE").Properties("DisplayControl") = 111

 

[gol4]

I just did it with this code. Seems as soon as you click on the lookup tab it appends the displaycontrol property so will want to check for that in any code you write.

Private Sub Command1_Click()
Dim dbs As Database, tdf As TableDef
Dim fld As Field, prp As Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!Contacter
Set fld = tdf!Phone
'fld.Properties("DisplayControl") = 110
Set prp = fld.CreateProperty("DisplayControl", 3, 110)
fld.Properties.Append prp
Set prp = fld.CreateProperty("Rowsource", 12, "Clients")
fld.Properties.Append prp
Set prp = fld.CreateProperty("BoundColumn", 3, 1)
fld.Properties.Append prp
Set prp = fld.CreateProperty("ColumnCount", 3, 2)
fld.Properties.Append prp
Set prp = fld.CreateProperty("ColumnHeads", 1, True)
fld.Properties.Append prp

 

[gol4]

second post asks more specifically for adding adding field

Dim dbs As Database, tdf As TableDef
Dim fld As Field, prp As Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!Contacter
Set fld = tdf.CreateField("newfield", dbText)
tdf.Fields.Append fld
Set prp = fld.CreateProperty("DisplayControl", 3, 110)
fld.Properties.Append prp
Set prp = fld.CreateProperty("Rowsource", 12, "Clients")
fld.Properties.Append prp
Set prp = fld.CreateProperty("BoundColumn", 3, 1)
fld.Properties.Append prp
Set prp = fld.CreateProperty("ColumnCount", 3, 2)
fld.Properties.Append prp
Set prp = fld.CreateProperty("ColumnHeads", 1, True)
fld.Properties.Append prp

Contacter = table adding to
Clients = table looking up values in

this works in access 97

 

[Dor100]

Thanks a lot, gol4. This is truly excellent. With some minor tweaking for combo boxes, it's doing everything I was looking for. I had always thought I could dispense with the "!" in favor of more preferable conventions, such as [Form_FormName] for forms or 'set Variable = "Name",' and was doing well without it until now.

Thank God for forums like this; this is a superb example of what a good and excellent gift they can be.

 

[Dor100]

Well, one can programmatically add the new table field to the relevant subform after the first stage. Need to make sure the subform field label has the right name instead of something like "Combo 5." Will also need to write code to give temporary design permissions and then remove them after the new fields are added to the table and the subform. I expect that should be doable.