OpenQuerydef Problem

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug
 
Hi,
I beleive this is what you're meaning to do:
Set qd = db.QueryDefs("Bank Contact List Append Query")

I don't think you want to open it :-)

HTH
Dan Artuso, MVP
 
There is no OpenQueryDef method. There is CreateQueryDef, but if you want
to set a query to a variable, use:
Set qd = db.QueryDefs("Bank Contact List Append Query")
 
Doug said:
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug
Click to expand...

I'm not aware of an OpenQueryDef method belonging to the DAO Database
object. Maybe it existed in some DAO version before 3.5, but it doesn't
exist any longer. Assuming that db is defined as a DAO.Database object
and qd is defined as a DAO.QueryDef object, and db has been set to an
appropriate value (e.g., "Set db = CurrentDb"), then just write this to
"open" qd:

Set db = db.QueryDefs("Bank Contact List Append Query")

I don't recognize the "Problem" argument in this line:
qd.Execute Problem
Click to expand...

What is that supposed to be? Normally, it would be a Long value or
variable composed of one or more of the options constants supported for
the Execute method, such as dbFailOnError.
 
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug

.
Thanks everybody for your replies and help. This is a
Click to expand...
pretty old database that I'm updating. So, that's why
openquerydef hasn't been used in a long time. I followed
Dirk's instructions and now have the following code.

Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]

OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Now I receive the error "Item not found in this
collection." The onlist column is the listbox that the
record is going to and the offlist is the listbox that
the reocrd was chosen from. The append query includes
the [bank contact id] and [bank/contact Id] fields.

Thanks again for all your help

doug
 
Doug said:
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug

.
Thanks everybody for your replies and help. This is a
Click to expand...
pretty old database that I'm updating. So, that's why
openquerydef hasn't been used in a long time. I followed
Dirk's instructions and now have the following code.

Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]

OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Now I receive the error "Item not found in this
collection." The onlist column is the listbox that the
record is going to and the offlist is the listbox that
the reocrd was chosen from. The append query includes
the [bank contact id] and [bank/contact Id] fields.
Click to expand...

What line is giving the error? If you click Debug on the error dialog,
what line is highlighted?
 
There isn't a debug option in the error message, just an
OK button and the the message.
-----Original Message-----
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug

.
Thanks everybody for your replies and help. This is a
Click to expand...
pretty old database that I'm updating. So, that's why
openquerydef hasn't been used in a long time. I followed
Dirk's instructions and now have the following code.

Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]

OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Now I receive the error "Item not found in this
collection." The onlist column is the listbox that the
record is going to and the offlist is the listbox that
the reocrd was chosen from. The append query includes
the [bank contact id] and [bank/contact Id] fields.
Click to expand...

What line is giving the error? If you click Debug on the error dialog,
what line is highlighted?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Doug said:
There isn't a debug option in the error message, just an
OK button and the the message.
-----Original Message-----
Doug said:
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug

.
Thanks everybody for your replies and help. This is a
pretty old database that I'm updating. So, that's why
openquerydef hasn't been used in a long time. I followed
Dirk's instructions and now have the following code.

Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]

OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Now I receive the error "Item not found in this
collection." The onlist column is the listbox that the
record is going to and the offlist is the listbox that
the reocrd was chosen from. The append query includes
the [bank contact id] and [bank/contact Id] fields.
Click to expand...

What line is giving the error? If you click Debug on the error
dialog, what line is highlighted?
Click to expand...
Click to expand...

Then set a breakpoint at the head of the code routine, then do whatever
is necessary to cause the routine to be called, and when it stops at the
breakpoint, step through line by line using F8 until you find the line
that is raising the error.
 
-----Original Message-----
There isn't a debug option in the error message, just an
OK button and the the message.
-----Original Message-----
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug

.
Thanks everybody for your replies and help. This is a
pretty old database that I'm updating. So, that's why
openquerydef hasn't been used in a long time. I followed
Dirk's instructions and now have the following code.

Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]

OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Now I receive the error "Item not found in this
collection." The onlist column is the listbox that the
record is going to and the offlist is the listbox that
the reocrd was chosen from. The append query includes
the [bank contact id] and [bank/contact Id] fields.

What line is giving the error? If you click Debug on the error
dialog, what line is highlighted?
Click to expand...
Click to expand...

Then set a breakpoint at the head of the code routine, then do whatever
is necessary to cause the routine to be called, and when it stops at the
breakpoint, step through line by line using F8 until you find the line
that is raising the error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Thanks for the help. The code stops at the first 3 lines
of the procedure, which is:
Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
Click to expand...
Thanks again.
-----Original Message-----
There isn't a debug option in the error message, just an
OK button and the the message.
-----Original Message-----
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug

.
Thanks everybody for your replies and help. This is a
pretty old database that I'm updating. So, that's why
openquerydef hasn't been used in a long time. I followed
Dirk's instructions and now have the following code.

Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]

OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Now I receive the error "Item not found in this
collection." The onlist column is the listbox that the
record is going to and the offlist is the listbox that
the reocrd was chosen from. The append query includes
the [bank contact id] and [bank/contact Id] fields.

What line is giving the error? If you click Debug on the error
dialog, what line is highlighted?
Click to expand...
Click to expand...

Then set a breakpoint at the head of the code routine, then do whatever
is necessary to cause the routine to be called, and when it stops at the
breakpoint, step through line by line using F8 until you find the line
that is raising the error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Doug said:
Thanks for the help. The code stops at the first 3 lines
of the procedure, which is:
Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
Click to expand...
Click to expand...
Click to expand...

It *can't* stop at "the first 3 lines"; it has to stop at *one* of
them. Which one? I don't see how you could get that error -- "Item not
found in this collection" -- on the first line. If it is raised on the
second line, it would be telling you that "Bank Contact List Append
Query" is not the name of an existing stored query. Is that true? A
statement like this:

