OpenQuery parameters

  • Thread starter Thread starter OEB
  • Start date Start date
O

OEB

Can I pass parameters when doing an DoCmd.OpenQuery? What is the syntax?
Since the query is used for different forms, I can't put the parameters in
the query.
 
No offense, but what are you intending to do with the query? OpenQuery
strictly presents the data sheet view of the query, and that's usually an
inappropriate vehicle for presenting data to users: you should use a form
instead.
 
Yes, there is a form. This is intended to update another table with data
from the form. This code is "behind the scenes" designed to get the info
before it is removed, which was the original intent of a piece of code I am
updating.
 
If the query's an Action query (UPDATE, INSERT INTO, DELETE, SELECT ...
INTO), you shouldn't be using OpenQuery, you should be using Execute.

If the parameters you're talking about are references to controls on an open
form, you can use:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("NameOfQuery")
For Each prmCurr In qdfAction.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

If you're wanting to set the values of the parameters in code, you can use

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("NameOfQuery")
qdfAction.Parameters("Parm 1").Value = "abc"
qdfAction.Parameters("Parm 2").Value = 25
qdfAction.Parameters("Parm 3").Value = #2009-01-24#
qdfAction.Execute dbFailOnError
 
I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?
 
Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer to the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

OEB said:
Can I pass parameters when doing an DoCmd.OpenQuery? What is the syntax?
Since the query is used for different forms, I can't put the parameters
in
the query.
Click to expand...
Click to expand...
 
Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate, Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE, RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not support this
property or method. And then the first qdfAction says the item is not found.

Douglas J. Steele said:
Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer to the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

OEB said:
Can I pass parameters when doing an DoCmd.OpenQuery? What is the syntax?
Since the query is used for different forms, I can't put the parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've got the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through the
Parameters collection already, and should have pointed that out earlier. You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate, Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE, RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not support this
property or method. And then the first qdfAction says the item is not
found.

Douglas J. Steele said:
Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
Thanks! We are almost there. The append query is based on a query in which
the original person who designed the database asks the user to select 1, 2,
or 3, which is why I tried to hard code to 1 becasue we are probably not
going to have the others (not getting into that here). Anyway, now it is
complaining because the question comes up and I can't figure out how to code
around it. Should I add a dcmma and then PARAMETERS
[Forms].[RTEsbyRTE].[Division] TEXT to the Parameters phrase in the query?
'Cause that doesn't work so I know I am doing something wrong.

Douglas J. Steele said:
I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've got the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through the
Parameters collection already, and should have pointed that out earlier. You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate, Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE, RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not support this
property or method. And then the first qdfAction says the item is not
found.

Douglas J. Steele said:
Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
Your existing query shows RTEInventoryRTEQuery.Division=1, so TEXT would
seem to be inappropriate. If Division is a Long Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If it's just an Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] SHORT;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


OEB said:
Thanks! We are almost there. The append query is based on a query in
which
the original person who designed the database asks the user to select 1,
2,
or 3, which is why I tried to hard code to 1 becasue we are probably not
going to have the others (not getting into that here). Anyway, now it is
complaining because the question comes up and I can't figure out how to
code
around it. Should I add a dcmma and then PARAMETERS
[Forms].[RTEsbyRTE].[Division] TEXT to the Parameters phrase in the query?
'Cause that doesn't work so I know I am doing something wrong.

Douglas J. Steele said:
I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've got the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through the
Parameters collection already, and should have pointed that out earlier.
You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the
beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not support
this
property or method. And then the first qdfAction says the item is not
found.

:

Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your
query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
I added the SHORT one. It is now giving me a Runtine Error 2482 saying that
the database can't find the name...and then has the question that is asked by
the query (RTEInventoryQuery) I am basing the append query on. Should I put
that question in (I tired that and I get a syntax error)?

Douglas J. Steele said:
Your existing query shows RTEInventoryRTEQuery.Division=1, so TEXT would
seem to be inappropriate. If Division is a Long Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If it's just an Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] SHORT;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


