Listing Fields in a query 2

[GJP55]

I am trying to write code that will list the field names from a query. What I have so far is :

Dim db As Database
Dim fld As Field
Dim qry As QueryDef
Dim str As String
Dim i As Integer

Set qry = db.QueryDefs("MyQuery&quot
Set db = CurrentDb()
Set fld = qry.Fields

For i = 1 To qry.Fields.Count
str = qry.Fields(i).name
Debug.Print str

Next

Problem I get is an error message saying "Object variable or With block variable not set91" when I try to run it.

Can anybody piont out what Im doing wrong .

Thanks

 

[Nikita6003]

Try switching these 2 around:
first SET the db object:

Set db = CurrentDb()

then USE the db object:

Set qry = db.QueryDefs("MyQuery")

HTH ;-)

Cheers
Nikki

 

[GJP55]

Nikki,

That sorted out part of the problem - thanks.

Tweeking the code now to get the rest to work.

Thanks very much

GJP

 

[Nikita6003]

plz remember that the fields array is zero-based ...

so you'll have loop from 0 to Fields.Count


Cjheers
Nikki

ps - yw

 

[jebry]

Hi!

Don't know if your still listening or what exactly you plan on doing with the list once you have it but the easiest way to get the list is to set the rowsourcetype of a list box to Field List and the rowsource to your query name and the list box will display the field names. This is also a simple way to allow users to select fields from a query or table to be displayed in a report or selected in a query etc.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[GJP55]

Nikki,

Thanks for that, found that one the hard way last week.
-------------------------------------------------------
Jeff,

I am also working on a report that the user selects the fields they want to see, so that will be useful.

Thanks to both.

Garry

 

[GJP55]

I am still having problems getting this to show the field names. This time I get a 'Type mismatch13' error.

Code:

Dim db As Database
Dim qry As QueryDef
Dim str As String
Dim i As Integer

Set db = CurrentDb()
Set qry = db.QueryDefs("MyQuery&quot

For i = 0 To qry.Fields.Count - 1
str = qry.Fields(i).name
Debug.Print str
Next


Any ideas???

 

[Nikita6003]

You can only list fields off of a recordset object. Try changing your code to thi:

Dim db As Database
Dim qry As QueryDef
Dim rs As Recordset
Dim str As String
Dim i As Integer

Set db = CurrentDb()
Set qry = db.QueryDefs("MyQuery")
Set rs = qry.OpenRecordset(dbOpenDynamic)

For i = 0 To rs.Fields.Count - 1
str = rs.Fields(i).name
Debug.Print str
Next

Cheers
Nikki

 

[GJP55]

Nikki,

Tried that but I get an 'Invalid Argument.3001' error.

Garry

 

[Nikita6003]

Try removing the

dbOpenDynamic

option in the

rs.OpenRecordset

line.

That'll force Access to use the standard recordset type

Cheers
Nikki

 

[GJP55]

Nikki,

Still get the same error unfortuneatley.

Garry

 

[Nikita6003]

Could you tell me on which lie the error occurs?

Cheers
N

 

[GJP55]

Nikki,

I only get an error message, no debug button. After clicking ok, it takes me back to the code screen (where I was running it from).

Garry

 

[Nikita6003]

Hokan, try this: when you get the error messag, don't click OK but press <Ctrl-Break>

The code might have an error handler which just gives the users a message & then quits running the macro.

Cheers again
N

PS - just to Check: this is the code I'm running:

Sub test()
    Dim db As Database
    Dim qry As QueryDef
    Dim rs As Recordset
    Dim str As String
    Dim i As Integer
    
    Set db = CurrentDb()
    Set qry = db.QueryDefs("qryMktData")
    Set rs = qry.OpenRecordset
    
    For i = 0 To rs.Fields.Count - 1
    str = rs.Fields(i).Name
    Debug.Print str
    Next
End Sub

It's a copy of yours with the extra rs lines added.
This gives me a list of field names in the immediate window in Access.

The qry name (qryMktdata) is one I use on a daily basis to run some checks.

;-) N

 

[GJP55]

Nikki,

Tried your code to extra sure and it fails on

Set rs = qry.OpenRecordset

It is creating a recordset and holding the curser over the rs shows rs = Nothing

 

[GJP55]

Nikki,

Just changed the dim to DAO.Recordset and it now works.

Do you know why this is ?

Garry

 

[Nikita6003]

Was just about to suggest you try that ... Your project probably has a reference to DAO and to ADO - or another Data Access Object model.

Both ADO and DAO have a RecordSet object, but only DAO has Database and QueryDef objects, which is why you weren;t getting an error. But as they both have recordSet Access got stuck.

When in doubt, I stick the DAO in front of all my DAO objects. That ususlly clears it all up

Cheers
Nikki

 

[MichaelRed]

Minimally modified from your original.

Public Function basQryFields(MyQuery As String)

    Dim dbs As DAO.Database
    Dim fld As Field
    Dim qry As QueryDef
    Dim MyStr As String
    Dim Idx As Integer

    Set dbs = CurrentDb()
    Set qry = dbs.QueryDefs(MyQuery)
    'Set fld = qry.Fields

    For Idx = 0 To qry.Fields.Count - 1

        MyStr = qry.Fields(Idx).Name
        Debug.Print MyStr

    Next Idx

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GJP55]

Nikki,

Thanks for the explanation and your help and giving up your time. I really appreciate it.

Cheers
Garry

--------------------------------------------------

Micheal,

Thank you for also helping and providing a solution. You have found solutions for me in the past (another login) to which I am very gratefull.

Cheers

Garry