Passing data types from recordset via automation

I'm working on an Access application that generates Excel Spreadsheets for the client.  I've setup a table to store spreadsheet layout parameters, i.e.  header/footer text strings, PaperSize, Margins, etc.  The PageSetup.LeftMargin (.RightMargin, etc.) in Excel is of data type Double.  I set my table field data types to be double.  When I attempt to pass the values to Excel by using a reocrdset I get an error

Set rsSpecs = db.OpenRecordset("tblOutputFileSpecs", dbOpenDynaset)

Set xlSheet = ExcelApp.ActiveWorkbook.Sheets(1)

With xlSheet
   .PageSetup.LeftMargin = rsSpecs!LeftMargin

If I add a variable declared as type Double I can get it to work

Dim dblMargin As Double

With xlSheet
   dblMargin = rsSpecs!LeftMargin
   .PageSetup.LeftMargin = dblMargin

Why?  I've run into the same issue with .PageSetup.Zoom - even though acceptable values are between 10 & 400 Excel lists the data type as variant.  I have to assign the value from the table to a variable of type Variant to make it work.

I know I've got it working but I am curious as to why it isn't working the other way.
OM Gang
LVL 28
omgangIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I tried this setup, and it works for me. The default property of a field is its .Value, so that it gets transfered OK.
However, here are some ideas:

* I'm assuming there is a current record and that the value of rsSpecs!LeftMargin isn't Null. Right?
* There could be an implicit conversion. What are your language settings?
* The use of dblMargin as intermediate variable can be simulated by:
   .PageSetup.LeftMargin = CDbl(rsSpecs!LeftMargin)
* Have you tried using .Value explicitely?
   .PageSetup.LeftMargin = rsSpecs!LeftMargin.Value

And, more important:
> (errors)

You leave out the most important piece of information! ;)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omgangIT ManagerAuthor Commented:
harfang, thank you for the response.  Language settings are English(US).

Correct on the existence of the current record and field value is non-Null.

Error 1004
Unable to set the LeftMargin property of the PageSetup class

Using .Value explicity succeeds.

Notice I have specified to open the recordset as dbOpenDynaset.  The source table is linked so it'd open as a dynaset anyway but could this be the problem?  I can't open it as a dbOpenTable since it's linked.

OM Gang
One thing not consistent between versions is the "default property" of the objects. The error message "unable to..." can mean just that. Although your original code works for me on Access 2k, what you really want is the current value from the field, so use .Value.

In this case, there is no difference between dbOpenTable and dbOpenDynaset. The field objects are exactly the same.

Anyway, I'm glad it now works!
Success with your application!
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

omgangIT ManagerAuthor Commented:
I hadn't thought about trying the same procedure in Access 2k (I'm developing in 2002).

Thanks for your assistance.
OM Gang
Woot! Your points just made me an Access Genius. Thanks a million ;)
omgangIT ManagerAuthor Commented:
Congratulations!  I've been so darned busy the last couple of months I haven't been able to pursue Guru level much.  Genius seems like an impossibility.  I don't know how you all do it.

OM Gang
Lucky you! Given the choice, I'm rather busy in real life than on EE...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.