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 r****ddenCol As DAO.Recordset
Dim ctl As Control
Dim stHidden As String
Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Set r****ddenCol = dbCurr.OpenRecordset("SELECT
tbl_AllDrillingInfo_HiddenColumns.UserName,
tbl_AllDrillingInfo_HiddenColumns.Column****dden FROM
tbl_AllDrillingInfo_HiddenColumns WHERE
(((tbl_AllDrillingInfo_HiddenColumns.UserName)='" & Forms!
swbPMLogin.txtUser & "'));", dbOpenDynaset)
Do Until r****ddenCol.EOF
Me!sfrm_AllDrillingInfo.Form(r****ddenCol!
Column****dden).ColumnHidden = True
r****ddenCol.MoveNext
Loop
Set r****ddenCol = 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 r****ddenCol As DAO.Recordset
Dim ctl As Control
Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Set r****ddenCol = 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 r****ddenCol
.AddNew
!UserName = Forms!swbPMLogin.txtUser
!Column****dden = ctl.Name
.Update
End With
Else
DoCmd.RunSQL "DELETE
tbl_AllDrillingInfo_HiddenColumns.UserName,
tbl_AllDrillingInfo_HiddenColumns.Column****dden FROM
tbl_AllDrillingInfo_HiddenColumns WHERE
(((tbl_AllDrillingInfo_HiddenColumns.UserName)='" & Forms!
swbPMLogin.txtUser & "') AND
((tbl_AllDrillingInfo_HiddenColumns.Column****dden)='" & ctl.Name &
"'));"
End If
End If
Next
Set r****ddenCol = Nothing
DoCmd.SetWarnings True
DoCmd.OpenForm "frm_Drilling_Well_List"
End Sub
|
