Using a Form to Create a Query. Problem with VBA code...

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---
 
A

Alex Dybenko

Hi,
then problem that you have 2 where, should be:

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & ";"


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

ryguy7272 said:
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---
 
R

ryguy7272

Wow!! That was quite easy. Just couldn't see the solution before. Thank
you so much for your help Alex!!

Regards,
Ryan---


--
RyGuy


Alex Dybenko said:
Hi,
then problem that you have 2 where, should be:

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & ";"


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

ryguy7272 said:
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---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top