Find record RunSQL in a crosstab query

G

Guest

I am trying to run a query based on a calendar date. When I run this code:
Dim dtDateOfYear As Date
dtDateOfYear = calschedule.Value
DoCmd.FindRecord (dtDateOfYear)

I have this error: Runtime error 2162
A macro set to one of the current field properties failed because of
an error in the FindRecord action argument

I tried also the RunSQL statement:
Dim SQL As String
SQL = "SELECT * FROM qryCrosstab" & _
"WHERE dateofyear = calschedule.value"
DoCmd.RunSQL SQL

The error is Syntax Error in FROM Clause

Anyone can help?
Thanks
 
J

John Vinson

I am trying to run a query based on a calendar date. When I run this code:
Dim dtDateOfYear As Date
dtDateOfYear = calschedule.Value
DoCmd.FindRecord (dtDateOfYear)

I have this error: Runtime error 2162
A macro set to one of the current field properties failed because of
an error in the FindRecord action argument

I tried also the RunSQL statement:
Dim SQL As String
SQL = "SELECT * FROM qryCrosstab" & _
"WHERE dateofyear = calschedule.value"
DoCmd.RunSQL SQL

The error is Syntax Error in FROM Clause
Click to expand...

You probably need to explicitly reference the Form, not just the
control name. and include a blank in the string so it's not read as
qryCrosstabWHERE:

SQL = "SELECT * FROM qryCrosstab " & _
"WHERE DateOfYear = [Forms]![yourformname]![calschedule];"


John W. Vinson[MVP]
 
G

Guest

I did, but it gives me the same error. Anything anyone can think of?
--
LeParis


John Vinson said:
I am trying to run a query based on a calendar date. When I run this code:
Dim dtDateOfYear As Date
dtDateOfYear = calschedule.Value
DoCmd.FindRecord (dtDateOfYear)

I have this error: Runtime error 2162
A macro set to one of the current field properties failed because of
an error in the FindRecord action argument

I tried also the RunSQL statement:
Dim SQL As String
SQL = "SELECT * FROM qryCrosstab" & _
"WHERE dateofyear = calschedule.value"
DoCmd.RunSQL SQL

The error is Syntax Error in FROM Clause
Click to expand...

You probably need to explicitly reference the Form, not just the
control name. and include a blank in the string so it's not read as
qryCrosstabWHERE:

SQL = "SELECT * FROM qryCrosstab " & _
"WHERE DateOfYear = [Forms]![yourformname]![calschedule];"


John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

I did, but it gives me the same error. Anything anyone can think of?
Click to expand...

Please post the actual code you're using. There's something in the
context that is probably the real cause of the error.

John W. Vinson[MVP]
 
G

Guest

Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub

I also used the following code that would result to the same, to no avail:
Dim dtDateOfYear As Date
dtDateOfYear = calschedule.Value
DoCmd.FindRecord (dtDateOfYear)

NB:The query is a crosstab
 
J

John Vinson

Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub
Click to expand...

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
 
G

Guest

I am in a dead end here. How can I use The Openquery with this sql statement?

Thank you
--
LeParis


John Vinson said:
Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub
Click to expand...

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
Click to expand...
 
D

Douglas J Steele

What do you really want: the results displayed in the typical query result
format, or do you want to be able to programmatically use the values?

To create a temporary query that you can open, you can use:

Private Sub calschedule_Click()
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim SQL As String

SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Set dbCurr = CurrentDB()
Set qdfCurr = dbCurr.CreateQueryDef("qryTemp", SQL)
DoCmd.RunSQL "qryTemp"
dbCurr.QueryDefs.Delete "qryTemp"

End Sub

Remember, though, that all this will do, though, is display the results.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeParis said:
I am in a dead end here. How can I use The Openquery with this sql statement?

Thank you
--
LeParis


John Vinson said:
Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub
Click to expand...

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
G

Guest

I am having the same error msg: "Run-time error 3131. Syntax error in FROM
clause"
--
LeParis


Douglas J Steele said:
What do you really want: the results displayed in the typical query result
format, or do you want to be able to programmatically use the values?

To create a temporary query that you can open, you can use:

Private Sub calschedule_Click()
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim SQL As String

SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Set dbCurr = CurrentDB()
Set qdfCurr = dbCurr.CreateQueryDef("qryTemp", SQL)
DoCmd.RunSQL "qryTemp"
dbCurr.QueryDefs.Delete "qryTemp"

End Sub

Remember, though, that all this will do, though, is display the results.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeParis said:
I am in a dead end here. How can I use The Openquery with this sql statement?

Thank you
--
LeParis


John Vinson said:
On Thu, 10 Nov 2005 08:30:22 -0800, "LeParis"

Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

That could be my fault!

I just noticed I left out the required space on either side of the keyword
WHERE:

