Creating queries on the fly

D

dcrqueens

Hello All,

I am using using Martin Green's creating queries on the fly but I am having
some trouble. Below is the code I am using for the On Click even for the OK
button of the form:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatusâ€)
strSQL = "SELECT Tbl CustomerDeliveryTracking.* †& _
"FROM Tbl CustomerDeliveryTracking †& _
"WHERE Tbl CustomerDeliveryTracking.Status='" &
Me.CboStatus.Value & "’ †& _
"AND Tbl CustomerDeliveryTracking.Package Received='" &
Me.CboPackageReceived.Value & "’ †& _
"AND Tbl CustomerDeliveryTracking.Products Received='" &
Me.CboProductsReceived.Value & "’ †& _
"ORDER BY Tbl CustomerDeliveryTracking.Status,Tbl
CustmoerDeliveryTracking.Customer Number;â€

When I click the Ok button I get a Compile Syntax error and the following is
highligted

Set qdf = db.QueryDefs("QryCustomerStatusâ€)

I have checked and rechecked my spelling and reviewed how it is set up. I
cannot seem to figure out why it will not work.

Any help would be greatly appreciated.

Thank you,

Dcrqueens
 
D

Dale Fye

My first thought is that you don't have a query by the name
"QryCustomerStatus"

My second thought is that your reference to tbl CustomerDeliveryTracking is
wrong. If you have a space in the name of a table (or field), you must wrap
the table or field name in brackets, like: [tbl CustomerDeliveryTracking]

HTH
Dale
 
D

Daryl S

Any fields, table names, etc. that have a space in them need to be surrounded
by square brackets. For example, change Tbl CustomerDeliveryTracking to
[Tbl CustomerDeliveryTracking] throughout the code.
 
D

dcrqueens

I tried putting the brackets and I checked the name of the query it does
exist. The error message I am getting states the following:

Invalid SQL Statement expected Delete,Insert,Procedure,Select,Or Update.

Any other suggestions.

Thank you for your help thus far,

dcrqueens

Dale Fye said:
My first thought is that you don't have a query by the name
"QryCustomerStatus"

My second thought is that your reference to tbl CustomerDeliveryTracking is
wrong. If you have a space in the name of a table (or field), you must wrap
the table or field name in brackets, like: [tbl CustomerDeliveryTracking]

HTH
Dale

dcrqueens said:
Hello All,

I am using using Martin Green's creating queries on the fly but I am
having
some trouble. Below is the code I am using for the On Click even for the
OK
button of the form:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatus")
strSQL = "SELECT Tbl CustomerDeliveryTracking.* " & _
"FROM Tbl CustomerDeliveryTracking " & _
"WHERE Tbl CustomerDeliveryTracking.Status='" &
Me.CboStatus.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Package Received='" &
Me.CboPackageReceived.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Products Received='" &
Me.CboProductsReceived.Value & "' " & _
"ORDER BY Tbl CustomerDeliveryTracking.Status,Tbl
CustmoerDeliveryTracking.Customer Number;"

When I click the Ok button I get a Compile Syntax error and the following
is
highligted

Set qdf = db.QueryDefs("QryCustomerStatus")

I have checked and rechecked my spelling and reviewed how it is set up. I
cannot seem to figure out why it will not work.

Any help would be greatly appreciated.

Thank you,

Dcrqueens
Click to expand...


.
Click to expand...
 
D

Dale Fye

can you repost the code now that you have added the brackets? Include all
of the code for the cmdOK_Click event.

Dale


dcrqueens said:
I tried putting the brackets and I checked the name of the query it does
exist. The error message I am getting states the following:

Invalid SQL Statement expected Delete,Insert,Procedure,Select,Or Update.

Any other suggestions.

Thank you for your help thus far,

dcrqueens

Dale Fye said:
My first thought is that you don't have a query by the name
"QryCustomerStatus"

My second thought is that your reference to tbl CustomerDeliveryTracking
is
wrong. If you have a space in the name of a table (or field), you must
wrap
the table or field name in brackets, like: [tbl
CustomerDeliveryTracking]

HTH
Dale

dcrqueens said:
Hello All,

I am using using Martin Green's creating queries on the fly but I am
having
some trouble. Below is the code I am using for the On Click even for
the
OK
button of the form:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatus")
strSQL = "SELECT Tbl CustomerDeliveryTracking.* " & _
"FROM Tbl CustomerDeliveryTracking " & _
"WHERE Tbl CustomerDeliveryTracking.Status='" &
Me.CboStatus.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Package Received='" &
Me.CboPackageReceived.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Products Received='"
&
Me.CboProductsReceived.Value & "' " & _
"ORDER BY Tbl CustomerDeliveryTracking.Status,Tbl
CustmoerDeliveryTracking.Customer Number;"

When I click the Ok button I get a Compile Syntax error and the
following
is
highligted

Set qdf = db.QueryDefs("QryCustomerStatus")

I have checked and rechecked my spelling and reviewed how it is set up.
I
cannot seem to figure out why it will not work.

Any help would be greatly appreciated.

Thank you,

Dcrqueens
Click to expand...


.
Click to expand...
Click to expand...
 
D

dcrqueens

Hi Dale,

Below is the code:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatus")
strSQL = "SELECT [Tbl CustomerDeliveryTracking].* " & _
"FROM [Tbl CustomerDeliveryTracking] " & _
"WHERE [Tbl CustomerDeliveryTracking].Status='" &
Me.CboStatus.Value & "' " & _
"AND [Tbl CustomerDeliveryTracking].[Package Received]='"
& Me.CboPackageReceived.Value & "' " & _
"AND [Tbl CustomerDeliveryTracking].[Products
Received]='" Me.CboProductsReceived.Value & "' " & _
"ORDER BY [Tbl CustomerDeliveryTracking].Status,[Tbl
CustomerDeliveryTracking].[Customer Number];"


