473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

QueryDefs("name") always returning first QueryDef in collection

Hello all,

I have the following code snippet in my Access app:

Dim dbQueryDef As QueryDef
Set dbQueryDef = CurrentDb.QueryDefs("QUERY_NAME") ' (1)
Set dbQueryDef = CurrentDb.QueryDefs(26) ' (2)
Line (1) does not do as I expect it - it always returns the first
QueryDef in the QueryDefs collection (seen by examining the QueryDefs
object at debug time).

However if I pass the ordinal of the correct QueryDef as in line (2)
above then it works fine.

Anyone know what is going on? Is the string in (1) above being
converted into 1 before being passed to the Item(Item) method of the
collections? I'm a VBA newbie so please excuse the innocence of this
question.
Thanks,
Tommy.

PS: It's an Access 2000 file being run from within Access 2003 if
that's a factor. I need to keep it in 2000 format for the client.

Apr 30 '07 #1
2 6230
On 30 Apr 2007 07:29:20 -0700, Tommy Hayes <to*********@gmail.comwrote:
>Hello all,

I have the following code snippet in my Access app:

Dim dbQueryDef As QueryDef
Set dbQueryDef = CurrentDb.QueryDefs("QUERY_NAME") ' (1)
Set dbQueryDef = CurrentDb.QueryDefs(26) ' (2)
Line (1) does not do as I expect it - it always returns the first
QueryDef in the QueryDefs collection (seen by examining the QueryDefs
object at debug time).

However if I pass the ordinal of the correct QueryDef as in line (2)
above then it works fine.
I've not seen this happen, but I don't use the QueryDefs collection very often. I do, however, use other collections a
bit and have yet to see this behavior occur.

After your first Set line, if you access properties of dbQueryDef (like dbQueryDef.SQL) do you see the correct
information?

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
May 1 '07 #2
Tommy Hayes wrote:
Hello all,

I have the following code snippet in my Access app:

Dim dbQueryDef As QueryDef
Set dbQueryDef = CurrentDb.QueryDefs("QUERY_NAME") ' (1)
Set dbQueryDef = CurrentDb.QueryDefs(26) ' (2)
Tommy, try running this slightly modified code as a test and see if it behaves the same:

Dim dbQueryDef As DAO.QueryDef, dbs As Database

Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs("QUERY_NAME") ' (1)
Debug.Print dbQueryDef.Name & " SQL:" & vbCrLf & dbQueryDef.SQL
dbQueryDef.Close
Set dbQueryDef = Nothing
Set dbs = Nothing
--
---------------
John Mishefske, Microsoft Access MVP
May 2 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: TD | last post by:
I created a query in Access 2000 that runs perfectly. I then copied the "sql" version of the same query and set it equal the variable "sql" in the code below. When I run the code below I get an...
6
by: MLH | last post by:
Mainly, I want to search the saved queries. I don't want to replace them. I just want to find them.
14
by: MLH | last post by:
I have the following code to list query names in Access 2.0's Immediate Window. Currently, it lists names of all saved queries. Can you suggest a modification that will result in it listing only...
9
by: Mike Bridge | last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me the error "Join expression not supported". Apparently, Access "fixed" it for...
13
by: royaltiger | last post by:
I am trying to copy the inventory database in Building Access Applications by John L Viescas but when i try to run the database i get an error in the orders form when i click on the allocate...
0
by: Tommy Hayes | last post by:
Hello all, I have the following code snippet in my Access app: Dim dbQueryDef As QueryDef Set dbQueryDef = CurrentDb.QueryDefs("QUERY_NAME") ' (1) Set dbQueryDef = CurrentDb.QueryDefs(26) '...
3
by: myemail.an | last post by:
Hi all, I use Access 2007 and have come across this error a number of times. I have two tables (customer information) which should contain - ideally - the same data. I create a query to compare...
3
by: nickvans | last post by:
Hello all, I have (hopefully) an easy question for you all. I'm running Access '03 and have created a pile of VBA code to create a form and some queries related to each control in the form....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.