R
ryguy7272
I hope this is the correct area for this question.
I am trying to use a Form to create a Query.
I found some code on the web:
http://www.fontstuff.com/access/acctut18.htm
It does almost what I want to do, but I modified it slightly for my specific
purposes:
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 cmdOK_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 strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
'Dim strProd As String
'Dim strStatusCondition 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 = "SelQuery" 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 tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If
' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If
‘Problem occurs here!!!
' Build SQL statement
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"WHERE tblTFI.[Prod] " & strProd & ";"
'strProdCondition & "tblTFI.[Prod] " & strProd & ";"
'"WHERE tblTFI.[Prod] " & strProd & ";"
'"WHERE tblTFI.[Prod] " & strProd & ";" '& _
strDepartmentCondition & "tblTFI.[Department] " & strDepartment
& _
''strGenderCondition & "tblStaff.[Gender] " & strGender & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"
' 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
The problem occurs when I try to build the SQL statement. When I use this
piece of code:
' Build SQL statement
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & ";"
The query builds fine and I get the expected results, but this is good only
for one criteria that I query for in the very first ListBox. I tried to
query for a second criteria, in a second ListBox, by modifying the code as
such:
' Build SQL statement
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"WHERE tblTFI.[Prod] " & strProd & ";"
When I do this I get an error that reads:
Syntax Error (missing operator) in query expression ‘tblTFI.[Broker] IN
(‘ADB’) Where tblTFI.[Prod] Like ‘*’
I know the [Prod] variable has been declared but somehow it is not
interpreted by Access. I can’t seem to resolve this error. Can someone see
what I am doing wrong? I’d appreciate any/all help with this.
Regards,
Ryan---
I am trying to use a Form to create a Query.
I found some code on the web:
http://www.fontstuff.com/access/acctut18.htm
It does almost what I want to do, but I modified it slightly for my specific
purposes:
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 cmdOK_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 strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
'Dim strProd As String
'Dim strStatusCondition 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 = "SelQuery" 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 tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If
' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If
‘Problem occurs here!!!
' Build SQL statement
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"WHERE tblTFI.[Prod] " & strProd & ";"
'strProdCondition & "tblTFI.[Prod] " & strProd & ";"
'"WHERE tblTFI.[Prod] " & strProd & ";"
'"WHERE tblTFI.[Prod] " & strProd & ";" '& _
strDepartmentCondition & "tblTFI.[Department] " & strDepartment
& _
''strGenderCondition & "tblStaff.[Gender] " & strGender & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"
' 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
The problem occurs when I try to build the SQL statement. When I use this
piece of code:
' Build SQL statement
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & ";"
The query builds fine and I get the expected results, but this is good only
for one criteria that I query for in the very first ListBox. I tried to
query for a second criteria, in a second ListBox, by modifying the code as
such:
' Build SQL statement
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"WHERE tblTFI.[Prod] " & strProd & ";"
When I do this I get an error that reads:
Syntax Error (missing operator) in query expression ‘tblTFI.[Broker] IN
(‘ADB’) Where tblTFI.[Prod] Like ‘*’
I know the [Prod] variable has been declared but somehow it is not
interpreted by Access. I can’t seem to resolve this error. Can someone see
what I am doing wrong? I’d appreciate any/all help with this.
Regards,
Ryan---