By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

Can I pass a filter to an update query via vba

P: n/a
I have an update query that runs when a report closes. I have several
reports that will need to run the update query with diferent criteria.
I'd like to simply make the criteria change in the report vba instead
of making different queries.

Here's my query sql:

UPDATE Draw SET Draw.Billed = Yes
WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
And My code that runs the update query

DoCmd.OpenQuery "DrawBillUpdateQueryHudson"

How do I pass the criteria : ((Draw.WholesalerName)="Hudson")); via
the VBA in the report?
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 1 Apr 2004 12:04:05 -0800, dj*******@hotmail.com (Don Seckler)
wrote:
I have an update query that runs when a report closes. I have several
reports that will need to run the update query with diferent criteria.
I'd like to simply make the criteria change in the report vba instead
of making different queries.

Here's my query sql:

UPDATE Draw SET Draw.Billed = Yes
WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
And My code that runs the update query

DoCmd.OpenQuery "DrawBillUpdateQueryHudson"

How do I pass the criteria : ((Draw.WholesalerName)="Hudson")); via
the VBA in the report?

Why not just simply execute an SQL string instead of using a saved
query?

Dim strUpDate As String

strUpDate = "UPDATE Draw SET Draw.Billed = Yes " _
& "WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)='Hudson'));"
'Note you need to use single quotes around text values

CurrentDb.Execute strUpDate, DbFailOnError

- Jim
Nov 12 '05 #2

P: n/a
Thanks Jim. I thought there was a more simple solution.
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40****************@news.west.earthlink.net>. ..
On 1 Apr 2004 12:04:05 -0800, dj*******@hotmail.com (Don Seckler)
wrote:
I have an update query that runs when a report closes. I have several
reports that will need to run the update query with diferent criteria.
I'd like to simply make the criteria change in the report vba instead
of making different queries.

Here's my query sql:

UPDATE Draw SET Draw.Billed = Yes
WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
And My code that runs the update query

DoCmd.OpenQuery "DrawBillUpdateQueryHudson"

How do I pass the criteria : ((Draw.WholesalerName)="Hudson")); via
the VBA in the report?

Why not just simply execute an SQL string instead of using a saved
query?

Dim strUpDate As String

strUpDate = "UPDATE Draw SET Draw.Billed = Yes " _
& "WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)='Hudson'));"
'Note you need to use single quotes around text values

CurrentDb.Execute strUpDate, DbFailOnError

- Jim

Nov 12 '05 #3

P: n/a
Don

I'd do it Jim's way, but if you create a stored update query with a
parameter:[Name, or something] you can pass the parameter in code by
something like
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("name of query")
qdf![Name] = "name"
qdf.Execute
set qdf = nothing

Neil
dj*******@hotmail.com (Don Seckler) wrote in message news:<fe**************************@posting.google. com>...
Thanks Jim. I thought there was a more simple solution.
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40****************@news.west.earthlink.net>. ..
On 1 Apr 2004 12:04:05 -0800, dj*******@hotmail.com (Don Seckler)
wrote:
I have an update query that runs when a report closes. I have several
reports that will need to run the update query with diferent criteria.
I'd like to simply make the criteria change in the report vba instead
of making different queries.

Here's my query sql:

UPDATE Draw SET Draw.Billed = Yes
WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
And My code that runs the update query

DoCmd.OpenQuery "DrawBillUpdateQueryHudson"

How do I pass the criteria : ((Draw.WholesalerName)="Hudson")); via
the VBA in the report?

Why not just simply execute an SQL string instead of using a saved
query?

Dim strUpDate As String

strUpDate = "UPDATE Draw SET Draw.Billed = Yes " _
& "WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)='Hudson'));"
'Note you need to use single quotes around text values

CurrentDb.Execute strUpDate, DbFailOnError

- Jim

Nov 12 '05 #4

P: n/a
Neil,

Watch out using Name, it is an Access reserved keyword.
If you want to go the querydef route, put a parameter in the
WholesalerName field -- [TheName]. Now run it like ....

-------------------------------------
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("DrawBillUpdateQueryHudson")
With qdf
.Parameters("TheName") = Me.WholesalerName
.Execute
End With
Set qdf = Nothing
-------------------------------

