Want a procedure to list saved qry fieldnames to immediate window

[MLH]

I use this to list table fieldnames to the debug window.
Can it be modified to perform similar objective for saved
query objects? Would it be best to trash this completely
and write a procedure for queries from scratch?

Private Sub ListFieldsBttn_Click()
'*********************************************
' Prompt user for the table name. List the
' [FieldName]s in the immediate window.
'*********************************************
Dim MyDB As Database, MyData As Recordset
Dim TempRecordset As Recordset, i As Integer
Dim j As Integer, Msg As String, Title As String
Dim Defvalue As String, Answer As String
Dim PString As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Msg = "Enter table name."
Title = "Table Name?"
Defvalue = "Notes4PHPMySQLprgmmr"
Answer = InputBox$(Msg, Title, Defvalue)
If Answer = "" Then Exit Sub
Set MyData = MyDB.OpenRecordset(Answer, DB_OPEN_SNAPSHOT)
Debug.Print
For j = 0 To MyDB.Recordsets.Count - 1
Set TempRecordset = MyDB.Recordsets(j)
HowManyFields = TempRecordset.Fields.Count
If HowManyFields > 99 Then
PString = "There are " & CStr(HowManyFields)
PString = PString & " fields. Remember, the "
PString = PString & "immediate window will only "
PString = PString & "display 99 lines."
MsgBox PString
End If
Debug.Print
Debug.Print
Debug.Print "Fields in "; TempRecordset.Name & ":"
Debug.Print
For i = 0 To TempRecordset.Fields.Count - 1
Debug.Print TempRecordset.Fields(i).Name
Next i
Next j
MyData.Close

End Sub

[pietlinden]

the querydef object has a fields collection too. So yes, you can.
Something along the lines of:

for intCounter = 0 to qdf.Fields.Count
debug.print qdf.fields(intCounter).Name
next intCounter

[Jeremy Wallace]

Did you even try this? Aside from not having one variable declared,
this same code works just fine for a query, too.

This newsgroup is a pretty good resource, but you'll find people are
much more responsive here if you've shown some initiative yourself and
tried to putter around a bit figuring things out yourself. It's best to
try a few things and report on what you've tried and what's worked or
not worked, and what exactly went wrong when things didn't work.

If you'd tried this with a query you'd have gotten exactly the results
you were looking for.

Jeremy

[Phil Latio]

Hi Jeremy

Could you clarify "Aside from not having one variable declared....."
I'm confused to your response, as within the code I can see the
varaibles declared.

Thanks

Phil
Jeremy Wallace wrote:

Did you even try this? Aside from not having one variable declared,
this same code works just fine for a query, too.

This newsgroup is a pretty good resource, but you'll find people are
much more responsive here if you've shown some initiative yourself and
tried to putter around a bit figuring things out yourself. It's best to
try a few things and report on what you've tried and what's worked or
not worked, and what exactly went wrong when things didn't work.

If you'd tried this with a query you'd have gotten exactly the results
you were looking for.

Jeremy

[Jeremy Wallace]

Phil,

Try to compile the code. Does it compile for you? It didn't for me.

Jeremy

[Bob Quintal]

"Jeremy Wallace" <je************@gmail.com> wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

Phil,

Try to compile the code. Does it compile for you? It didn't
for me.

Jeremy

Remove the "option explicit" from the module's header and it
compiles fine.

Note to MLH: put a checkmark to Tools->Options...=>Require Variable
Declaration

--
Bob Quintal

PA is y I've altered my email address.

[Jeremy Wallace]

Yeah, or declare the variable!

[Bob Quintal]

"Jeremy Wallace" <je************@gmail.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:

Yeah, or declare the variable!

I told MLH to fix that problem.
But I responded the way I did because removing the option explicit
allows the code as posted to compile.

Having option explicit is a good debugging technique in that
undeclared variables get caught. In my experience, typos in
variable names is a much larger problem than logic errors..

proper indenting, explicit variable declarations and /1 automatic
conditional closure are very good ways of saving time.

/1 I wish Access did this like Brief did it. Type IF x Then (CR)
and Brief inserted the next line with End If.
Type FOR x = 1 to 2 (CR) and Next x appeared below.

--
Bob Quintal

PA is y I've altered my email address.