Error 13 when checking Boolean statement

I have, what seems a very simple question. I have a string that I want to check the boolean but get a type mismatch error when  including a control variable.

Here is the code I'm using

Private Sub cmdAnalyze_Click()
   
   Dim ctl As Control
    For Each ctl In Me.reportSubForm.Controls
   
        If ctl.Name <> "_Label" Then
           If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = False Then
             Debug.Print ctl.Name
             End If
        End If
Next


End Sub

Open in new window


It errors out on the line
 If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = False Then

But this works
Private Sub cmdAnalyze_Click()
   
   Dim ctl As Control
    For Each ctl In Me.reportSubForm.Controls
   
        If ctl.Name <> "_Label" Then
             If Me.[reportSubForm].Form.[LastName].ColumnHidden = False Then
             Debug.Print ctl.Name
             End If
        End If
Next

Open in new window


I tried many things, but I am missing something simple.
Any help is appreciated!

Thanks Mark
TriMarkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
How about
If CBool("Me.[reportSubForm].Form.Controls(" & ctl.Name & ").ColumnHidden") = False Then

Open in new window

0
TriMarkAuthor Commented:
Same  result.
0
John TsioumprisSoftware & Systems EngineerCommented:
If you put a breakpoint on this line what do you get ? if its Null it won't CBool...you need Nz first but...put the breakpoint and let us know...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.
Make your If statement "If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = 0 Then"
0
TriMarkAuthor Commented:
At the breakpoint this is what I get

?"Me.[reportSubForm].Form.Controls(" & ctl.Name & ").ColumnHidden"
Me.[reportSubForm].Form.Controls(LastName).ColumnHidden

Paul, I tried "If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = 0 Then" with the  same error 13 happening
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I would say this:

Me.[reportSubForm].Form.Controls(LastName).ColumnHidden

need to become

Me.[reportSubForm].Form.Controls("LastName").ColumnHidden

So this

 If CBool("Me.[reportSubForm].Form.[" & Chr(34) &  ctl.Name & Chr(34) & "].ColumnHidden") = False Then
0
TriMarkAuthor Commented:
Joe, This was one of my ideas also, but did a copy/paste with your string to make sure I didn't typo, and still the same result.

This is what the strings is in the Immediate window
?"Me.[reportSubForm].Form.[" & Chr(34) & ctl.Name & Chr(34) & "].ColumnHidden"
Me.[reportSubForm].Form.["LastName"].ColumnHidden
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
By same result ... you mean you got an error ?
Try this

"Me.(reportSubForm).Form.Controls(" & Chr(34) & ctl.Name & Chr(34) & ").ColumnHidden"

Mainly parens instead of brackets.
0
PatHartmanCommented:
Inside the loop, refer to the control as ctl.  You already have a pointer to the control.   Don't try to reconstruct it.  If you do, you would need to use the Eval() function to get VBA to work it out on the fly.

Private Sub cmdAnalyze_Click()
   
   Dim ctl As Control
    For Each ctl In Me.reportSubForm.Controls
   
        If ctl.Name <> "_Label" Then
           If CBool(ctl.ColumnHidden) = False Then
             Debug.Print ctl.Name
             End If
        End If
Next
End Sub

Open in new window

You probably don't even need the CBool() because .ColumnHidden should be true or false natively.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TriMarkAuthor Commented:
Pat, you are a life (and hair) saver!!!!!

