Passing a recordset 7

[SpiritOfLennon]

Hi,
I've written a subroutine in a module that generates Excel output. I can pass a SQL string to it and run the query to populate the spreadsheet, however this seems inefficient as I'm already running the query to populate a flexgrid. So I tried to set it up so that I could just pass it the recordset. Here's what I have..

Private Sub Command2_Click()
MousePointer = 11
Excel_Export (rs1)
MousePointer = 1
End Sub

The subroutine I have defined is like this

Public Sub Excel_Export(rs As ADODB.Recordset)

Dim xlApp As excel.Application
Dim xlBook As excel.Workbook
Dim xlSheet As excel.Worksheet
Dim vcnt As Integer
Dim vcol_num As Integer
Dim Vrng_end As String
Dim Vrng_end2 As String

'Run_Query (strSQL)
If rs.RecordCount > 0 Then
Set xlApp = CreateObject("Excel.Application&quot
Set xlBook = xlApp.Workbooks.Add

etc...

When I hit the button I get
Run-Time error 13
type mismatch

Any ideas?

SOL
I'm only guessing but my guess work generally works for me.

 

[nicsin]

hi SpiritOfLennon,

instead of Excel_Export (rs1) write Excel_Export rs1 or call Excel_Export(rs1)

nicsin

 

[SpiritOfLennon]

Hi nicsin,
That sorted that problem out, I now have another problem but I'll wrestle with it myself for a while.
Sorry to be dumb, VB isn't my bag, but what's the difference between using call excel_export and just excel_export?
Thanks.

SOL
I'm only guessing but my guess work generally works for me.

 

[LazyMe]

It's something VB specific:

When you call a method without expecting a return value, you can't use them "()" surrounding the arguments passed.

Call does absolutely nothing special. However, in this case the "()" are not forbidden to be used....

Greetings,
Rick

 

[nicsin]

Have a look at thread222-711289. strongm has given some very representative examples.

 

[SpiritOfLennon]

Thanks nicsin, still not 100% sure why this caused my error but it's useful to know.



SOL
I'm only guessing but my guess work generally works for me.

 

[CCLINT]

>still not 100% sure why this caused my error but it's useful to know.

It is because a recordset's default collection is the Fields Collection.

When you do this

SomeFunction (rs1)

the () causes that what is in between the () to be evaluated first, which in th eabove case evaluates to a fields collection, the default for a recordset object.
The argument in the proceedure asks for a recordset:

Debug.Print TypeName((rs1))

returns "Fields"

You are trying to pass:
rs1.Fields

So, a fields collection is not a recordset object, thus the type mismatch.



This is not the same as

Call Excel_Export(rs1)

which is the same as

Excel_Export rs1

With these two you are passing the object itself, with-out being first evaluated.

 

[zemp]

CCLINT, very well written explanation. I have never heard it explained that well and straignt forward before. Thanks.

A star for you.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[CCLINT]

Thank you zemp!

Here's a treat:

PassRecord myRS.Fields
or
PassRecord (myRs)

Private Sub PassRecord(Record As ADODB.Fields)
Debug.Print Record.item("SomeField&quot.Value
Debug.Print Record.item("SomeField&quot.Type
End Sub

Now you have passed "one single record" (By Reference) to a proceedure and not the whole recordset, and can use most of the Field methods and properties.

You just cannot use the Field's collection methods except Count, Item, Refresh, Resync, and ,if you are using a fabricated recordset, Append and Delete. But, as said, you can use most of the Field methods and properties.

The Update/CancelUpdate are used elsewhere (if provider supported) so you will need to update the record using the recordset's Update method.

I just thought someone may find it to be useful sometimes...

 

[cyberbiker]

CCLINT
Are you a genius? or just brilliant.
This may prove quite useful for what I am working on.
thanks

Terry (cyberbiker)

 

[Sideman]

cyberbiker,

I think CCLINT may very well be a genius AND brilliant...BUT he's a mind reader for SURE!!

I needed this tip and didn't realize it till I read this thread!! So weird.

Thanks, CCLINT.
...oh, and a star.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[SpiritOfLennon]

Thanks CCLINT, that's pretty clear now.

SOL
I'm only guessing but my guess work generally works for me.