Replies

A while back I wanted to do exactly what you’re describing. I poked around in the Adobe documentation describing their Object Model (hoping to find something for their bookmarks “controllable” from Access via VBA automation). No such luck. As I remember, the documentation explicitly stated that Bookmarks were not accessible via this route. Maybe there’s some other “back-door” solution to your problem. I’ll be watching here to see what materializes!

Yes, I went in with programming experience in other languages so I may have had a head start. Nonetheless, I remember the book as not leaving too much to prior experience. Your best bet is to look at the books (as you plan to do), but I suggest you look closely at how the book progresses from chapter to chapter (avoid the “reference” format and look for the “tutorial” format). See if Chapter 1 is comprehensible or all Greek. If Greek, then maybe a step back to a beginning book on Visual Basic might be in order (forget the “Access” and “for Applications” piece until you get familiar with the fundamentals). There are a lot more of just Visual Basic books out there, so it’s more likely there’s one that fits your experience level, is well-organized, easy-to-follow, and all that good stuff.

Good luck.

You’re right, Claus. As long as the form has a control (combo box or list box) where the class is selected (and ClassID is the bound value), as is the case with your sample db. (Randall, it seems like you need to have this class-selection control on your form, right?)

I was giving Randall a possible patch to his existing code.

Randall,

I haven’t checked out Claus’ solution, but I think you’re pretty close with yours.

As Claus points out, you are assigning your SQL string to rst!ClassID. What you want to do is evaluate the SQL query and assign the result of the query to rst!ClassID. To do this you can used CreateQueryDef():

rst!ClassID = CurrentDb().CreateQueryDef(“”,””).OpenRecordset!ClassID

Alternatively, you could use the DLookup function:

rst!ClassID = DLookup(“ClassID”, “qryClasses”, “ClassDate = ‘” & Me!lstClassDate & “‘ AND ClassName = ‘” & Me!cboClassName & “‘”)

The three arguments of DLookup() are the SELECT, FROM, and WHERE clauses (without the keywords and closing semicolon), respectively.

Hope this helps.

Neil,

Glad you got it figured out. Sorry I didn’t check back sooner to see if you needed more help. Obviously there’s lots of help here in the Lounge, ready and waiting! I knew I had left lots of gaps in my suggestion — sometimes you learn more by figuring things out rather than following a recipe, right? Frustrating at times, sure, but more satisfying in the end.

You might try Rory’s suggestion for “populating” your list box. Rather clever, I think. It keeps the list up to date as you add or remove reports or change report names, and it doesn’t require a separate table. I was trying to figure out a way of doing that but was unsuccessful. If you want to exclude certain reports (e.g., subreports as Rory mentions or other reports used for other purposes), just name your reports in a clever way. For example, begin the names of the ones you want to include with something like “List”, then add the following filter to Rory’s query:

SELECT Name from MSysObjects WHERE Type = -32764 AND Left(Name,4)=”List”;

Good luck.

Here’s a basic outline of one approach (reply back if you need more detail):

1. Create a table which includes your report names (this table could be populated by inspecting the CurrentDb().Containers(“Forms”).Documents collection — if you’re familiar with VBA and navigating the object model — otherwise, just fill in the table by hand).

2. Create a form that uses this table as its Record Source. Include a text control that references the table’s report name field.

3. On this form create a “Preview” button. As its OnClick event, specify the following command:

DoCmd.OpenReport Me!txtReportName, acViewPreview

(txtReportName is the Name for the text control on the form that displays the selected report name).

4. Create a similar “Print” button with a simiar OnClick event, but specify acViewNormal instead of acViewPreview

I think something like that would get you what you want. I’ve left out the gory details. If you need them, holler.

I, too, was using Access 97 when I determined that I needed to learn VBA. I used “Microsoft Access 97 / Visual Basic Step-by-Step” (http://www.microsoft.com/MSPress/books/338.asp) which I found very nicely organized and targeted towards the VBA novice (particularly if you’re already familiar with Access w/o VBA). I’d suggest forcing yourself to work all the way through it from start to finish — as the title implies, it’s designed that way; as a hands-on (in front of your computer) tutorial. It does have some mistakes, but none of them are too frustrating. (As my first VBA “project” I actually created a database to log the book’s errata and sent it in to Microsoft Press — I’ll post it here if anyone’s interested.)

The author has published an Access 2000 edition (http://www.microsoft.com/mspress/books/2533.asp), but I haven’t looked at it.

There may be better alternatives out there, but this one worked very nicely for me.

Here

Peter,

If you’re using a macro for the On Click event for your Preview Report button on your form, then you just need to add a second action to that macro (after your OpenReport action). Select RunCommand as the action and Zoom100% as the command (it’s in the command drop-down list).

Alternatively, if you’re using VBA code (by selecting “Event Procedure”) as the On Click event handler, then include the following code for your cmdButton_Click() procedure:

DoCmd.OpenReport “ReportName”, acViewPreview
DoCmd.RunCommand acCmdZoom100

Hope this helps. Holler if you need more detail.

Include the following line after your DoCmd.OpenReport statement:

DoCmd.RunCommand acCmdZoom100

Note that this will not work if you try to include it in your On Open event for the report. Docmd.Maximize does work either place… go figure.

One option is to open the table you just created in Design View, Insert a new field, name it PrimaryID (or whatever), with an AutoNumber data type. Then right click on this row of the design grid and select “Primary Key”.

If your “Customers” table has a AutoNumber PrimaryID field already, include that in your Make Table query, too. Then all you’ll have to do is identify it as the Primary Key in the new table (as described above).

I don’t know of identifying the Primary Key as part of the Make Table Query. Anyone?

Yes, when you run MSQuery from Excel (Tools…Get External Data) and the Wizard asks for the source database and you select your Access mdb file (the one with the UNION query), that query will appear in the list of “tables” you can retrieve.

When you use Tools…Get External Data to run MSQuery and import the data the first time, Excel creates a QueryTable object that maintains the linkage to the Access database, so that when your Access data changes, you just need to select Data…Refresh Data to update your Excel table.

You can also set a refresh interval for the QueryTable object so that the refresh occurs automatically and periodically. Select Data…Get External Data…Data Range Properties for the various options (these menus are from Excel 2000; Excel 97 may be slightly different?). Of course, you may already be familiar with these options since you’ve queried from Excel before…

Here’s a possibility to get you started:

In your yyProj.mdb database (which has link tables to the table(s) of interest in each of your mmmyyProj.mdb databases, right?) create a UNION query that “glues” all these tables together. The syntax would be something like:

SELECT * FROM [jan02Proj]
UNION SELECT * FROM [feb02Proj]
UNION …
…
UNION SELECT * FROM [dec02Proj];

where jan02Proj, etc. are the linked table names (not necessarily the mdb file names.

As far as I know, UNION queries can’t be created using the design grid; you have to type the SQL code directly into the SQL window.

The result of this query can be exported to Excel using Office Links or via VBA code.

Yes you can do all of this from Excel VBA. I’ll follow up with some guidance for this in a later post (don’t have time now), if somebody doesn’t beat me to it.

By the way, I had some “flakeness” problems with Excel 97 – Access 97 automation (e.g., running Access from Excel and vise versa). Generally it worked okay but occasionally I got “automation errors” seemingly out of the blue. Just a forewarning. Office 2000 seems to behave much better in this regard.

Good luck.

Thank you all for your quick feeback!

It appears that “the solution” is placing a text box in the landscape page’s header, sizing it to look like a portrait header, and rotating its text 90

The date data type is a 64-bit floating point number (same as the ‘double’ type). When you subtract two dates/times resulting in a negative time you get a negative time. For example, try this in the VBA debug window:

?Cdbl(#10/16/01 8:00# – #10/17/01 8:00#)

You get a result of -1 (i.e., 1 day).

Retaining this ‘double’ data type (using either the ‘Double’ or the ‘Date’ data type) will let you deal with the math without all the text conversions. When you get to the point you want to display the value, use something like:

iif(dteGain<0,"-","")&format(abs(dteGain),"hh:mm")

(I’m not sure how you want to display the gain — the above will have problems if the abs(dteGain) > 1; i.e., > 24 hours.)

Hope this helps a little.