Hiding Columns and Creating Combo Boxes From Within VBA Code

[mathare]

I am trying to create a number of tables from within VBA code. I want the tables to have 10 columns, 6 of which are hidden. I also want 2 of the unhidden columns to be combo boxes, populated from queries that I have already set up.

I know how to create a table and append fields to it. What I cannot do is set the required columns to be hidden or get 2 of the fields to be combo boxes.

I have tried using the ColumnHidden property mentioned in the help (Access '97) but to no avail. I used:

myField.Properties(ColumnHidden) = True

but this failed.

As for the combo boxes, I don't really know where to start. I have been adding my fields to the table like:

with myTableDef
    .Fields.Append .CreateField("My field", dbText, 50)
end with

Any help with any of this, probably the first of many problems, is much appreciated

 

[Frink]

Hallo,

I'm not sure what you mean by 'hidden' table columns.
In access tables have fields (not columns) but you can't have them hidden. You can hide them (make their width in datasheet view 0) but that wouldn't stop anyone changing the width.
To set up a table to use a combo box has to be done manually (I think) You can't use VBA.

On a more general note, users should interact with tables using forms, not the tables themselves. This way you can have much more control over what they can and can't see/do.

To hide a field on a FORM in datasheet view, create a new form based on the table. Use the wizard and select datasheet. Then create an On Open event procedure and in it put lines like:
Me![ID].ColumnHidden = True
where ID is the name of the field you want to hide. Not sure if this is the control Name or ControlSource. The wizard should make them the same.

To make them Combo boxes, I think you have to use a form in Form View. Create a Tabular form using the wizard. Select the control you want to be a combo box and use Format>Change To>Combo Box. Now update the rowsource etc.

I'm guessing that the structure of your table is variable, that's why you want to create it on the fly. If it's constant then why not create the table how you want it, then just paste records into it (using an update query?)

Hope that helps,

- Frink

 

[mathare]

Cheers. I'm pretty new to designing databases and writing in VB, I guess my inexperience is showing.

The overall aim was to have tables that the user can create, check the contents of and amend as required. But I only wanted them to have 4 of the fields showing, so they don't get scared by seeing too much data for each record. I also wanted 2 of the available fields to be combo boxes so the user was restricted in the choices he could make.

I am likely to have about 100 (maybe more) of these tables. They will all have exactly the same structure. I guess a form is the way to go. I hadn't thought of doing it that way before. But I assume I can set the source for the form in VBA. I was creating the tables on the fly because I want a lot of tables, the names for which are coming out of a query running on another table.

Thanks for the help. I will probably be back for more help later but for now I will go look at using forms to solve my problem.

Cheers