Re: execute append query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Allen (anyone else)

copied sql from query (without parameter) into strings in vba in form,
joined strings and ended with:
"... WHERE PurchaseOrderID = " & me!purchaseorderID

so code now reads:

Dim qdfAppend as querydef
Dim A, B, C, D, E As String
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
A = "INSERT INTO TempPurchaseOrders ..."
B = "..."
....
E = "...WHERE PurchaseOrderID = " & me!purchaseorderID
qdfAppend.SQL = A & B & C & D & E
qdfAppend.Execute

this gave the desired result of only copying the 1 record whose
purchaseorderid = purchaseorderID on the form.

and the depug.print qdfappend.sql showed the same result

not sure why it didn't work the other way.

any further thoughts would be appreciated as i'd much rather use the
existing query

thanks
--
David Benjamin


"dbb1970@xxxxxxxxxxx" wrote:

> Hi Allen
>
> yes i did declare the parameter in the query and yes there was a suitable
> value (number) in the textbox on the form.
> the string is rather long which is why i kept it as a query, but will
> attempt this (will have to join several strings) and see what is happening.
>
> thanks
> --
> David Benjamin
>
>
> "Allen Browne" wrote:
>
> > Did you declare the parameter in the query?
> >
> > Was there a value of a suitable type (a number?) in the text box at the time
> > you executed the code?
> >
> > If all else fails, can you build the string dynamically (as suggested) so
> > you can:
> > Debug.Print strSQL
> > to see what's going on?
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "dbb1970@xxxxxxxxxxx" <dbb1970hotmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > message
> > news:DB0C393E-8AE7-4205-A428-28BBE701425D@xxxxxxxxxxxxxxxx
> > > Hi Allen
> > >
> > > had tried what you suggested below including declaring parameter in query,
> > > and while it removed the "too few parameters" error it appended all
> > > records
> > > not just the one where purchaseorderid = the purchaseorderid on the form
> > >
> > > any other thoughts???
> > >
> > > thanks
> > > --
> > > David Benjamin
> > >
> > >
> > > "Allen Browne" wrote:
> > >
> > >> The Expression Service resolves the reference to the control on the form
> > >> when you execute the query normally, but the ES is not available in DAO.
> > >>
> > >> You can tell DAO how to read the parameter like this:
> > >> qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
> > >> [forms]![purchaseorders]![purchaseorderid]
> > >>
> > >> If the purchaseorderid field is Text (not Number), you will need
> > >> additional
> > >> quotes.
> > >>
> > >> It may also help to declare the parameter:
> > >> - Open the query in design view.
> > >> - Choose Parameters on the Query menu.
> > >> - In the dialog enter something like this:
> > >> [forms]![purchaseorders]![purchaseorderid] Long
> > >>
> > >> My personal preference is to use a query string in VBA code instead of a
> > >> saved query. There are fewer saved queries to manage, and fewer
> > >> dependencies
> > >> (e.g. if you copy a form to reuse it elsewhere). You concatenate the
> > >> value
> > >> into the string, like this:
> > >>
> > >> Dim strSql As String
> > >> strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
> > >> Forms![purchaseorders]![purchaseorderid] & ";"
> > >> dbEngine(0)(0).Execute strSql, dbFailOnError
> > >>
> > >> "dbb1970@xxxxxxxxxxx" <dbb1970hotmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
> > >> in
> > >> message
> > >> news:2328DD27-C7B3-44B7-85AB-090CA077542C@xxxxxxxxxxxxxxxx
> > >> >
> > >> > i have an existing append query called qryTempPurchaseOrder which
> > >> > appends
> > >> > data from another query qryPurchaseOrders to a table TempPurchaseOrder.
> > >> > i
> > >> > have added a criteria within qryTempPurchaseOrder to the field
> > >> > PurchaseOrderID as follows:
> > >> > =[forms]![purchaseorders]![purchaseorderid]
> > >> >
> > >> > this works fine when running from within the append query, with the
> > >> > form
> > >> > PurchaseOrders open in the background, but trying to run with VBA using
> > >> > the
> > >> > following code brings up error "Too few parameters. expected 1":
> > >> >
> > >> > Dim qdfAppend As DAO.QueryDef
> > >> > Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
> > >> > qdfAppend.Execute
> > >> >
> > >> > what am i doing wrong and/or is there a better way to approach it?
> > >> >
> > >> > thanks
> > >> >
> > >> > --
> > >> > David Benjamin
> >
> >
> >
.

Relevant Pages

  • Re: update table from form
    ... all my fields are text / strings and am trying to get form to update table ... as when get it to run query only updating first record. ... Dim stDocName As String ...
    (microsoft.public.access.formscoding)
  • Re: execute append query
    ... It may be that adding dbFailOnError to your original Execute statement would ... > copied sql from query into strings in vba in form, ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Working with tables in Excel 2007.
    ... The only way I've found to handle data tables is using the old ADODB ... only put strings in, and I can't read the index of the string selected, I ... colorful data table displayer in Excel '07, it works in Microsoft's Query ... can't make it work in the ADODB connector, where I can give it parameters ...
    (microsoft.public.excel.programming)
  • Access 2003 treats embedded functions in nested queries as a reference rather than a value - Acc
    ... Microsoft Access 2003 allows one to embed "expressions" in the query ... I also make extensive use of queries that are built from queries, ... Typically the IIf and Switch functions I use return strings. ... Has this behavior changed in Microsoft Access 2007? ...
    (microsoft.public.access.queries)