R
ryguy7272
I am trying to build a dynamic Query from a Form.
I keep getting an error that reads ‘Object qryFilter already exists’
I suspect it has something to do with the string of dates being passed to
the Query;
strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom]
And [Forms]![SearchForm]![cboTo])"
I am trying to add a means for a user to Query by Customer and Trader AND
all records between two dates. This was working fine for Customer and
Trader; when I added in the code to filter by dates I started having
problems.
I know the SQL will be like this:
SELECT Trades.TDATE, *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
[Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
((Trades.Trader) Like '*'));
That’s what I have to pass from the VBA; just not sure how to tell the VBA
to convert to that SQL.
Here is all my code:
Option Compare Database
Option Explicit
' 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.
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 strDateCondition As String
Dim strCust As String
Dim strTrader As String
Dim strCustCondition 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
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 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 for string Date
strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom]
And [Forms]![SearchForm]![cboTo])"
' Build criteria string for Customer
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 Department condition
If Me.optAndTrader.Value = True Then
strCustCondition = " AND "
Else
strCustCondition = " OR "
End If
' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE strDateCondition And [Trades].[Cust] " & strCust & _
strCustCondition & "[Trades].[Trader] " & strTrader & _
strTraderCondition & ";"
' 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
Thanks for any/all help with this!!
I keep getting an error that reads ‘Object qryFilter already exists’
I suspect it has something to do with the string of dates being passed to
the Query;
strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom]
And [Forms]![SearchForm]![cboTo])"
I am trying to add a means for a user to Query by Customer and Trader AND
all records between two dates. This was working fine for Customer and
Trader; when I added in the code to filter by dates I started having
problems.
I know the SQL will be like this:
SELECT Trades.TDATE, *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![SearchForm]![cboFrom] And
[Forms]![SearchForm]![cboTo]) AND ((Trades.Cust) Like '*') AND
((Trades.Trader) Like '*'));
That’s what I have to pass from the VBA; just not sure how to tell the VBA
to convert to that SQL.
Here is all my code:
Option Compare Database
Option Explicit
' 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.
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 strDateCondition As String
Dim strCust As String
Dim strTrader As String
Dim strCustCondition 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
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 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 for string Date
strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom]
And [Forms]![SearchForm]![cboTo])"
' Build criteria string for Customer
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 Department condition
If Me.optAndTrader.Value = True Then
strCustCondition = " AND "
Else
strCustCondition = " OR "
End If
' Build SQL statement
strSQL = "SELECT * FROM Trades " & _
"WHERE strDateCondition And [Trades].[Cust] " & strCust & _
strCustCondition & "[Trades].[Trader] " & strTrader & _
strTraderCondition & ";"
' 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
Thanks for any/all help with this!!