Passing parameters to an Append query using Access VB

[Guest]

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!

 

[Van T. Dinh]

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}

 

[Guest]

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!

 

[Van T. Dinh]

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

--
HTH
Van T. Dinh
MVP (Access)

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).

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}

 

[Guest]

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.

--
HTH
Van T. Dinh
MVP (Access)

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).

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}

--
HTH
Van T. Dinh
MVP (Access)



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!

 

[Van T. Dinh]

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.

--
HTH
Van T. Dinh
MVP (Access)

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

 

[Guest]

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
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.

--
HTH
Van T. Dinh
MVP (Access)

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

 

[Chris2]

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

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.

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

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.

 

[Van T. Dinh]

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

 

[Guest]

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"
qdf.Parameters("forms!frmQuoteHdr![Quote Number]").Value = _
forms!frmQuoteHdr![Quote Number]
qdf.Execute dbFailOnError


--
HTH
Van T. Dinh
MVP (Access)

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

 

[Van T. Dinh]

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