Solved

Hidding and unhidding objects using VBA

Posted on 2008-10-08
2
503 Views
Last Modified: 2013-11-29
Hello,

I'm using the following code to hide and unhide certain tables, queries, forms & modules from the user to prevent them from applying any changes. the code is working fine in terms of getting the job done, but I get  a run-time error 49: Bad DLL calling convention
Where is the issue

Option Compare Database
Option Explicit
 
Public Function HideContactDBTables(Optional strText As String)
    ' Call HideContactDBTables("H")   - To Hide
    ' Call HideContactDBTables("S")   - To Show
    
    Dim intCounter As Integer
    Dim intSetAs As Integer
    Dim bolSetAs As Boolean
    
    'Debug.Print UCase(strText)
    
    If UCase(strText) = "H" Then
        intSetAs = dbHiddenObject
        bolSetAs = True
    Else
        intSetAs = 0
        bolSetAs = False
    End If
    
    ' Tables to hide/show
    Dim strTables(24) As String
    strTables(0) = "tblHonCode"
    strTables(1) = "tblHospitalPhysContacts"
    strTables(2) = "tblImport_SurveyComplete"
    strTables(3) = "tblImport_SurveyLink"
    strTables(4) = "tblMasterList"
    strTables(5) = "tblParticipationHistory"
    strTables(6) = "tblSpecialties"
    strTables(7) = "tblPhysicianList_CopyMaster"
    strTables(8) = "tblPhysRecruitingTargets"
    strTables(9) = "tblProgram_IDIStatus"
    strTables(10) = "tblProjectID"
    strTables(11) = "tblProjSpec_ProjectSpecifications"
    strTables(12) = "tblProjSpec_Clients"
    strTables(13) = "tblProjSpec_Expenses"
    strTables(14) = "tblProjSpec_ProjectDescription"
    strTables(15) = "tblProjSpec_ProjectSpecifications"
    strTables(16) = "tblProjSpec_Expenses"
    strTables(17) = "tblProjSpec_ResearchSample"
    strTables(18) = "tblProjSpec_Services"
    strTables(19) = "tblProjSpec_Targets"
    strTables(20) = "tblUpdate"
    strTables(21) = "tblUserFields"
    strTables(22) = "tblVendors"
    strTables(23) = "tblParticipationHistory"
    strTables(24) = "tblProjectDataCollection"
 
    
    ' Queries to hide/show
    Dim strQueries(32) As String
    strQueries(0) = "qry_CompareContactsNew_Old"
    strQueries(1) = "qry_HonUpdate"
    strQueries(2) = "qry_MatchPhysTempIDs"
    strQueries(3) = "qry_NewPhysToAdd"
    strQueries(4) = "qry_ProjCheckList"
    strQueries(5) = "qry_ProjectPIData"
    strQueries(6) = "qryHospitalPhysContacts"
    strQueries(7) = "qryPIData"
    strQueries(8) = "qryProgram_AdditionalProjects"
    strQueries(9) = "qryProgram_HonorariaToPay_ProjInfo"
    strQueries(10) = "qryProgram_Interview"
    strQueries(11) = "qryProgram_LookupPID"
    strQueries(12) = "qryProgram_Physicians"
    strQueries(13) = "qryProgram_ProjectSpecifications"
    strQueries(14) = "qryProjectNames"
    strQueries(15) = "qryReport_FocusGroupAdBoardSchedule"
    strQueries(16) = "qryReport_HonorariaToPay_FGAB"
    strQueries(17) = "qryReport_HonorariaToPay_IDI"
    strQueries(18) = "qryReport_HonorariaToPay_Survey"
    strQueries(19) = "qryReport_InterviewSchedule"
    strQueries(20) = "qryReport_StatusReport"
    strQueries(21) = "qryScreener"
    strQueries(22) = "qrySorted_Employees"
    strQueries(23) = "qrySorted_ExpenseTypes"
    strQueries(24) = "qrySorted_FGABLocations"
    strQueries(25) = "qrySorted_FGABVendors"
    strQueries(26) = "qrySorted_PastProjects"
    strQueries(27) = "qrySorted_TargetTypes"
    strQueries(28) = "qryUpdateCommunication"
    strQueries(29) = "qryProgram_ExportToConfirmit"
    strQueries(30) = "qryProgram_ExportToExcel"
    strQueries(31) = "qryProgram_ExportToMailMerge"
    strQueries(32) = "qryProgram_VendorNames"
   
    ' Forms to hide/show
    Dim strForms(19) As String
    strForms(0) = "frm_SubFrmUpdate"
    strForms(1) = "frm_SubFrmUpdateCommunication"
    strForms(2) = "frmAddNewContact"
    strForms(3) = "frmInterviewSetUp"
    strForms(4) = "frmLookupByPID"
    strForms(5) = "frmRemoveDups"
    strForms(6) = "frmReport_HonorariaToPay_FGAB"
    strForms(7) = "frmReport_HonorariaToPay_IDI"
    strForms(8) = "frmReport_HonorariaToPay_Survey"
    strForms(9) = "frmSubForm_AdditionalProjects"
    strForms(10) = "frmSubForm_AddToHistory_FGAB"
    strForms(11) = "frmSubForm_AddToHistory_IDI"
    strForms(12) = "frmSubForm_AddToHistory_Survey"
    strForms(13) = "frmSubForm_CompareContactsNew_Old"
    strForms(14) = "frmSubForm_Expenses"
    strForms(15) = "frmSubForm_HospitalPhysContacts"
    strForms(16) = "frmSubForm_lMatchLists"
    strForms(17) = "frmSubForm_ResearchSample"
    strForms(18) = "frmUserFields"
    strForms(19) = "subfrmParticipationHistory"
    
    Dim strMod(10) As String
    strMod(0) = "basAssignPIDs"
    strMod(1) = "basBrowseFiles"
    strMod(2) = "basGlobalInfoFunctions"
    strMod(3) = "basInfoFunctions"
    strMod(4) = "basListTables"
    strMod(5) = "basMatchFunctions"
    strMod(6) = "basOpenform"
    strMod(7) = "basRegistryFunctions"
    strMod(8) = "basRemoveDups"
    strMod(9) = "basReportFunctions"
    strMod(10) = "basTableFunctions"
    
 
    
    ' Hide or show tables
    For intCounter = 0 To UBound(strTables)
        CurrentDb.TableDefs(strTables(intCounter)).Attributes = intSetAs
    Next intCounter
    
    ' Hide or show queries
    For intCounter = 0 To UBound(strQueries)
        Application.SetHiddenAttribute acQuery, strQueries(intCounter), bolSetAs
    Next intCounter
    
    ' Hide or show forms
    For intCounter = 0 To UBound(strForms)
        Application.SetHiddenAttribute acForm, strForms(intCounter), bolSetAs
    Next intCounter
    
    ' Hide or show Modules
    For intCounter = 0 To UBound(strMod)
        Application.SetHiddenAttribute acModule, strMod(intCounter), bolSetAs
    Next intCounter
    
    
    Application.SetHiddenAttribute acMacro, "mcrRefreshTableLinks", bolSetAs
    Application.SetHiddenAttribute acReport, "rptRelationships_PhysDB", bolSetAs
    
    
    'CurrentDb.TableDefs("").Attributes = dbHiddenObject
 
End Function

Open in new window

0
Comment
Question by:AIdoHSG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 22673394
You mean you get the error in the code when you call this function?

Try changing:

Public Function HideContactDBTables(Optional strText As String)

to

Public Function HideContactDBTables(Optional strText As String) as variant

  Shouldn't make any difference, but it's always a good habit to explicitly declare everything.  Make sure you have the right type of variable on the call.

JimD.
0
 

Author Closing Comment

by:AIdoHSG
ID: 31504434
well... that was an easy fix... thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access VBA Control Visibility Not Working Properly 7 54
Access Query function 4 46
Microsoft Access 32-bit or 64-bit? 11 52
office 2016 license quastion 3 36
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question