A SQL statement in VBA

  • Thread starter Thread starter ericb
  • Start date Start date
E

ericb

On the main form is a subform in datasheet view and 5 command buttons.

On the click of one button I have an event procedure that should run this
query :

SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((([La première date :])<=[date_livraison]) AND
((Produit.date_livraison)<=[ La seconde date]))
GROUP BY [Client Extended].ID_client, [Client Extended].date_ouverture,
[Client Extended].nom_client, [Client Extended].date_naissance, [Client
Extended].couriel
ORDER BY [Client Extended].nom_client

The query is well assigned to a string variable.

I run this :

docmd.runsql mysql

and I always get this message :

Run-time error 2342 : A RunSQL action requires an argument consisting of an
SQL statement.

What to do ?

And mostly how do I get around this problem ?

Thank you for the help
 
A couple tips.

1. Never use spaces in field names, variables,... use _ instead or another
convention
2. Never use special characters (é,è,ç,ô,...) in field names, variables,...

As for your question. Should run the following SQL Statement .... Please
post your actual VBA code so we can properly analyse the situation. We need
to see your use of "" ...
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Actually, I think the message is (or should be) A RunSQL action requires an
argument consisting of an ACTION query. Action queries are queries that
change the data - Update, Delete, Insert.

Since I don't know what you are attempting to do with a SELECT query it is
difficult to tell you what you should be doing. If you are trying to change
the records being displayed by the sub-form you might need to change the
sub-form's source by assigning the SQL string to the sub-form's record source.

If you want to open a query and display the results, then you need to do
something else. Or perhaps you need to open a recordset and do some
processing on the records (which the use of DISTINCT will make impossible).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
One of these I will learn to write every thing at onces.

Here is my simple procedure event that is run when I click the button :

Private Sub Label95_Click()
Dim mySQL As String

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client = Produit.REF_client"
mySQL = mySQL + " WHERE ((([La premiere date :]) <= [date_livraison]) And
((Produit.date_livraison) <= [ La seconde date]))"
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

DoCmd.RunSQL mySQL

'Me![Liste Client HB subform].Form.Requery
'Filtre pour voir les clients entre deux dates
'Selection_dun_filtre (4)

End Sub

I want to run this but that error msg always shows up.

What am I doing wrong ?

Thanks you
--
eric


Daniel Pineault said:
A couple tips.

1. Never use spaces in field names, variables,... use _ instead or another
convention
2. Never use special characters (é,è,ç,ô,...) in field names, variables,...

As for your question. Should run the following SQL Statement .... Please
post your actual VBA code so we can properly analyse the situation. We need
to see your use of "" ...
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



ericb said:
On the main form is a subform in datasheet view and 5 command buttons.

On the click of one button I have an event procedure that should run this
query :

SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((([La première date :])<=[date_livraison]) AND
((Produit.date_livraison)<=[ La seconde date]))
GROUP BY [Client Extended].ID_client, [Client Extended].date_ouverture,
[Client Extended].nom_client, [Client Extended].date_naissance, [Client
Extended].couriel
ORDER BY [Client Extended].nom_client

The query is well assigned to a string variable.

I run this :

docmd.runsql mysql

and I always get this message :

Run-time error 2342 : A RunSQL action requires an argument consisting of an
SQL statement.

What to do ?

And mostly how do I get around this problem ?

Thank you for the help
Click to expand...
Click to expand...
 
The error message is a good one. RunSQL expects and action query like UPDATE,
INSERT INTO, or DELETE. If you want to display a SQL statement, create a
query and open it.
--
Duane Hookom
Microsoft Access MVP


ericb said:
One of these I will learn to write every thing at onces.

Here is my simple procedure event that is run when I click the button :

Private Sub Label95_Click()
Dim mySQL As String

mySQL = "SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " FROM [Client Extended] RIGHT JOIN Produit ON [Client
Extended].ID_client = Produit.REF_client"
mySQL = mySQL + " WHERE ((([La premiere date :]) <= [date_livraison]) And
((Produit.date_livraison) <= [ La seconde date]))"
mySQL = mySQL + " GROUP BY [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel"
mySQL = mySQL + " ORDER BY [Client Extended].nom_client"

