Can I pass a filter to an update query via vba

[Don Seckler]

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?

[Jim Allensworth]

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

[Don Seckler]

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

[NeilAnderson]

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

[Jim Allensworth]

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

[NeilAnderson]

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