|
|
Hi there,
I have created a database that has login names and users. In this
database, I have a datasheet form which can allow users to Hide/Unhide
columns using format -> Unhide columns. I am trying to make the
database remember which columns were hidden by which users so that the
users don't constantly have to hide/unhide the columns they want to
see.
My approach to this is the following:
- Create a table with two field names: UserName, HiddenColumns
- Run a VB procedure on the "On Load" even of the form which looks
into the table and sees which columns are hidden by the logged in
user, then hide the appropriate columns.
- Run a VB procedure on the "On Close" even of the form which looks to
see which controls on the form are hidden, then then adds them to the
HiddenColumns Table with the appropriate username.
I am a relative novice when it comes to coding so I'm hoping there's
someone out there that can help me?!? Thanks
James
|
|
OK, after a LOT of internet searching and testing, I figured this
problem out for anyone who's interested. Here is the code I wrote for
the OnLoad event:
Private Sub Form_Load()
'*******************************************************************************************************
' The following looks into the table tbl_AllDrillingInfo_HiddenColumns
to see which columns are hidden
' for the user. It then hides the appropriate columns.
'*******************************************************************************************************
Dim dbCurr As DAO.Database
Dim rsHiddenCol As DAO.Recordset
Dim ctl As Control
Dim stHidden As String
Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Set rsHiddenCol = dbCurr.OpenRecordset("SELECT
tbl_AllDrillingInfo_HiddenColumns.UserName,
tbl_AllDrillingInfo_HiddenColumns.ColumnsHidden FROM
tbl_AllDrillingInfo_HiddenColumns WHERE
(((tbl_AllDrillingInfo_HiddenColumns.UserName)='" & Forms!
swbPMLogin.txtUser & "'));", dbOpenDynaset)
Do Until rsHiddenCol.EOF
Me!sfrm_AllDrillingInfo.Form(rsHiddenCol!
ColumnsHidden).ColumnHidden = True
rsHiddenCol.MoveNext
Loop
Set rsHiddenCol = Nothing
Me.txtProjName = "<all>"
Me.TxtSec = Null
Me.TxtTwp = Null
Me.TxtRng = Null
Me.sfrm_AllDrillingInfo.SetFocus
End Sub
And here is the OnClose code:
Private Sub Form_Close()
'****************************************************************************************
' the following is used to maintain a list of which columns are hidden
by which user.
' It is done so that the database can remember the columns hidden by
the users, and then
' hide them in the Form_Load event. This is so that users do not have
to constantly hide
' columns/
'****************************************************************************************
Dim dbCurr As DAO.Database
Dim rsHiddenCol As DAO.Recordset
Dim ctl As Control
Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Set rsHiddenCol = dbCurr.OpenRecordset
("tbl_AllDrillingInfo_HiddenColumns", dbOpenDynaset)
DoCmd.SetWarnings False
On Error Resume Next
For Each ctl In Me!sfrm_AllDrillingInfo.Form.Controls
If Right(ctl.Name, 5) = "Label" Then
Else
If ctl.ColumnHidden = True Then
With rsHiddenCol
.AddNew
!UserName = Forms!swbPMLogin.txtUser
!ColumnsHidden = ctl.Name
.Update
End With
Else
DoCmd.RunSQL "DELETE
tbl_AllDrillingInfo_HiddenColumns.UserName,
tbl_AllDrillingInfo_HiddenColumns.ColumnsHidden FROM
tbl_AllDrillingInfo_HiddenColumns WHERE
(((tbl_AllDrillingInfo_HiddenColumns.UserName)='" & Forms!
swbPMLogin.txtUser & "') AND
((tbl_AllDrillingInfo_HiddenColumns.ColumnsHidden)='" & ctl.Name &
"'));"
End If
End If
Next
Set rsHiddenCol = Nothing
DoCmd.SetWarnings True
DoCmd.OpenForm "frm_Drilling_Well_List"
End Sub
|
|
|