Thanks John and Jim! I got it worked out. For anyone who is interested,
here is the final version of code:
Private Sub cmdRun_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strTraderCondition As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryFilter")
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
Then
DoCmd.Close acQuery, "qryFilter"
End If
' Build criteria string for Office
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If
' Get condition
If Me.optAndTrader.Value = True Then
strTraderCondition = " AND "
Else
strTraderCondition = " OR "
End If
' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom]
And [Forms]![QueryForm]![cboTo]) And (Trades.[Cust] " & strCust & _
strTraderCondition & "Trades.[Trader] " & strTrader & ");"
' Open the Query
qdf.SQL = strSQL
DoCmd.OpenQuery "qryFilter"
Set qdf = Nothing
Set db = Nothing
End Sub
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"JimBurke via AccessMonster.com" wrote:
> I didn't look through all of your code, but it sounds like you're defining a
> query to access when you could just be running the SQL and not actually
> creating a query. Is there a reason you're creating a query, or am I
> misinterpreting? You can just execute an SQL statement without actually
> defining a new query.
>
> ryguy7272 wrote:
> >I’m wondering if it makes sense to delete a query which I build on the fly
> >each time I use. If I start with no Query, and then run my code, the Query
> >is loaded with SQL, which comes from several objects on a Form. This is all
> >driven by VBA. In the original example, the author did NOT delete the query,
> >but with the query present, I keep getting messages like this:
> >‘Error Description: Object ‘qryFilter’ already exists.’
> >
> >If I manually delete the Query and rerun my code, everything works fine.
> >Would it make more sense to empty the query and then populate it with SQL?
> >
> >Here is my code (works fine except that I keep getting that ‘query already
> >exists message’):
> >
> >Option Compare Database
> >Option Explicit
> >
> >Private Sub cmdRun_Click()
> >
> > On Error GoTo cmdOK_Click_Err
> > Dim blnQueryExists As Boolean
> > Dim cat As New ADOX.Catalog
> > Dim cmd As New ADODB.Command
> > Dim qry As ADOX.View
> > Dim varItem As Variant
> > Dim strCust As String
> > Dim strTrader As String
> > Dim strTraderCondition As String
> >
> > Dim strSQL As String
> >' Check for the existence of the stored query
> > blnQueryExists = False
> > Set cat.ActiveConnection = CurrentProject.Connection
> > For Each qry In cat.Views
> > If qry.Name = "qryFilter" Then
> > DoCmd.DeleteObject acQuery, "qryFilter"
> > Exit For
> > End If
> > Next qry
> >' Create the query if it does not already exist
> > If blnQueryExists = False Then
> > cmd.CommandText = "SELECT * FROM Trades"
> > cat.Views.Append "qryFilter", cmd
> > End If
> > Application.RefreshDatabaseWindow
> >' Turn off screen updating
> > DoCmd.Echo False
> >' Close the query if it is already open
> > If SysCmd(acSysCmdGetObjectState, acQuery, "qryFilter") = acObjStateOpen
> >Then
> > DoCmd.Close acQuery, "qryFilter"
> > End If
> >' Build criteria string for Office
> > For Each varItem In Me.lstCust.ItemsSelected
> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) & "'"
> > Next varItem
> > If Len(strCust) = 0 Then
> > strCust = "Like '*'"
> > Else
> > strCust = Right(strCust, Len(strCust) - 1)
> > strCust = "IN(" & strCust & ")"
> > End If
> >' Build criteria string for Trader
> > For Each varItem In Me.lstTrader.ItemsSelected
> > strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
> > Next varItem
> > If Len(strTrader) = 0 Then
> > strTrader = "Like '*'"
> > Else
> > strTrader = Right(strTrader, Len(strTrader) - 1)
> > strTrader = "IN(" & strTrader & ")"
> > End If
> >
> >' Get condition
> >
> > If Me.optAndTrader.Value = True Then
> > strTraderCondition = " AND "
> > Else
> > strTraderCondition = " OR "
> > End If
> >
> >' Build SQL statement
> > strSQL = "SELECT * FROM Trades " & _
> > "WHERE Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
> >[Forms]![QueryForm]![cboTo] And Trades.[Cust] " & strCust & _
> > strTraderCondition & "Trades.[Trader] " & strTrader & ";"
> >' Apply the SQL statement to the stored query
> > cat.ActiveConnection = CurrentProject.Connection
> > Set cmd = cat.Views("qryFilter").Command
> > cmd.CommandText = strSQL
> > Set cat.Views("qryFilter").Command = cmd
> > Set cat = Nothing
> >' Open the Query
> > DoCmd.OpenQuery "qryFilter"
> >' If required the dialog can be closed at this point
> > ' DoCmd.Close acForm, Me.Name
> >' Restore screen updating
> >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
> >
> >Private Sub optAndTrader_Click()
> >' Toggle option buttons
> > If Me.optAndTrader.Value = True Then
> > Me.optOrTrader.Value = False
> > Else
> > Me.optOrTrader.Value = True
> > End If
> >End Sub
> >
> >Private Sub optOrTrader_Click()
> >' Toggle option buttons
> > If Me.optOrTrader.Value = True Then
> > Me.optAndTrader.Value = False
> > Else
> > Me.optAndTrader.Value = True
> > End If
> >End Sub
> >
> >Finally, I already tried to delete the Query with this snippet of code:
> >' Check for the existence of the stored query
> > blnQueryExists = False
> > Set cat.ActiveConnection = CurrentProject.Connection
> > For Each qry In cat.Views
> > If qry.Name = "qryFilter" Then
> > DoCmd.DeleteObject acQuery, "qryFilter"
> > Exit For
> > End If
> > Next qry
> >
> >That actually doesn’t do anything, and I can’t figure out why that doesn’t
> >work.
> >
> >I’d sincerely appreciate help with this.
> >
> >Thanks everyone!
> >Ryan--
> >
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/201002/1
>
> .
>
Similar Threads
