I'm trying to adapt code from an existing SQL Server database to a MS Access DB. My question is, will this even translate over to Access? I step through it on the original DB and it works, then when I set it to the Access DB and step through it, it does not do anything.
I have all the tables and forms it refers to set up and my test form has the on load procedure set to run this code.
What I want to do is hide and order columns in the DB with this code. What I'm wondering is if Access can handle the setting of the SQL code in the module below.
Thanks for all your help.
Code:
Option Compare Database
'========================================================
'=
'= Prep Sub Form Column
'= This module hides and order all columns on a sub form datasheet
'= It uses Two Tables [Form Rows] and [Form Order]
'= Form Row is counter intautive instead of being what columns to
'= show Form Row is what column to hide. There is no good reason
'= for this.
'= For Order contains the numerical order columns appear
'= there may be duplicate numbers, for example you can have three number 3
'= Access will place them in the order they appear in the file (per Microsoft)
'=
'========================================================
Public Sub prepSubForm(Frm As Form)
Dim con As Object
Dim rs As Object
Dim stSQL As String
On Error Resume Next
Set con = Application.CurrentProject.Connection
stSQL = "SELECT * FROM [frmRow] WHERE [FormName] = '" & Frm.Name & " ';"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSQL, con, 1 ' 1 = adOpenKeyset
If (Not (rs.EOF)) Then
Frm.[LoanID].ColumnHidden = rs![HideLoanID]
Frm.[LoanNumber].ColumnHidden = rs![HideLoanNumber]
Frm.[BorrowerName].ColumnHidden = rs![HideBorrowerName]
Frm.[RM].ColumnHidden = rs![HideRM]
Frm.[RA].ColumnHidden = rs![HideRA]
Else
Frm.[LoanID].ColumnHidden = False
Frm.[LoanNumber].ColumnHidden = False
Frm.[BorrowerName].ColumnHidden = False
Frm.[RM].ColumnHidden = False
Frm.[RA].ColumnHidden = False
End If
rs.Close
stSQL = "SELECT * FROM [frmOrder] WHERE [FormName] = '" & Frm.Name & " ';"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSQL, con, 1 ' 1 = adOpenKeyset
If (Not (rs.EOF)) Then
Frm.[LoanID].ColumnOrder = rs![OrderLoanID]
Frm.[LoanNumber].ColumnOrder = rs![OrderLoanNumber]
Frm.[BorrowerName].ColumnOrder = rs![OrderBorrowerName]
Frm.[RM].ColumnOrder = rs![OrderRM]
Frm.[RA].ColumnOrder = rs![OrderRA]
End If
rs.Close
stSQL = "SELECT * FROM [frmWidth] WHERE [FormName] = '" & Frm.Name & " ';"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSQL, con, 1 ' 1 = adOpenKeyset
If (Not (rs.EOF)) Then
Frm.[LoanID].ColumnWidth = rs![WidthLoanID]
Frm.[LoanNumber].ColumnWidth = rs![WidthLoanNumber]
Frm.[BorrowerName].ColumnWidth = rs![WidthBorrowerName]
Frm.[RM].ColumnWidth = rs![WidthRM]
Frm.[RA].ColumnWidth = rs![WidthRA]
End If
rs.Close
End Sub