Thanks a million!
0
PatHartmanCommented:
I saved your hair but who will save mine?????
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Oh, sorry... I missed that you were trying to concatenate the object name.  You'd think that would work, but it doesn't.  (Maybe John can explain why;  I don't know.)

This works for me;  I commented out two lines in your code and replaced them to work with my objects (I'm not using the ColumnHidden property anywhere), and got a list of all the visible controls:

   Dim ctl As Control, varProperty As Variant
   
'   For Each ctl In Me.reportSubForm.Controls
    For Each ctl In Me.Controls
   
        If ctl.Name <> "_Label" Then
            varProperty = ctl.Visible
'           If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = False Then
            If varProperty = "True" Then
             Debug.Print ctl.Name
             End If
        End If
Next

Open in new window

0
TriMarkAuthor Commented:
Paul,

This function was tied to a subform that the user manipulates with filters and hiding columns of data to export to an excel file (which is still an open question of mine on this forum, but now that this works, I think I have my answer for the other question and will post my code when I get it done). The visible and HiddenColumn  are two different properties and don't show the same results in my situation. If I would hide a column on this subform, the .Visible property was still set to true.

Thanks for all the help everyone!
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Oooooh, that's clever!  Definitely post the code.  :)
0
TriMarkAuthor Commented:
Paul,
Here is what I adapted to work with my quick database. The query is defined in the form by way of a listbox with several query names that populate a subform with a datasheet style data.

The problem I had was parsing the made up or calculated field names not part of the underlying table field and those columns still show in the excel file. But in the end, it did what I need to do.

Someone with more experience could make this more universal and adapting to the SQL string which I would love to see.

If someone wants to see a database, I can make a striped down version.

Dim strSQL As String
    Dim strSQLSelect As String
    Dim strSQLFrom As String
    Dim strSQLGroup As String
    Dim strSQLHaving As String
    Dim strSQLOrder As String
    Dim strFile As String
    Dim strQry As String
    Dim db As dao.Database
    Dim Qdf As dao.QueryDef
    Dim ctl As Control
    Dim rs As dao.Recordset
    Dim i, j, k, l As Integer
    strSQL = GetQuerySQL(Me.[reportSubForm].Form.RecordSource) 'Get the current SQL String and parse it
    i = InStr(1, strSQL, "FROM")
    j = InStr(1, strSQL, "GROUP")
    k = InStr(1, strSQL, "HAVING")
    l = InStr(1, strSQL, "ORDER BY")
    strSQLSelect = Mid(strSQL, 1, i - 1)  'SELECT part
    For Each ctl In Me.reportSubForm.Controls
        If CStr(Right(ctl.Name, 6)) <> "_Label" Then
            If (ctl.ColumnHidden) <> False Then
                If InStr(1, strSQLSelect, "[") > 0 Then
                    If Mid(strSQLSelect, InStr(1, strSQLSelect, ctl.Name) - 1, 1) = "[" Then
                        strSQLSelect = Replace(strSQLSelect, "FamilyTbl.[" & ctl.Name & "],", "")
                    Else
                        strSQLSelect = Replace(strSQLSelect, "FamilyTbl." & ctl.Name & ",", "")
                    End If
                Else
                    strSQLSelect = Replace(strSQLSelect, "FamilyTbl." & ctl.Name & ",", "")
                End If
            End If
        End If
    Next
    strSQLSelect = TrimMultiSpaces(strSQLSelect)
    If i > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, i, (j - i) - 1)   'FROM
    If j > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, j - 1, (k - j) - 1)  'GROUP
    If k > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, k - 1, (l - k) - 1)  'HAVING
    If l > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, l - 1, Len(strSQL))  'ORDER BY
'start the excel process
    strFile = SpecialFolderPath("desktop") & "\Export.xls"
'If the temp target file exists, delete it first
    If FileExists(strFile) Then
        KillProperly strFile
    End If
    strQry = "qryTempFile"
    Set db = CurrentDb
    On Error Resume Next
    With db
        .QueryDefs.Delete (strQry)
        Set Qdf = .CreateQueryDef(strQry, strSQLSelect)
    End With
    
    Set rs = Qdf.OpenRecordset
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("sheet1").Select
        .ActiveSheet.Range("A2").CopyFromRecordset rs
        For i = 1 To rs.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).value = rs.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With

Open in new window


And the Functions, some I made, some I borrowed

Public Function GetQuerySQL(MyQueryName As String) As String
Dim QD As dao.QueryDef
 
Set QD = CurrentDb.QueryDefs(MyQueryName)
GetQuerySQL = QD.SQL
 
End Function

Open in new window

Public Function TrimMultiSpaces(strSample As String)
    Do While InStr(1, strSample, "  ")
        strSample = Replace(strSample, "  ", " ")
    Loop

    TrimMultiSpaces = Trim(strSample)
End Function

Open in new window

 ' Options For specical folders
 '    AllUsersDesktop
 '    AllUsersStartMenu
 '    AllUsersPrograms
 '    AllUsersStartup
 '    Desktop
 '    Favorites
 '    Fonts
 '    MyDocuments
 '    NetHood
 '    PrintHood
 '    Programs
 '    Recent
 '    SendTo
 '    StartMenu
 '    Startup
 '    Templates
Function SpecialFolderPath(strFolder As String) As String
     
    Dim objWSHShell As Object
    Dim strSpecialFolderPath
     
     'On Error GoTo ErrorHandler
     ' Create a shell object
    Set objWSHShell = CreateObject("WScript.Shell")
     ' Find out the path to the passed special folder,
     '  just change the "Desktop" for one of the other options
     SpecialFolderPath = objWSHShell.SpecialFolders.Item(CVar(strFolder))
    'SpecialFolderPath = objWSHShell.SpecialFolders(strFolder)
     ' Clean up
    Set objWSHShell = Nothing
    Exit Function
ErrorHandler:
     
    MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function

Open in new window

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = FileToTest <> ""
End Function

Open in new window

Public Function KillProperly(Killfile As String)
    If Len(Dir(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Function

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.