hide columns BUT not in a combo box

[craw]

I have a form with several subforms. In one of the subforms, the data is a result from a query (a select query built from two tables). This resulting query has many columns (more than 20 and growing by the day). Some of which don't always contain data for a recordset. On the main form as I select different records, I would like this subform to only display the columns that contain data. I am a newbie (understatement) to writing code but have the awful feeling this is the only solution. I can't use the columnwidth or hiddencolumn property because this is not a combo box nor can it be in my application.

 

[Schof]

Use the ColumnHidden form property.

For example:

Me.MySubform.Form.ColumnName.ColumnHidden = True


Obviously this requires you to know which columns need to be hidden based on what is selected in the main form. Once you know that though you can control your subforms as needed.

 

[craw]

unfortunately, I know just enough to get frustrated. Where do I type this in? The properties of the subform does not have a columnhidden property.

 

[Schof]

Let's say you have a combobox called COMBO that when the user selects a different value it affects which records (and controls) are displayed in the subform. From what you said

On the main form as I select different records

I don't know how you are doing it but the approach should be the same.

So in the controls's Change event you hide the appropriate controls in the subform:

Private Sub COMBO_Change()
' MySubform <-- replace this with the name of your subform
' ColumnName <-- replace this with the name of the column you want hidden
Me.MySubform.Form.ColumnName.ColumnHidden = True
End Sub

Note: You are hiding the column in the form property of the subform, not the subform properties itself. Make you you have the word Form (see underlined text above) following the name of your subform.

 

[craw]

but don't I have to "dim" some variables first? and does it matter if this is not a combo box?

 

[Pat Hartman]

This resulting query has many columns (more than 20 and growing by the day).

Why, she asks with terror in her voice, are new columns being added daily?

but don't I have to "dim" some variables first?

- not when you are referencing controls or fields from the bound recordsource.

and does it matter if this is not a combo box?

- no.

 

[craw]

hmm, this is hard to explain...

i have a book of tool drawings. Each drawing contains a picture and a spreadsheet of dimensions. Each drawing can refer to any number of similar tools having the same geometry. From drawing to drawing, the dimensional callouts (or titles) can be different but refer to the same thing (example, OAL means overall length, but could appears as L on another drawing) But here's where the additional columns come into play - I could have one drawing that calls out a dimension that doesn't appear on any other drawings, hence, a new column called "newdim". As I go through all the drawings getting the data input, my columns for tbl_DimensionData is growing. On my form, I have a subform with tbl_DimensionData (it's really a query).... and because I have ALL those darn columns, I would like to hide the appropriate ones for the particular drawing that called up.....

whew, did that make sense to anybody???

Question #23, will taking a 5 day VB crash course help in the future? I'm trying to get my boss to fork over the 2G's for the class. but I'm not sure if it's worth the $$

 

[Pat Hartman]

Since dimensions have a many-to-one relationship with drawings, they should be stored as rows in a related table rather than as an ever increasing number of columns in this one. This will also solve your problem of showing only what is relevant since only those rows with data will exist.

#23. An unequivical maybe. It really depends on what your weak points are now. If you know any other programming language, you can pick up VBA on the fly. You might be better off with a good data modeling class so that you can learn how to design databases. Once the db schema is correct, much of the app just falls into place.

 

[craw]

thanks for all your help, Pat - I will give it a try. But i think i might run into trouble because the dimensions are associated with each tool number on the drawing. The drawing name is called a Tab-on, which is only the name of one of the tools on the drawing. So I have a relationship set up between tbl_TabOn and tbl_ToolsOnDrawing.

 

[craw]

Can someone please help. I am trying to hide a column in a subform... the following is the suggestion I've rec'd. but can't get it to work.

Private Sub COMBO_change()
        Me.sfrm_CToolInfo.Form.CuttingDiameterNominal.ColumnHidden = True

End Sub

 

[Schof]

COMBO was the name of the control in the example I gave you. Did you replace it with the name of your control? If so, look at the control's properties and make sure that [Event Procedure] is listed beside the On Change property. If all of that seems OK then step through the code and try to see what is going wrong.

 

[craw]

i thought you told me that i was adding the code to the form not the subform properties

Note: You are hiding the column in the form property of the subform, not the subform properties itself. Make you you have the word Form (see underlined text above) following the name of your subform.

 

[Schof]

You are. As I said,

Let's say you have a combobox called COMBO that when the user selects a different value it affects which records (and controls) are displayed in the subform

Do you not have such a control in the main form? If you do then replace COMBO in the procedure name with the name of that control. If not, what changes in the main form that requires the hiding of a column in the subform?

I am attaching a small sample DB to give you an example of how this works.

 

[craw]

oh snakes!
i get some error when i try to open your example. unrecognized database format.

i do not have such a control.

i just have record selectors that scroll through the records of the mainform. this subform is linked to the main through a field called TabOn. there's a half-a$$ explanation to what my database does/contains in one of my posts.

<i'm doomed>

 

[Schof]

The database was created using MS-Access 2000. What version are you running?

So if the only link between the main and subform is TabOn, how do you when to hide columns in the subform? Are you going to have something like:

select case TabOn
Case "a"
' Hide column 1
Case "b"
' Hide column 2
Case "c"
' Hide column 3
Case "d"
...
End Select

If you would prefer to send me the DB to my yahoo account (tyme4help) I can take a look at it and try to help you that way.

 

[craw]

i'm on '97 --- actually, the case thing is what i'm thinking. or some "if" logic (a really really long if statement) for all possibilities of tabon. i'll send the db to yr yahoo