Avatar of TriMark
TriMark
Flag for United States of America asked on

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
VBAMicrosoft Access

Avatar of undefined
Last Comment
TriMark

8/22/2022 - Mon
John Tsioumpris

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

Open in new window

TriMark

ASKER
Same  result.
John Tsioumpris

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...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Paul Cook-Giles

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"
TriMark

ASKER
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
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TriMark

ASKER
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
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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.
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
TriMark

ASKER
Pat, you are a life (and hair) saver!!!!!

Thanks a million!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PatHartman

I saved your hair but who will save mine?????
Paul Cook-Giles

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

TriMark

ASKER
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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Paul Cook-Giles

Oooooh, that's clever!  Definitely post the code.  :)
TriMark

ASKER
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