For each Next prob with recordset

[sdh]

Simple I know but I cannot understand why this code gives me grief.

I get a type mismatch on the green line.
rsecordset is open
.eof=false
if in debug I hover over nfield it = nothing
cannot understand it please help.

Public Sub dbtocsv()

'************************************************************
'variables for writing files
'************************************************************
Dim filesystemobject, DocsStream As Object, i As Long

Dim d As String
Dim Nfield As Field
Dim rs As ADODB.Recordset

Dim sqltext As String
Dim nfile As Integer
Dim fname As String
'***********************************************************
'Procedure:
'**********************************************************


'open database
OpenDBShared db_path_name
'create new Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = m_DBConnection.ConnectionString

gsStart 150, frmbptocsv.hwnd
For nfile = 0 To 9

'handling the file selection to create
' by checking if the sqltext is not blank
If query(nfile) <> &quot;&quot; Then

sqltext = query(nfile)'comment(#query(nfile=&quot;Select*From BPFARROW)
fname = Add_Slash(App.path) & filename(nfile) 'filepath
rs.Open sqltext
If rs.BOF And rs.EOF Then 'handling the empty recordsets
exit sub
Else
rs.MoveFirst
End If
'Create a file object for the output file
Set filesystemobject = CreateObject(&quot;Scripting.Filesystemobject&quot
Set DocsStream = filesystemobject.CreateTextFile(fname, True)
Do Until rs.EOF

d = &quot;&quot;
'Loop through the fields in the database

For Each Nfield In rs.Fields()

'If First time no comma needed, otherwise put a comma before the data
If d <> &quot;&quot; Then
d = d + &quot;,&quot;
End If

'Convert the database field to text depending on type and place it after the comma
If Not IsNull(Nfield) Then
If 7 = Nfield.Type Then
If Nfield > 1 Then
'a date
d = d + Format(Nfield, &quot;dd/mm/yyyy&quot
Else
'a time
d = d + Format(Nfield, &quot;hh:mm&quot
End If
ElseIf 5 = Nfield.Type Or 2 = Nfield.Type Or 4 = Nfield.Type Then
'Numeric
d = d + str(Nfield)
ElseIf 11 = Nfield.Type Then
'Boolean
If Nfield Then
d = d + &quot;True&quot;
Else
d = d + &quot;False&quot;
End If

Else
d = d + Nfield
End If
End If
end if

Next Nfield

'Write the record to the file
DocsStream.WriteLine (d)
rs.MoveNext
Loop

DocsStream.Close

End If
rs.Close

gsTick
Next nfile

gsStop
Unload Me

end sub
:

 

[MichaelRed]

For Each Nfield In rs.Fields()

I use:

For Idx = 0 to rs.Fields.Count - 1

To use your construct, I would expect to see:


For Each Nfield In rs.Fields.Count





MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[sdh]

thanks for your reply

I have fixed it.

you can use the construct

For each nfield in rs.fields

however I was referencing adox and adodb and forgot
to declare nfield as adodb.field
so it thought it was adox.field and wouldn't work.
thanks
again
sdh

 

[harmmeijer]

Well sdh, I use a code like yours:
for each myFieldVar in rsMyRecordSet.fields
debug.print myFieldVar.name
next

Can you stop the code just before the For Each Nfield In rs.Fields() line and see the type of the Nfield var?

The value will be nothing because the for each line is not executed yet.