We help IT Professionals succeed at work.

Passing data types from recordset via automation

omgang asked
Medium Priority
Last Modified: 2006-11-18
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
Watch Question


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


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
omgangIT Manager


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!
omgangIT Manager


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 Manager


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...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.