OEB said:
Thanks! We are almost there. The append query is based on a query in
which
the original person who designed the database asks the user to select 1,
2,
or 3, which is why I tried to hard code to 1 becasue we are probably not
going to have the others (not getting into that here). Anyway, now it is
complaining because the question comes up and I can't figure out how to
code
around it. Should I add a dcmma and then PARAMETERS
[Forms].[RTEsbyRTE].[Division] TEXT to the Parameters phrase in the query?
'Cause that doesn't work so I know I am doing something wrong.

Douglas J. Steele said:
I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've got the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through the
Parameters collection already, and should have pointed that out earlier.
You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the
beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate, RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not support
this
property or method. And then the first qdfAction says the item is not
found.

:

Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your
query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value = [Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
Not sure I understand what you're saying.

What's the SQL you've currently got?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I added the SHORT one. It is now giving me a Runtine Error 2482 saying
that
the database can't find the name...and then has the question that is asked
by
the query (RTEInventoryQuery) I am basing the append query on. Should I
put
that question in (I tired that and I get a syntax error)?

Douglas J. Steele said:
Your existing query shows RTEInventoryRTEQuery.Division=1, so TEXT would
seem to be inappropriate. If Division is a Long Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If it's just an Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] SHORT;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


OEB said:
Thanks! We are almost there. The append query is based on a query in
which
the original person who designed the database asks the user to select
1,
2,
or 3, which is why I tried to hard code to 1 becasue we are probably
not
going to have the others (not getting into that here). Anyway, now it
is
complaining because the question comes up and I can't figure out how to
code
around it. Should I add a dcmma and then PARAMETERS
[Forms].[RTEsbyRTE].[Division] TEXT to the Parameters phrase in the
query?
'Cause that doesn't work so I know I am doing something wrong.

:

I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've got
the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through the
Parameters collection already, and should have pointed that out
earlier.
You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the
beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a
different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not
support
this
property or method. And then the first qdfAction says the item is
not
found.

:

Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer
to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your
query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is
the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
I have a query on another query that has this line in it:
WHERE (((RTEInventory.Division)=[Enter Division: Retail-1, Institutional-2,
Corporate-3]) AND ((RTEInventory.RTE)=[Enter RTE]))

The answer is always 1 at this point. You suggested in the query I am
executing to put PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If I pass 1 using the suggestion you made, the WHERE clause in the first
query shows up as not being answered. The first parameter populates fine.

How do I code around that?

Douglas J. Steele said:
Not sure I understand what you're saying.

What's the SQL you've currently got?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I added the SHORT one. It is now giving me a Runtine Error 2482 saying
that
the database can't find the name...and then has the question that is asked
by
the query (RTEInventoryQuery) I am basing the append query on. Should I
put
that question in (I tired that and I get a syntax error)?

Douglas J. Steele said:
Your existing query shows RTEInventoryRTEQuery.Division=1, so TEXT would
seem to be inappropriate. If Division is a Long Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If it's just an Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] SHORT;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks! We are almost there. The append query is based on a query in
which
the original person who designed the database asks the user to select
1,
2,
or 3, which is why I tried to hard code to 1 becasue we are probably
not
going to have the others (not getting into that here). Anyway, now it
is
complaining because the question comes up and I can't figure out how to
code
around it. Should I add a dcmma and then PARAMETERS
[Forms].[RTEsbyRTE].[Division] TEXT to the Parameters phrase in the
query?
'Cause that doesn't work so I know I am doing something wrong.

:

I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've got
the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through the
Parameters collection already, and should have pointed that out
earlier.
You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the
beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a
different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not
support
this
property or method. And then the first qdfAction says the item is
not
found.

:

Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to refer
to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of your
query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is
the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
I'm afraid I'm not following you. It looks as though you've got one query
with parameters that prompt the user, and one query with parameters based on
values put into text boxes on a form. There's no way that I can think of to
marry the two queries.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I have a query on another query that has this line in it:
WHERE (((RTEInventory.Division)=[Enter Division: Retail-1,
Institutional-2,
Corporate-3]) AND ((RTEInventory.RTE)=[Enter RTE]))

The answer is always 1 at this point. You suggested in the query I am
executing to put PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If I pass 1 using the suggestion you made, the WHERE clause in the first
query shows up as not being answered. The first parameter populates fine.

How do I code around that?

Douglas J. Steele said:
Not sure I understand what you're saying.

What's the SQL you've currently got?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OEB said:
I added the SHORT one. It is now giving me a Runtine Error 2482 saying
that
the database can't find the name...and then has the question that is
asked
by
the query (RTEInventoryQuery) I am basing the append query on. Should
I
put
that question in (I tired that and I get a syntax error)?

:

Your existing query shows RTEInventoryRTEQuery.Division=1, so TEXT
would
seem to be inappropriate. If Division is a Long Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] LONG;

