I am trying to do a form run query in MS Access 2010 and it does everything BUT open the query...
It returns a run time error 3270 property not found on the doCmd.OpenQuery
but when rem - ed out the DoCmd.Close acForm, Me.Name works fine
can anyone assist?


Private Sub RunList1_Click()

Rem dao
Dim db As DAO.Database
Dim qdf As DAO.QueryDef


Rem varlist parameters
Dim varItem As Variant

Rem output parameters
Dim strDynamicScript As String
Dim strSubScriptStoreNumber As String
Dim strSubScriptTrade As String
Dim strSubScriptProblem As String
Dim strSubScriptVendor As String

Rem field parameters
Dim strStoreNumber As String
Dim strAndOrStoreNumber As String
Dim strTrade As String
Dim strAndOrTrade As String
Dim strProblem As String
Dim strAndOrProblem As String
Dim strVendor As String
Dim strAndOrVendor As String

For Each varItem In Me.StoreNumber.ItemsSelected
strStoreNumber = strStoreNumber & ",'" & Me.StoreNumber.ItemData(varItem) & "'"
Next varItem

If Len(strStoreNumber) = 0 Then
strStoreNumber = "x"
Else
If Me.andStoreNumber = True Then
strAndOrStoreNumber = " AND "
Else
strAndOrStoreNumber = " OR "
End If
strStoreNumber = Right(strStoreNumber, Len(strStoreNumber) - 1)
strStoreNumber = "IN(" & strStoreNumber & ")"
End If

If strStoreNumber = "x" Then
strSubScriptStoreNumber = ""
Else
strSubScriptStoreNumber = " HistoryReport.[STORE NUMBER] " & strStoreNumber & strAndOrStoreNumber
End If

Rem MsgBox strSubScriptStoreNumber

For Each varItem In Me.Trade.ItemsSelected
strTrade = strTrade & "," & Me.Trade.ItemData(varItem)
Next varItem



If Len(strTrade) = 0 Then
strTrade = "0"
Else
If Me.andTrade = True Then
strAndOrTrade = " AND "
Else
strAndOrTrade = " OR "
End If
strTrade = Right(strTrade, Len(strTrade) - 1)
strTrade = "IN(" & strTrade & ")"
End If

If strTrade = "0" Then
strSubScriptTrade = ""
Else
strSubScriptTrade = " HistoryReport.[TRADE ID] " & strTrade & strAndOrTrade
End If

Rem MsgBox strSubScriptTrade

For Each varItem In Me.Problem.ItemsSelected
strProblem = strProblem & "," & Me.Problem.ItemData(varItem)
Next varItem

If Me.andProblem = True Then
strAndOrProblem = " AND "
Else
strAndOrProblem = " OR "
End If

If Len(strProblem) = 0 Then
strProblem = "0"
Else
If Me.andProblem = True Then
strAndOrProblem = " AND "
Else
strAndOrProblem = " OR "
End If
strProblem = Right(strProblem, Len(strProblem) - 1)
strProblem = "IN(" & strProblem & ")"
End If

If strProblem = "0" Then
strSubScriptProblem = ""
Else
strSubScriptProblem = " HistoryReport.[PUC] " & strProblem & strAndOrProblem
End If

Rem MsgBox strSubScriptProblem

For Each varItem In Me.Vendor.ItemsSelected
strVendor = strVendor & "," & Me.Vendor.ItemData(varItem)
Next varItem


If Len(strVendor) = 0 Then
strVendor = "0"
Else
strVendor = Right(strVendor, Len(strVendor) - 1)
strVendor = "IN(" & strVendor & ")"
End If

If strVendor = "0" Then
strSubScriptVendor = ""
Else
strSubScriptVendor = " HistoryReport.[VENDOR ID] " & strVendor
End If

Rem MsgBox strSubScriptVendor

strDynamicScript = "SELECT HistoryReport.[STORE NUMBER], HistoryReport.[TRADE ID], HistoryReport.[PUC], HistoryReport.[VENDOR ID] FROM HistoryReport WHERE " & strSubScriptStoreNumber & strSubScriptTrade & strSubScriptProblem & strSubScriptVendor & ";"

MsgBox strDynamicScript

Set db = CurrentDb
Set qdf = db.QueryDefs("HistoryReport")
qdf.SQL = strDynamicScript
DoCmd.OpenQuery "HistoryReport"
Rem DoCmd.Close acForm, Me.Name
Set db = Nothing
Set qdf = Nothing

End Sub

Hi,



Just curious... Do you have a table in your db called "HistoryReport?"

Just my 2 cents...

At the time when it errs out, can you view the immediate window and place this:

?strDynamicScript and hit Enter?

After you get that, copy and paste that sql directly into a new, manually-created Query. Run it.

Does it work?

I recognize that what you are trying to do is to execute a query in Excel where the SQL is dynamically changed based on user inputs.

I do this on almost an industrial scale. I've developed about 100 spreadsheets that do this, and some of them are up to their 30th revision in a year. I cannot afford to custom code a solution for every application. I have a generalized solution that uses MS-Query. The SQL script is written in the spreadsheet and then gets copied to the CommandText. If you are open to this kind of solution, I will present it to you.

I agree with ipisors: do you know what the end query statement looks like so you can troubleshoot it?