Results 1 to 2 of 2
  1. #1
    RLJ is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    9

    Macro for only Sequal Server Databse?

    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

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    typically any field you have a WHERE clause on will automatically be hidden.


    So let's say you have the table


    tblTest
    PK TestNumber TestText
    1 1 Test One
    2 1 Test Two
    3 2 Test Three
    4 2 Test Four

    and you just wanted to show the test text where the test number was 1

    you'd have


    SELECT TestText FROM tblTest WHERE TestNumber = 1

    The result of your query should be

    Test One
    Test Two

    If you are looking to hide columns in a combo box or list box you do that in the properties of the combo box/list box

    There is a property for COLUMN COUNT *and* COLUMN WIDTH

    so let's say you wanted to show all your test numbers in a comb box but you only wanted to show the testtext field you would base the combo box on your tblTest then set the column count to 3 and the column widths to 0,0,1

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Databse design for a teacher application
    By James89 in forum Database Design
    Replies: 6
    Last Post: 04-18-2012, 08:54 AM
  2. Remove <0 rule on existing databse
    By pclarejnr in forum Access
    Replies: 1
    Last Post: 04-12-2012, 01:27 PM
  3. Replies: 1
    Last Post: 08-19-2009, 01:54 AM
  4. Access Databse
    By mbarmecha in forum Access
    Replies: 3
    Last Post: 05-26-2009, 02:23 PM
  5. FOLLOW UP DATABSE
    By gab_esp in forum Database Design
    Replies: 2
    Last Post: 07-19-2006, 11:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums