?
Solved

Passing data types from recordset via automation

Posted on 2006-03-29
7
Medium Priority
?
445 Views
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
 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
0
Comment
Question by:omgang
  • 4
  • 3
7 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 1000 total points
ID: 16351679
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
 
LVL 28

Author Comment

by:omgang
ID: 16362467
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
 
LVL 58

Expert Comment

by:harfang
ID: 16362722
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 28

Author Comment

by:omgang
ID: 16363355
I hadn't thought about trying the same procedure in Access 2k (I'm developing in 2002).

Thanks for your assistance.
OM Gang
0
 
LVL 58

Expert Comment

by:harfang
ID: 16363497
Woot! Your points just made me an Access Genius. Thanks a million ;)
(°v°)
0
 
LVL 28

Author Comment

by:omgang
ID: 16363573
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
 
LVL 58

Expert Comment

by:harfang
ID: 16363631
Lucky you! Given the choice, I'm rather busy in real life than on EE...
(°v°)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question