assumes that "Bank Contact List Append Query" is an existing query.

If the error is raised on the third line, it would most likely be saying
that "Bank Contact ID" is not the name of a parameter in the query.
That would be something for you to check. If you aren't sure, post the
SQL of the query, "Bank Contact List Append Query".
 
Here us the SQL for the "Bank contact list append query"
query. When I first received this DB, this query only
included the 2 fields in it, without the table. So, I
added the table and replaced the fields. Therefore, the
query may indeed be the current problem.

INSERT INTO [Bank Contact List] ( [Bank Contact ID],
[Bank/Contact ID] )
SELECT DISTINCTROW [Bank Contact List].[Bank Contact ID],
[Bank Contact List].[Bank/Contact ID]
FROM [Bank Contact List];

Thanks for your continuing help.

Doug
-----Original Message-----
Thanks for the help. The code stops at the first 3 lines
of the procedure, which is:
Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
Click to expand...
Click to expand...

It *can't* stop at "the first 3 lines"; it has to stop at *one* of
them. Which one? I don't see how you could get that error -- "Item not
found in this collection" -- on the first line. If it is raised on the
second line, it would be telling you that "Bank Contact List Append
Query" is not the name of an existing stored query. Is that true? A
statement like this:
Query")

assumes that "Bank Contact List Append Query" is an existing query.

If the error is raised on the third line, it would most likely be saying
that "Bank Contact ID" is not the name of a parameter in the query.
That would be something for you to check. If you aren't sure, post the
SQL of the query, "Bank Contact List Append Query".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Doug said:
Here us the SQL for the "Bank contact list append query"
query. When I first received this DB, this query only
included the 2 fields in it, without the table. So, I
added the table and replaced the fields. Therefore, the
query may indeed be the current problem.

INSERT INTO [Bank Contact List] ( [Bank Contact ID],
[Bank/Contact ID] )
SELECT DISTINCTROW [Bank Contact List].[Bank Contact ID],
[Bank Contact List].[Bank/Contact ID]
FROM [Bank Contact List];

Thanks for your continuing help.

Doug
-----Original Message-----
Doug said:
Thanks for the help. The code stops at the first 3 lines
of the procedure, which is:
Set db = CurrentDb
Set qd = db.QueryDefs("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
Click to expand...

It *can't* stop at "the first 3 lines"; it has to stop at *one* of
them. Which one? I don't see how you could get that error -- "Item
not found in this collection" -- on the first line. If it is raised
Set qd = db.QueryDefs("Bank Contact List Append Query")
Click to expand...

assumes that "Bank Contact List Append Query" is an existing query.

If the error is raised on the third line, it would most likely be
saying that "Bank Contact ID" is not the name of a parameter in the
query. That would be something for you to check. If you aren't
sure, post the SQL of the query, "Bank Contact List Append Query".
Click to expand...
Click to expand...

I'm afraid that query makes no sense at all. As written, it would
appear to be attempting to insert into the table [Bank Contact List]
distinct combinations of values in two fields, [Bank Contact ID] and
[Bank/Contact ID], that already exist in that same table. Why would you
be doing this? Certainly the query has no parameters.

If you modified the original query you were given, you may have done so
mistakenly. Can you post the original SQL of the query, before you
changed it? What do you intend for this query to do?
 
Doug

Please could I ask you to stop "top posting". It is much easier to read a
thread if everyone replies at the bottom or in the text next to the point it
refers to. Then it would go
Suggested solution
Click to expand...

etc.

As it stands readers have to jump all over the message to follow the logic.

Thank you
 
-----Original Message-----
Even after setting the DB's reference to DAO 3.6, the
following code:

Set qd = db.OpenQueryDef("Bank Contact List Append Query")
qd![Bank Contact ID] = OffList.Column(0)
qd![Bank/Contact ID] = Me![AccountID]
qd.Execute Problem
OffList = OffList.ItemData(1)
OnList = OnList.ItemData(1)

OnList.Requery
OffList.Requery

Causes an error at the QueryDef part. Stating
that "Function or interface marked as restricted".

This code moves the highlighted record in a listbox to
another listblox. I know there exists the Docmd.Openquery
method, but I can't get it to work in this procedure.

Thanks in advance for your help. I really appreciate it.

Doug
Click to expand...

I'm not aware of an OpenQueryDef method belonging to the DAO Database
object. Maybe it existed in some DAO version before 3.5, but it doesn't
exist any longer. Assuming that db is defined as a DAO.Database object
and qd is defined as a DAO.QueryDef object, and db has been set to an
appropriate value (e.g., "Set db = CurrentDb"), then just write this to
"open" qd:

Set db = db.QueryDefs("Bank Contact List Append Query")

I don't recognize the "Problem" argument in this line:
qd.Execute Problem
Click to expand...

What is that supposed to be? Normally, it would be a Long value or
variable composed of one or more of the options constants supported for
the Execute method, such as dbFailOnError.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
Dirk,

Thanks for all your help with this problem. The powers
to be have decided not to use this part of the database.
Therefore, I don't want to take any more of your time
trying to figure something out that wasn't designed well
and won't be used. Thanks again for your help, and sorry
that some of the parts of this problem didn't make sense.

Doug
 
Doug said:
Dirk,

Thanks for all your help with this problem. The powers
to be have decided not to use this part of the database.
Therefore, I don't want to take any more of your time
trying to figure something out that wasn't designed well
and won't be used. Thanks again for your help, and sorry
that some of the parts of this problem didn't make sense.

Doug
Click to expand...

Ah well, Doug, sometimes that's the way it goes. Good luck with future
projects.
 
Back
Top