Search column names in table and unhiding them

[bryon_woods]

I have a table in access that I have all the columns hidden to begin with by using vba. I need code that will go through all columns in the table and unhide any columns that have "FCT_" in the column name. Any ideas would be a great help. Thanks.

 

[Micron]

Can I ask why there is a need to either hide or unhide table columns? Users should not be able to even get at the tables if the db is split and set up right.
Also, will this be a frequent thing (notwithstanding what I just said)? If not, just do it manually one time and you're done without the bother of code. If frequent, then I don't understand the approach of hiding and unhiding table columns. It suggests the information is too accessible.

 

[bryon_woods]

We have a process where we import a file into a database that can have multiple columns starting with "FCT_" and a random variable at the FCT_ part. These files are being imported anywhere from 1-30 times a day and can have as many as 20 columns starting with FCT_ in them. There is other vba code that does other updates to these columns. At the end of the vba code the table is opened and the user needs to quickly check each of the FCT_ columns to QA and manually check the results in those columns with other columns.
The question of why the user has access to the data doesn't matter, it is their database they are working with, so it is their data. This is simply looking for a way to unhide upwards 20 columns many times a day automatically instead of the user having to do it manually each time.

 

[xenou]

Sample code and information is here:
https://msdn.microsoft.com/en-us/library/office/aa224081(v=office.11).aspx

I haven't done anything like this in a long time (and that, probably just once). I think in general things are easier if you give your user forms to work with (i.e., forms displaying data in datasheet view), rather than letting them work in tables directly. It may work with tables as well, though.

 

[Micron]

The question of why the user has access to the data doesn't matter, it is their database they are working with, so it is their data

All questions matter to the respondent and you have to respect that if you want help. Otherwise I would get the feeling that I should simply solve with no questions asked, which is not the way I want to donate my free time. You have a valid reason, and that's fine. It's your answer I have a problem with.

I think altering the column width would make it easier for users to expand the column afterwards by dragging the border, but that's just an opinion. This should work for hiding the column. Note - it has no obvious effect on an open table until you close and reopen it.

Private Sub SetColVisible()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db("nameOfYourTable")
For Each fld In tdf.Fields
    If fld.Name = "NameOfField" Then fld.Properties("ColumnHidden") = True
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Edit: I forgot that the code has to determine the start of the field name. Let us know if you can't alter the supplied code accordingly. This could also be written to accept the name of a table or partial field name from a form.

 

[xenou]

A possible workaround (code isn't working for me in TABLES to UNHIDE columns) is that if you right-click a column head, you get a drop-down selection for fields to unhide so it is possible to unhide fields a least a little more quickly than one by one, and instead click all the ones you want to unhide all at once.