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
JimNOT@NOTdatacentricsolutions.com (Jim Allensworth) wrote in message news:<4072cf3f.5979015@netnews.comcast.net>...[color=blue]
> 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,
neil.anderson@boroughmuir.edin.sch.uk
> (NeilAnderson) wrote:
>[color=green]
> >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
> >
> >
> >djseckler@hotmail.com (Don Seckler) wrote in message news:<fe6bb0b0.0404051159.44fbb86a@posting.google. com>...[color=darkred]
> >> Thanks Jim. I thought there was a more simple solution.
> >>
> >>
> >> "Jim Allensworth" <jimNOT@Notdatacentricsolutions.com> wrote in message news:<406c80c3.860127437@news.west.earthlink.net>. ..
> >> > On 1 Apr 2004 12:04:05 -0800,
djseckler@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[/color][/color][/color]