SQL = "SELECT * FROM qry_Crosstab_AM " & _
" WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeParis said:
I am having the same error msg: "Run-time error 3131. Syntax error in FROM
clause"
--
LeParis


Douglas J Steele said:
What do you really want: the results displayed in the typical query result
format, or do you want to be able to programmatically use the values?

To create a temporary query that you can open, you can use:

Private Sub calschedule_Click()
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim SQL As String

SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Set dbCurr = CurrentDB()
Set qdfCurr = dbCurr.CreateQueryDef("qryTemp", SQL)
DoCmd.RunSQL "qryTemp"
dbCurr.QueryDefs.Delete "qryTemp"

End Sub

Remember, though, that all this will do, though, is display the results.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeParis said:
I am in a dead end here. How can I use The Openquery with this sql statement?

Thank you
--
LeParis


:

On Thu, 10 Nov 2005 08:30:22 -0800, "LeParis"

Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am having this error: Runtime error 3129: Invalid SQL statement; expected
'DELETE','INSERT','PROCEDURE','SELECT', OR 'UPDATE'
Thank you
--
LeParis


Douglas J Steele said:
That could be my fault!

I just noticed I left out the required space on either side of the keyword
WHERE:

SQL = "SELECT * FROM qry_Crosstab_AM " & _
" WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeParis said:
I am having the same error msg: "Run-time error 3131. Syntax error in FROM
clause"
--
LeParis


Douglas J Steele said:
What do you really want: the results displayed in the typical query result
format, or do you want to be able to programmatically use the values?

To create a temporary query that you can open, you can use:

Private Sub calschedule_Click()
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim SQL As String

SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Set dbCurr = CurrentDB()
Set qdfCurr = dbCurr.CreateQueryDef("qryTemp", SQL)
DoCmd.RunSQL "qryTemp"
dbCurr.QueryDefs.Delete "qryTemp"

End Sub

Remember, though, that all this will do, though, is display the results.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am in a dead end here. How can I use The Openquery with this sql
statement?

Thank you
--
LeParis


:

On Thu, 10 Nov 2005 08:30:22 -0800, "LeParis"

Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

After you've defined SQL, put a Debug.Print SQL statement in your code, so
that the content of SQL will be written to the Immediate Window (Ctrl-G).
Copy-and-paste what shows up there here, please, as the code should be
correct.

Also, comment out the line of code dbCurr.QueryDefs.Delete "qryTemp", so
that the query being created (qryTemp) doesn't get deleted afterwards. Go to
the Query tab, and see what happens if you try and run qryTemp manually,
rather than through code.

This assumes, of course, that qry_Crosstab_AM runs properly on its own, that
it has a field named dateofyear in it, that form frm_qry_Crosstab_AM is
open, and that form frm_qry_Crosstab_AM has a control named calschedule



LeParis said:
I am having this error: Runtime error 3129: Invalid SQL statement; expected
'DELETE','INSERT','PROCEDURE','SELECT', OR 'UPDATE'
Thank you
--
LeParis


Douglas J Steele said:
That could be my fault!

I just noticed I left out the required space on either side of the keyword
WHERE:

SQL = "SELECT * FROM qry_Crosstab_AM " & _
" WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LeParis said:
I am having the same error msg: "Run-time error 3131. Syntax error in FROM
clause"
--
LeParis


:

What do you really want: the results displayed in the typical query result
format, or do you want to be able to programmatically use the values?

To create a temporary query that you can open, you can use:

Private Sub calschedule_Click()
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim SQL As String

SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"

Set dbCurr = CurrentDB()
Set qdfCurr = dbCurr.CreateQueryDef("qryTemp", SQL)
DoCmd.RunSQL "qryTemp"
dbCurr.QueryDefs.Delete "qryTemp"

End Sub

Remember, though, that all this will do, though, is display the results.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am in a dead end here. How can I use The Openquery with this sql
statement?

Thank you
--
LeParis


:

On Thu, 10 Nov 2005 08:30:22 -0800, "LeParis"

Here is the code:

Private Sub calschedule_Click()
Dim SQL As String
SQL = "SELECT * FROM qry_Crosstab_AM" & _
"WHERE [dateofyear] = FORMS!frm_qry_Crosstab_AM.calschedule"
DoCmd.RunSQL SQL
End Sub

RunSQL is used for *action* queries - Update, Delete, Append and so
on. It isn't used for select queries like this.

Try instead DoCmd.OpenQuery(SQL).

John W. Vinson[MVP]
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

VBA -- SQL 9
RunSQL question 10
Running SQL statements in VBA 4
Problem with DoCMD.RUNSQL Select statement 7
SQL in VBA 2
Help with FindRecord 1
Run-Time error '2342': 1
SQL -- VBA 3

Top