Solved

Hidding and unhidding objects using VBA

Posted on 2008-10-08
2
466 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
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now