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
 example

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

Set xlSheet = ExcelApp.ActiveWorkbook.Sheets(1)

With xlSheet
   .PageSetup.LeftMargin = rsSpecs!LeftMargin
(errors)

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
(succeeds)

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.
Thanks,
OM Gang
LVL 28
omgangIT ManagerAsked:
Who is Participating?
 
harfangCommented:
Hello,

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! ;)

Cheers!
(°v°)
0
 
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
0
 
harfangCommented:
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!
(°v°)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
0
 
harfangCommented:
Woot! Your points just made me an Access Genius. Thanks a million ;)
(°v°)
0
 
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
0
 
harfangCommented:
Lucky you! Given the choice, I'm rather busy in real life than on EE...
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.