Suddenly receiving Error #3270 - Property Not Found

[Qtip23]

Hi All,

I have been creating a database that basically searches for data based on the SQL string created. So far, I was successful until I received -- Error Number 3270 - Property Not Found -- when I click on the button to trigger the search.

I modified a database which I found on line for my purposes.

See code below. How do I resolve the error message? What object is VB referring to when it says "property not found"?

 Option Compare Database
Option Explicit
'This database code has been modified  based on Martin Green's website
'************************** Õ¿Õ- **************************
'*** Coded by Martin Green ******* {Removed} ***
'******* Office Tips Web Site - www.fontstuff.com *********
'**********************************************************
 
' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.
 
Public Sub cmdOK_Click()
    On Error GoTo cmdOK_Click_Err
    Dim blnQueryExists As Boolean
    Dim cat As New ADOX.Catalog     'ADOX namespace declared to provide a class named Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
    Dim varItem As Variant
    Dim strGrProgram As String
    Dim strRegion As String
    Dim strState As String
    Dim strRegionCondition As String
    Dim strStateCondition As String
    Dim strSQL As String
    Dim Msg, Style, Title, Response, MyString
 
' You might want to use a make some sort of temporary table query to creat a table for the cross tabluation table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qmak_Data_CROSS", acViewNormal, acEdit
    DoCmd.SetWarnings True
 
' Check for the existence of the stored query
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    For Each qry In cat.Views
        If qry.Name = "qryALL_I2" Then
            blnQueryExists = True
            Exit For
       End If
    Next qry
' Create the query if it does not already exist
    If blnQueryExists = False Then
        cmd.CommandText = "SELECT * FROM tbNational9"
        cat.Views.Append "qryALL_I2", cmd
    End If
    Application.RefreshDatabaseWindow
' Turn off screen updating
    DoCmd.Echo False
' Close the query if it is already open
    If SysCmd(acSysCmdGetObjectState, acQuery, "qryALL_I2") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryALL_I2"
    End If
 
' Build criteria string for Program
    For Each varItem In Me.lstGrProgram.ItemsSelected
        strGrProgram = strGrProgram & ",'" & Me.lstGrProgram.ItemData(varItem) & "'"
    Next varItem
 
    If Len(strGrProgram) = 0 Then
        strGrProgram = "Like '*'"
 
    Else
        strGrProgram = Right(strGrProgram, Len(strGrProgram) - 1)
        strGrProgram = "IN(" & strGrProgram & ")"
    End If
 
     If strGrProgram = "Lap Band Program" Then
        Msg = "Lap Band program information is unavailable in this I2 run. Please deselect."  'message defined
        Style = vbInformation                                   'button defined
        Title = "I2 Limitation Warning"                      'title defined
        Response = MsgBox(Msg, Style, Title)
    Else
    End If
' Build criteria string for Region
    For Each varItem In Me.lstRegion.ItemsSelected
        strRegion = strRegion & ",'" & Me.lstRegion.ItemData(varItem) & "'"
    Next varItem
    If Len(strRegion) = 0 Then
        strRegion = "Like '*'"
    Else
        strRegion = Right(strRegion, Len(strRegion) - 1)
        strRegion = "IN(" & strRegion & ")"
    End If
' Build criteria string for State
    For Each varItem In Me.lstState.ItemsSelected
        strState = strState & ",'" & Me.lstState.ItemData(varItem) & "'"
    Next varItem
    If Len(strState) = 0 Then
        strState = "Like '*'"
    Else
        strState = Right(strState, Len(strState) - 1)
        strState = "IN(" & strState & ")"
    End If
' Get Region condition
    If Me.optAndRegion.Value = True Then
        strRegionCondition = " AND "
    Else
        strRegionCondition = " OR "
    End If
' Get State condition
    If Me.optAndState.Value = True Then
        strStateCondition = " AND "
    Else
        strStateCondition = " OR "
    End If
' Build SQL statement
    strSQL = "SELECT tbNational9.* FROM tbNational9 " & _
             "WHERE tbNational9.[GrProgram] " & strGrProgram & _
             strRegionCondition & "tbNational9.[Region] " & strRegion & _
             strStateCondition & "tbNational9.[StateReg] " & strState & ";"
 
' Apply the SQL statement to the stored query
    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("qryALL_I2").Command
    cmd.CommandText = strSQL
    Set cat.Views("qryALL_I2").Command = cmd
    Set cat = Nothing
' Open the Query
    DoCmd.OpenQuery "qryALL_I2", , acReadOnly
 
' If required the dialog can be closed at this point
 '    DoCmd.Close acForm, Me.Name
' Restore screen updating
 
    Application.RefreshDatabaseWindow
 
 
cmdOK_Click_Exit:
    DoCmd.Echo True
    Exit Sub
cmdOK_Click_Err:
    MsgBox "An unexpected error hass occurred." _
        & vbCrLf & "Procedure: cmdOK_Click" _
        & vbCrLf & "Error Number: " & Err.Number _
        & vbCrLf & "Error Description:" & Err.Description _
        , vbCritical, "Error"
    Resume cmdOK_Click_Exit
End Sub

[Rabbit]

You didn't say which line the error is on.

[Qtip23]

Hi Rabbit,

I have the error trapping set to "Break on All Errors". Line 119 is highlighted.

Not quite sure why because the query exists.

Thx.

[Rabbit]

I don't see anything explicitly wrong with it. When you hover over acReadOnly, does it show the number 2? Have you tried commenting everything else out to see if the issue really is with that line?

[Mihail]

Is your query working ? Try it outside VBA code.
I suspect that the query has a problem, not VBA code.

[NeoPa]

At the point of the error (when line #119 is highlighted) try running that query manually (from the Database Window). If you get the same error then, you need to start looking more closely at the QueryDef itself.

[NeoPa]

Qtip23:
I have the error trapping set to "Break on All Errors".

I missed this earlier. That's a setting to use only when checking code and in the stages of development where you, as a designer, want to see exactly where the code would break if you didn't handle them. This is not a good setting to use in the normal course of events (It can cause all sorts of confusions when running otherwise fine code). I suggest you set it to Break in class module instead, and leave it set that way unless/until you need it set differently for a specific, and temporary, reason.

[Qtip23]

It's working now. I followed some of everyone's tips.

@Rabbit - I did not see the number two when I hovered over. Perhaps this is because I turned off the Break on All Errors.

@Mihail @NeoPa - I ran the query in the database again. It worked like a charm.

I think perhaps the code was getting hung up since the query is based on a form control. The query does not display anything until the parameter (e.g. [forms]![frmABC]![cboEntry]) is selected.

Thanks!