423,113 Members | 1,976 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,113 IT Pros & Developers. It's quick & easy.

Suddenly receiving Error #3270 - Property Not Found

P: 39
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"?
Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2. Option Explicit
  3. 'This database code has been modified  based on Martin Green's website
  4. '************************** տ- **************************
  5. '*** Coded by Martin Green ******* {Removed} ***
  6. '******* Office Tips Web Site - www.fontstuff.com *********
  7. '**********************************************************
  8.  
  9. ' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
  10. ' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.
  11.  
  12. Public Sub cmdOK_Click()
  13.     On Error GoTo cmdOK_Click_Err
  14.     Dim blnQueryExists As Boolean
  15.     Dim cat As New ADOX.Catalog     'ADOX namespace declared to provide a class named Catalog
  16.     Dim cmd As New ADODB.Command
  17.     Dim qry As ADOX.View
  18.     Dim varItem As Variant
  19.     Dim strGrProgram As String
  20.     Dim strRegion As String
  21.     Dim strState As String
  22.     Dim strRegionCondition As String
  23.     Dim strStateCondition As String
  24.     Dim strSQL As String
  25.     Dim Msg, Style, Title, Response, MyString
  26.  
  27. ' You might want to use a make some sort of temporary table query to creat a table for the cross tabluation table
  28.     DoCmd.SetWarnings False
  29.     DoCmd.OpenQuery "qmak_Data_CROSS", acViewNormal, acEdit
  30.     DoCmd.SetWarnings True
  31.  
  32. ' Check for the existence of the stored query
  33.     blnQueryExists = False
  34.     Set cat.ActiveConnection = CurrentProject.Connection
  35.     For Each qry In cat.Views
  36.         If qry.Name = "qryALL_I2" Then
  37.             blnQueryExists = True
  38.             Exit For
  39.        End If
  40.     Next qry
  41. ' Create the query if it does not already exist
  42.     If blnQueryExists = False Then
  43.         cmd.CommandText = "SELECT * FROM tbNational9"
  44.         cat.Views.Append "qryALL_I2", cmd
  45.     End If
  46.     Application.RefreshDatabaseWindow
  47. ' Turn off screen updating
  48.     DoCmd.Echo False
  49. ' Close the query if it is already open
  50.     If SysCmd(acSysCmdGetObjectState, acQuery, "qryALL_I2") = acObjStateOpen Then
  51.         DoCmd.Close acQuery, "qryALL_I2"
  52.     End If
  53.  
  54. ' Build criteria string for Program
  55.     For Each varItem In Me.lstGrProgram.ItemsSelected
  56.         strGrProgram = strGrProgram & ",'" & Me.lstGrProgram.ItemData(varItem) & "'"
  57.     Next varItem
  58.  
  59.     If Len(strGrProgram) = 0 Then
  60.         strGrProgram = "Like '*'"
  61.  
  62.     Else
  63.         strGrProgram = Right(strGrProgram, Len(strGrProgram) - 1)
  64.         strGrProgram = "IN(" & strGrProgram & ")"
  65.     End If
  66.  
  67.      If strGrProgram = "Lap Band Program" Then
  68.         Msg = "Lap Band program information is unavailable in this I2 run. Please deselect."  'message defined
  69.         Style = vbInformation                                   'button defined
  70.         Title = "I2 Limitation Warning"                      'title defined
  71.         Response = MsgBox(Msg, Style, Title)
  72.     Else
  73.     End If
  74. ' Build criteria string for Region
  75.     For Each varItem In Me.lstRegion.ItemsSelected
  76.         strRegion = strRegion & ",'" & Me.lstRegion.ItemData(varItem) & "'"
  77.     Next varItem
  78.     If Len(strRegion) = 0 Then
  79.         strRegion = "Like '*'"
  80.     Else
  81.         strRegion = Right(strRegion, Len(strRegion) - 1)
  82.         strRegion = "IN(" & strRegion & ")"
  83.     End If
  84. ' Build criteria string for State
  85.     For Each varItem In Me.lstState.ItemsSelected
  86.         strState = strState & ",'" & Me.lstState.ItemData(varItem) & "'"
  87.     Next varItem
  88.     If Len(strState) = 0 Then
  89.         strState = "Like '*'"
  90.     Else
  91.         strState = Right(strState, Len(strState) - 1)
  92.         strState = "IN(" & strState & ")"
  93.     End If
  94. ' Get Region condition
  95.     If Me.optAndRegion.Value = True Then
  96.         strRegionCondition = " AND "
  97.     Else
  98.         strRegionCondition = " OR "
  99.     End If
  100. ' Get State condition
  101.     If Me.optAndState.Value = True Then
  102.         strStateCondition = " AND "
  103.     Else
  104.         strStateCondition = " OR "
  105.     End If
  106. ' Build SQL statement
  107.     strSQL = "SELECT tbNational9.* FROM tbNational9 " & _
  108.              "WHERE tbNational9.[GrProgram] " & strGrProgram & _
  109.              strRegionCondition & "tbNational9.[Region] " & strRegion & _
  110.              strStateCondition & "tbNational9.[StateReg] " & strState & ";"
  111.  
  112. ' Apply the SQL statement to the stored query
  113.     cat.ActiveConnection = CurrentProject.Connection
  114.     Set cmd = cat.Views("qryALL_I2").Command
  115.     cmd.CommandText = strSQL
  116.     Set cat.Views("qryALL_I2").Command = cmd
  117.     Set cat = Nothing
  118. ' Open the Query
  119.     DoCmd.OpenQuery "qryALL_I2", , acReadOnly
  120.  
  121. ' If required the dialog can be closed at this point
  122.  '    DoCmd.Close acForm, Me.Name
  123. ' Restore screen updating
  124.  
  125.     Application.RefreshDatabaseWindow
  126.  
  127.  
  128. cmdOK_Click_Exit:
  129.     DoCmd.Echo True
  130.     Exit Sub
  131. cmdOK_Click_Err:
  132.     MsgBox "An unexpected error hass occurred." _
  133.         & vbCrLf & "Procedure: cmdOK_Click" _
  134.         & vbCrLf & "Error Number: " & Err.Number _
  135.         & vbCrLf & "Error Description:" & Err.Description _
  136.         , vbCritical, "Error"
  137.     Resume cmdOK_Click_Exit
  138. End Sub
Aug 11 '11 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,236
You didn't say which line the error is on.
Aug 11 '11 #2

P: 39
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.
Aug 12 '11 #3

Rabbit
Expert Mod 10K+
P: 12,236
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?
Aug 12 '11 #4

100+
P: 759
Is your query working ? Try it outside VBA code.
I suspect that the query has a problem, not VBA code.
Aug 12 '11 #5

NeoPa
Expert Mod 15k+
P: 30,909
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.
Aug 12 '11 #6

NeoPa
Expert Mod 15k+
P: 30,909
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.
Aug 12 '11 #7

P: 39
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!
Aug 12 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.