Dale Fye said:
can you repost the code now that you have added the brackets? Include all
of the code for the cmdOK_Click event.

Dale


dcrqueens said:
I tried putting the brackets and I checked the name of the query it does
exist. The error message I am getting states the following:

Invalid SQL Statement expected Delete,Insert,Procedure,Select,Or Update.

Any other suggestions.

Thank you for your help thus far,

dcrqueens

Dale Fye said:
My first thought is that you don't have a query by the name
"QryCustomerStatus"

My second thought is that your reference to tbl CustomerDeliveryTracking
is
wrong. If you have a space in the name of a table (or field), you must
wrap
the table or field name in brackets, like: [tbl
CustomerDeliveryTracking]

HTH
Dale

Hello All,

I am using using Martin Green's creating queries on the fly but I am
having
some trouble. Below is the code I am using for the On Click even for
the
OK
button of the form:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatus")
strSQL = "SELECT Tbl CustomerDeliveryTracking.* " & _
"FROM Tbl CustomerDeliveryTracking " & _
"WHERE Tbl CustomerDeliveryTracking.Status='" &
Me.CboStatus.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Package Received='" &
Me.CboPackageReceived.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Products Received='"
&
Me.CboProductsReceived.Value & "' " & _
"ORDER BY Tbl CustomerDeliveryTracking.Status,Tbl
CustmoerDeliveryTracking.Customer Number;"

When I click the Ok button I get a Compile Syntax error and the
following
is
highligted

Set qdf = db.QueryDefs("QryCustomerStatus")

I have checked and rechecked my spelling and reviewed how it is set up.
I
cannot seem to figure out why it will not work.

Any help would be greatly appreciated.

Thank you,

Dcrqueens




.
Click to expand...
Click to expand...


.
Click to expand...
 
J

John Spencer

Missing an ampersand in this phrase
[Products Received]='" Me.CboProductsReceived.Value & "' "

strSQL = "SELECT [Tbl CustomerDeliveryTracking].* " & _
"FROM [Tbl CustomerDeliveryTracking] " & _
"WHERE [Tbl CustomerDeliveryTracking].Status='" & _
Me.CboStatus.Value & "' " & _
"AND [Tbl CustomerDeliveryTracking].[Package Received]='" & _
Me.CboPackageReceived.Value & "' " & _
"AND [Tbl CustomerDeliveryTracking].[Products Received]='" & _
Me.CboProductsReceived.Value & "' " & _
"ORDER BY [Tbl CustomerDeliveryTracking].Status" & _
",[Tbl CustomerDeliveryTracking].[Customer Number];"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Dale,

Below is the code:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatus")
strSQL = "SELECT [Tbl CustomerDeliveryTracking].* " & _
"FROM [Tbl CustomerDeliveryTracking] " & _
"WHERE [Tbl CustomerDeliveryTracking].Status='" &
Me.CboStatus.Value & "' " & _
"AND [Tbl CustomerDeliveryTracking].[Package Received]='"
& Me.CboPackageReceived.Value & "' " & _
"AND [Tbl CustomerDeliveryTracking].[Products
Received]='" Me.CboProductsReceived.Value & "' " & _
"ORDER BY [Tbl CustomerDeliveryTracking].Status,[Tbl
CustomerDeliveryTracking].[Customer Number];"


Dale Fye said:
can you repost the code now that you have added the brackets? Include all
of the code for the cmdOK_Click event.

Dale


dcrqueens said:
I tried putting the brackets and I checked the name of the query it does
exist. The error message I am getting states the following:

Invalid SQL Statement expected Delete,Insert,Procedure,Select,Or Update.

Any other suggestions.

Thank you for your help thus far,

dcrqueens

:

My first thought is that you don't have a query by the name
"QryCustomerStatus"

My second thought is that your reference to tbl CustomerDeliveryTracking
is
wrong. If you have a space in the name of a table (or field), you must
wrap
the table or field name in brackets, like: [tbl
CustomerDeliveryTracking]

HTH
Dale

Hello All,

I am using using Martin Green's creating queries on the fly but I am
having
some trouble. Below is the code I am using for the On Click even for
the
OK
button of the form:

Private Sub CmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCustomerStatus")
strSQL = "SELECT Tbl CustomerDeliveryTracking.* " & _
"FROM Tbl CustomerDeliveryTracking " & _
"WHERE Tbl CustomerDeliveryTracking.Status='" &
Me.CboStatus.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Package Received='" &
Me.CboPackageReceived.Value & "' " & _
"AND Tbl CustomerDeliveryTracking.Products Received='"
&
Me.CboProductsReceived.Value & "' " & _
"ORDER BY Tbl CustomerDeliveryTracking.Status,Tbl
CustmoerDeliveryTracking.Customer Number;"

When I click the Ok button I get a Compile Syntax error and the
following
is
highligted

Set qdf = db.QueryDefs("QryCustomerStatus")

I have checked and rechecked my spelling and reviewed how it is set up.
I
cannot seem to figure out why it will not work.

Any help would be greatly appreciated.

Thank you,

Dcrqueens



.
Click to expand...

.
Click to expand...
Click to expand...
 

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

Similar Threads

Creating queries on the fly (Form not working) 1
Creating a table with all the "object dependancies" 4
Between Dates on Combo Form 10
How to display the value of a variable on a form or report 1
Create Report On the Fly 1
Actions Queries in ADO 5
Creating numbering on the fly 4
Calculate on the fly vs update query 3

Top