If it's just an Integer, use

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT,
[Forms].[RTEsbyRTE].[Division] SHORT;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks! We are almost there. The append query is based on a query
in
which
the original person who designed the database asks the user to
select
1,
2,
or 3, which is why I tried to hard code to 1 becasue we are probably
not
going to have the others (not getting into that here). Anyway, now
it
is
complaining because the question comes up and I can't figure out how
to
code
around it. Should I add a dcmma and then PARAMETERS
[Forms].[RTEsbyRTE].[Division] TEXT to the Parameters phrase in the
query?
'Cause that doesn't work so I know I am doing something wrong.

:

I only see one parameter there (Forms].[RTEsbyRTE].[RTE]): you've
got
the
value for Division hard-coded to 1.

That means your code should be:

Dim qdfAction As DAO.QueryDef

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Execute dbFailOnError

Sorry, I missed the fact that you were redundantly looping through
the
Parameters collection already, and should have pointed that out
earlier.
You
could try:

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

but technically your SQL should have a PARAMETERS clause at the
beginning:

PARAMETERS [Forms].[RTEsbyRTE].[RTE] TEXT;
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR, RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate, CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate, RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));


Note that if RTE is a numeric field, you'd replace TEXT with a
different
type, such as LONG.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes. Here is the query:
INSERT INTO RTEHistoryTable ( RTE, ActualUPTTurnoverDate,
ScheduledRECSTurnoverDate, ActualRECSTurnoverDate, UPTSCR,
RECSSCR,
CurrentBaselineVerision, ScheduledUPTTurnoverDate,
CommitmentDate,
Comments,
Division )
SELECT RTEInventoryRTEQuery.RTE,
RTEInventoryRTEQuery.ActualUPTTurnoverDate,
RTEInventoryRTEQuery.ScheduledRECSTurnoverDate,
RTEInventoryRTEQuery.ActualRECSTurnoverDate,
RTEInventoryRTEQuery.UPTSCR,
RTEInventoryRTEQuery.RECSSCR,
RTEInventoryRTEQuery.CurrentBaselineVersionLabel,
RTEInventoryRTEQuery.ScheduledUPTTurnoverDate,
RTEInventoryRTEQuery.CommitmentDate,
RTEInventoryRTEQuery.Comments,
RTEInventoryRTEQuery.Division
FROM RTEInventoryRTEQuery
WHERE (((RTEInventoryRTEQuery.RTE)=[Forms].[RTEsbyRTE].[RTE]) AND
((RTEInventoryRTEQuery.Division)=1));

But when I add the code I get an error saying object does not
support
this
property or method. And then the first qdfAction says the item
is
not
found.

:

Does the query refer to [Forms].[RTEsbyRTE].[RTE] and
[Forms].[RTEsbyRTE].[Division]? If so, that's how you need to
refer
to
the
parameters:

qdfAction.Parameters("[Forms].[RTEsbyRTE].[RTE]").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("[Forms].[RTEsbyRTE].[Division]").value =
[Forms].[RTEsbyRTE].[Division].value

If that doesn't solve the problem, take a look at the SQL of
your
query.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am getting object not found in collection with this code.

Dim qdfAction As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
qdfAction.Parameters("RTE").value =
[Forms].[RTEsbyRTE].[RTE].value
qdfAction.Parameters("Division").value =
[Forms].[RTEsbyRTE].[Division].value

Set qdfAction = CurrentDb.QueryDefs("qryAppendRTEHistory")
For Each prmCurr In qdfAction.Parameters
prmCurr.value = Eval(prmCurr.Name)
Next prmCurr
qdfAction.Execute dbFailOnError

What am I doing wrong?

:

Can I pass parameters when doing an DoCmd.OpenQuery? What is
the
syntax?
Since the query is used for different forms, I can't put the
parameters
in
the query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top