Passing parameters to an Append query using Access VB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 18 Nov 2005 20:13 GMT

I am building a very simple quote builder, and need direction on dealing with
the following...

I need to pass a parameter to an append query. Not from a form, but from
Access VB. The reason is that my Part Number, (my key for price lookups) is
is derived based upon the user selecting a couple of resposnes on a form.

All help is appreciated!

Reply to this Message

Van T. Dinh - 18 Nov 2005 21:33 GMT

Check Access VB Help on the Parameters Collection / Parameter Object of the
QueryDef Object (DAO Library needed in the References).

The code should be something like:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("QueryName")
qdf.Parameters("ParamName") = {your value}
qdf.Execute {required options}

Signature

HTH
Van T. Dinh
MVP (Access)

>I am building a very simple quote builder, and need direction on dealing
>with
[quoted text clipped - 6 lines]
>
> All help is appreciated!

Reply to this Message

Ed - 21 Nov 2005 21:31 GMT

Greatr start, and I think I amk almost there, but I suspect syntax is got the
better of me.

Following is my coce...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb

Set qdf = db.QueryDefs("qry_OrdDtl_append")
qdf.Parameters("[prmPN]") = "RB04C"
qdf.Execute dbFailOnError

When running the code, the message I get "Run time error 3061 ... too few
parameters. Expected 2"

My query does have a parm in the cirteria field [prmPN]

Any ideas why this kacks...?

Thx for all the help!

> Check Access VB Help on the Parameters Collection / Parameter Object of the
> QueryDef Object (DAO Library needed in the References).
[quoted text clipped - 19 lines]
> >
> > All help is appreciated!

Reply to this Message

Van T. Dinh - 22 Nov 2005 12:02 GMT

Post relevant details of the Tables and the SQL String of the query.

Signature

HTH
Van T. Dinh
MVP (Access)

> Greatr start, and I think I amk almost there, but I suspect syntax is got
> the
[quoted text clipped - 46 lines]
>> >
>> > All help is appreciated!

Reply to this Message

Ed - 22 Nov 2005 17:15 GMT

Thank you for the help...

SQL string inn the append query "qry_OrdDtl_Append"- cut 'n paste
"PARAMETERS prmPN Text ( 255 );
INSERT INTO Tbl_Quote_Dtl ( PN, [Desc], Qty, Cost, Sell_VAR, Sell_MSRP,
Extended_Sell_VAR, Extended_Sell_MSRP, Quote_Number )
SELECT Tbl_BOM.PN, Tbl_BOM.Description, Tbl_BOM.Qty, Tbl_BOM.Cost,
Tbl_BOM.VAR_Price, Tbl_BOM.MSRP_Price, [VAR_Price]*[Qty] AS Expr1,
[MSRP_Price]*[Qty] AS Expr2, forms!frmQuoteHdr![Quote Number] AS Expr3
FROM Tbl_BOM
WHERE (((Tbl_BOM.PN)=[prmPN]));"

Parameter is set as [prmPN] in criteria field, and is specificed in the
parameter window as text (I read one of your earlier postings)

Table details

Tbl_Quote_Dtl
key is autofield, as PN is not unique, customer is not unique.
Field "PN" is text
Tbl_BOM
Key is PN, as it is unique.
Field "PN" is text

Query runs fine when running from qbe tool...askls for prompt and appends
record to Tbl_Quote_Detail. doesn't run form within form, as parameter is
not set right (assumably)

I hope this helps.

Once again, thank you for your help!.

Ed

> Post relevant details of the Tables and the SQL String of the query.
>
[quoted text clipped - 48 lines]
> >> >
> >> > All help is appreciated!

Reply to this Message

Van T. Dinh - 22 Nov 2005 22:38 GMT

forms!frmQuoteHdr![Quote Number] is also an (inplicit) Parameter that you
need to resolve before JET can process the qdf. The different is that if
you use the Query grid, Access automatically resolve it for you so you may
not think it as a Parameter. However, in code, you need to resolve it for
JET.

Signature

HTH
Van T. Dinh
MVP (Access)

> Thank you for the help...
>
[quoted text clipped - 29 lines]
>
> Ed

Reply to this Message

Ed - 23 Nov 2005 01:06 GMT

OK, dumb question time, but Access help was too confusing.

Resolve how?
e.g.
1) Dim the quote number
2) set the value in code before running the querydef

Thx for all your patience

Ed

> forms!frmQuoteHdr![Quote Number] is also an (inplicit) Parameter that you
> need to resolve before JET can process the qdf. The different is that if
[quoted text clipped - 35 lines]
> >
> > Ed

Reply to this Message

Chris2 - 23 Nov 2005 05:39 GMT

> > > Thank you for the help...
> > >
[quoted text clipped - 46 lines]
>
> Ed

Ed,

That would be "2)".

Untested:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb

Set qdf = db.QueryDefs("qry_OrdDtl_append")
qdf.Parameters("[prmPN]") = "RB04C"
qdf.Parameters("[Quote Number]") = "1234" '<--New Line
qdf.Execute dbFailOnError

Note: I've never personally tried passing parameters in DAO code
where a form's control value was one of the query's parameters, so
I'm not sure I've added the new line correctly or not, but that's
the idea.

Sincerely,

Chris O.

Reply to this Message

Van T. Dinh - 23 Nov 2005 20:02 GMT

Set qdf = db.QueryDefs("qry_OrdDtl_append")
qdf.Parameters("[prmPN]").Value = "RB04C"
qdf.Parameters("forms!frmQuoteHdr![Quote Number]").Value = _
forms!frmQuoteHdr![Quote Number]
qdf.Execute dbFailOnError

Signature

HTH
Van T. Dinh
MVP (Access)

> OK, dumb question time, but Access help was too confusing.
>
[quoted text clipped - 6 lines]
>
> Ed

Reply to this Message

Ed - 23 Nov 2005 21:58 GMT

Thx for all the help....I'm well along the way to getting my project done.

> Set qdf = db.QueryDefs("qry_OrdDtl_append")
> qdf.Parameters("[prmPN]").Value = "RB04C"
[quoted text clipped - 12 lines]
> >
> > Ed

Reply to this Message

Van T. Dinh - 23 Nov 2005 23:44 GMT

You're welcome ... Glad you worked it out.

Signature

HTH
Van T. Dinh
MVP (Access)

> Thx for all the help....I'm well along the way to getting my project done.

Reply to this Message