This assumes you have a field in the form's recordset named
"WholesalerName"

- Jim

On 6 Apr 2004 07:48:10 -0700, ne***********@boroughmuir.edin.sch.uk
(NeilAnderson) wrote:
Don

I'd do it Jim's way, but if you create a stored update query with a
parameter:[Name, or something] you can pass the parameter in code by
something like
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("name of query")
qdf![Name] = "name"
qdf.Execute
set qdf = nothing

Neil
dj*******@hotmail.com (Don Seckler) wrote in message news:<fe**************************@posting.google. com>...
Thanks Jim. I thought there was a more simple solution.
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40****************@news.west.earthlink.net>. ..
> On 1 Apr 2004 12:04:05 -0800, dj*******@hotmail.com (Don Seckler)
> wrote:
>
> >I have an update query that runs when a report closes. I have several
> >reports that will need to run the update query with diferent criteria.
> > I'd like to simply make the criteria change in the report vba instead
> >of making different queries.
> >
> >Here's my query sql:
> >
> >UPDATE Draw SET Draw.Billed = Yes
> >WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
> >
> >
> >And My code that runs the update query
> >
> >DoCmd.OpenQuery "DrawBillUpdateQueryHudson"
> >
> >How do I pass the criteria : ((Draw.WholesalerName)="Hudson")); via
> >the VBA in the report?
> Why not just simply execute an SQL string instead of using a saved
> query?
>
> Dim strUpDate As String
>
> strUpDate = "UPDATE Draw SET Draw.Billed = Yes " _
> & "WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)='Hudson'));"
> 'Note you need to use single quotes around text values
>
> CurrentDb.Execute strUpDate, DbFailOnError
>
> - Jim


Nov 12 '05 #5

P: n/a
Jim's way is right of course. I always do it in code because a) you
can trap errors, b) troubleshoot it more easily c) you might, like me,
have a tendancy to say "what's that query for? delete it" or d) change
the stored query without realizing what you are doing.

Neil

Ji****@NOTdatacentricsolutions.com (Jim Allensworth) wrote in message news:<40**************@netnews.comcast.net>...
Neil,

Watch out using Name, it is an Access reserved keyword.
If you want to go the querydef route, put a parameter in the
WholesalerName field -- [TheName]. Now run it like ....

-------------------------------------
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("DrawBillUpdateQueryHudson")
With qdf
.Parameters("TheName") = Me.WholesalerName
.Execute
End With
Set qdf = Nothing
-------------------------------

This assumes you have a field in the form's recordset named
"WholesalerName"

- Jim

On 6 Apr 2004 07:48:10 -0700, ne***********@boroughmuir.edin.sch.uk
(NeilAnderson) wrote:
Don

I'd do it Jim's way, but if you create a stored update query with a
parameter:[Name, or something] you can pass the parameter in code by
something like
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("name of query")
qdf![Name] = "name"
qdf.Execute
set qdf = nothing

Neil
dj*******@hotmail.com (Don Seckler) wrote in message news:<fe**************************@posting.google. com>...
Thanks Jim. I thought there was a more simple solution.
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40****************@news.west.earthlink.net>. ..
> On 1 Apr 2004 12:04:05 -0800, dj*******@hotmail.com (Don Seckler)
> wrote:
>
> >I have an update query that runs when a report closes. I have several
> >reports that will need to run the update query with diferent criteria.
> > I'd like to simply make the criteria change in the report vba instead
> >of making different queries.
> >
> >Here's my query sql:
> >
> >UPDATE Draw SET Draw.Billed = Yes
> >WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
> >
> >
> >And My code that runs the update query
> >
> >DoCmd.OpenQuery "DrawBillUpdateQueryHudson"
> >
> >How do I pass the criteria : ((Draw.WholesalerName)="Hudson")); via
> >the VBA in the report?
> Why not just simply execute an SQL string instead of using a saved
> query?
>
> Dim strUpDate As String
>
> strUpDate = "UPDATE Draw SET Draw.Billed = Yes " _
> & "WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)='Hudson'));"
> 'Note you need to use single quotes around text values
>
> CurrentDb.Execute strUpDate, DbFailOnError
>
> - Jim

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.