DoCmd.RunSQL mySQL

'Me![Liste Client HB subform].Form.Requery
'Filtre pour voir les clients entre deux dates
'Selection_dun_filtre (4)

End Sub

I want to run this but that error msg always shows up.

What am I doing wrong ?

Thanks you
--
eric


Daniel Pineault said:
A couple tips.

1. Never use spaces in field names, variables,... use _ instead or another
convention
2. Never use special characters (é,è,ç,ô,...) in field names, variables,...

As for your question. Should run the following SQL Statement .... Please
post your actual VBA code so we can properly analyse the situation. We need
to see your use of "" ...
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



ericb said:
On the main form is a subform in datasheet view and 5 command buttons.

On the click of one button I have an event procedure that should run this
query :

SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((([La première date :])<=[date_livraison]) AND
((Produit.date_livraison)<=[ La seconde date]))
GROUP BY [Client Extended].ID_client, [Client Extended].date_ouverture,
[Client Extended].nom_client, [Client Extended].date_naissance, [Client
Extended].couriel
ORDER BY [Client Extended].nom_client

The query is well assigned to a string variable.

I run this :

docmd.runsql mysql

and I always get this message :

Run-time error 2342 : A RunSQL action requires an argument consisting of an
SQL statement.

What to do ?

And mostly how do I get around this problem ?

Thank you for the help
Click to expand...
Click to expand...
Click to expand...
 
I am doing this to change the records being displayed by the subform.

What should I do to change the record source with that SQL statement ?

Do I have to set the filterON = True

How do I go about this ?

Thank you
--
eric


John Spencer said:
Actually, I think the message is (or should be) A RunSQL action requires an
argument consisting of an ACTION query. Action queries are queries that
change the data - Update, Delete, Insert.

Since I don't know what you are attempting to do with a SELECT query it is
difficult to tell you what you should be doing. If you are trying to change
the records being displayed by the sub-form you might need to change the
sub-form's source by assigning the SQL string to the sub-form's record source.

If you want to open a query and display the results, then you need to do
something else. Or perhaps you need to open a recordset and do some
processing on the records (which the use of DISTINCT will make impossible).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
On the main form is a subform in datasheet view and 5 command buttons.

On the click of one button I have an event procedure that should run this
query :

SELECT DISTINCT [Client Extended].ID_client, [Client
Extended].date_ouverture, [Client Extended].nom_client, [Client
Extended].date_naissance, [Client Extended].couriel
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((([La première date :])<=[date_livraison]) AND
((Produit.date_livraison)<=[ La seconde date]))
GROUP BY [Client Extended].ID_client, [Client Extended].date_ouverture,
[Client Extended].nom_client, [Client Extended].date_naissance, [Client
Extended].couriel
ORDER BY [Client Extended].nom_client

The query is well assigned to a string variable.

I run this :

docmd.runsql mysql

and I always get this message :

Run-time error 2342 : A RunSQL action requires an argument consisting of an
SQL statement.

What to do ?

And mostly how do I get around this problem ?

Thank you for the help
Click to expand...
.
Click to expand...
 
Try assigning the query string to the subform's record source.

Me![Liste Client HB subform].Form.ControlSource = MySql

If the SQL is correctly formed and has the proper fields for the subform
control's form, that should automatically cause a requery and display the data.

If [ La seconde date] (extra space there before La ???) and [La première date
:] are parameters you will have a problem. If they are controls on the main
form you have two choices:
1) Fully reference them in the SQL statement
[forms]![NameOfMainForm]![La première date :]
2) Incorporate the value of the control into the SQL string
mySQL = mySQL + _
" WHERE #" & Format([La premiere date :],"yyyy-mm-dd") & _
"# <= [date_livraison]) And Produit.date_livraison <=#" & _
Format([ La seconde date] & "#"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
If you simply want to open the query as a datasheet add the following
procedure to your database:
Click to expand...

Why would anyone bother with all that code and creating and deleting a
QueryDef when you can just use a datasheet form and set the
recordsource at runtime?
 
Back
Top