Replies

To add some to Patt’s reply, see the following Microsoft KB article. Ignore the “ACC2000” in the title, as this applies to all versions of Access:

ACC2000: Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/?id=209778

I apologize for the wording of the original subject line. I can see how it would be confusing. Thanks for your answers. It’s good (and bad) to know it can be done. I guess I’m off to delve into OLE Automation and VBA now.

You can export to Excel without using any automation code, if your needs are fairly simple. In fact, you can even export data using a macro (no VBA code required). However, if your needs include preparing a fully formatted Excel workbook, then yes, you will need to use automation code.

I don’t think that the idea is for the new information to be added to the database. I think the idea is to create an Excel spreadsheet, Word document or PDF file that allows someone else in another location to fill it in electronically and print out.

Were you aware that you posted your question in a databases group? Certainly you can create an Excel spreadsheet or Word document, fill in part of the information, and have someone else fill out the rest and print. With the right software, you can even use the .pdf file format.

Thanks. I’ve been hunting around for that setting, but had not found it on my own.

Tom

The samples I posted use an unbound main form with a bound subform. Then, when you double-click on a record in the subform, you open a bound form to just that one record selected. So, I’m still enjoying the ease of development using bound forms, but, if the bound form would normally be tied to a large recordset, then I use the Unbound QBF form to help find the appropriate record to open. Does that make sense?

Question for you as a moderator:
I’m new to this group. Is there a way to set up email notifications? If so, can it be done without revealing one’s real email address?

Thanks,

Tom Wickerath
Microsoft Access MVP
2006 – 2011

If both users are on the same Local Area Network, then you just need to create a Multi-user Access application, where each user fills in their portions of the required data.

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

If these users are at separate locations (a Wide Area Network is involved), then you probably should not consider using a JET database.

Tom Wickerath
Microsoft Access MVP
2006 – 2011

Hi MacroAlan,

I have a sample database that is based on code provided by Access MVP Dirk Goldgar, which shows how to easily create a temporary work database using a “template” table, or any SELECT query (including crosstab queries). There’s only one little restriction that involves the VBA editor option needs to be set to Break on Unhandled Errors, as opposed to Break on all Errors. If you distribute your application in the compiled .mde (.accde) form, then you need not worry about how this VBA option is set on a user’s machine. The sample is located here:

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip

Tom Wickerath
MS Access MVP
2006 – 2011

Hi Marty,

> Tom, regarding late binding of the references, are you refering to an approach where the db is compiled with out them…

Yes. However, if your code has not been converted to use late binding, and there is a dependency on a checked reference, then any attempt to compile the code will fail.

Note: If you can remove a reference, and your code compiles okay, then you did not need that reference, period. It is ALWAYS best to starve the references listing. In other words, do not allow a reference to remain checked unless it is absolutely necessary for your code to compile.

> …and, upon the FE’s starup, the references are set?

No. Late bound code does not require that any references be set. Here is a sample file that you are welcome to download from my web site, which uses late bound (no reference required) code to automate Excel:

Automation with Late Binding (Calling Excel’s NormInv function)
http://www.accessmvp.com/TWickerath/downloads/Statistics.zip

Tom Wickerath
Microsoft Access MVP
2006 – 2011

Is there some good reason for using an unbound form with unbound controls? It would be much simpler if the form and controls were bound.

You can then just use the combo box wizard to add an unbound Combo Box, and choose to Find a Record, and the wizard will write the simple bit of code needed.

Hi John,

Using a bound form is really only appropriate for fairly small databases, or if the database is located entirely on a user’s local hard drive (no network involved), because you end up pulling more records over the network wire than are usually necessary. The Golden Rule for good performance of a multi-user Access application [or any application, for that matter] is to retrieve only the data needed. The same logic applies to a web page or other application that retrieves data from SQL Server, Oracle or whatever database you are using.

For more information, check out my Multi-user Applications paper:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Tom Wickerath
Microsoft Access MVP

Hi Tom,

You can simplify the “Sort” field, where you have used the Switch function to print the name of the month by using the built-in MonthName function. For example, in query design view, enter the following expression in the Field row:

Sort: MonthName(Month([WeddingAnniversary]))

Notes:
I would think that you’d want a numeric field as a sort result, ie: Sort: Month([WeddingAnniversary])
I would avoid aliasing any columns with reserved words. Both Month and Day are considered reserved words in Access.

If, however, the user selects March from sometime in February, the results in anniversary years are 1 less than they should be.

It appears as if you want to round the result up to the nearest whole number, in years. So, should 9.5 years round up to 10 but 9.49 years round down to 9 years? If so, I think the following expression should do the trick:

WedAnnvYears: Round((DateDiff(“d”,[WeddingAnniversary],Now())+Int(Format(Now(),”mmdd”)<Format([WeddingAnniversary],"mmdd")))/364.25+0.000001,0)

In the above expression, I'm calculating an age in days, and then converting to years. Finally, I add a very small number, 0.000001 to the result and apply the Round function, rounding to zero decimal places.

Tom Wickerath
MS Access MVP
2006 – 2011

Hi Marty,

Wouldn’t it be better to use late binding (no checked reference required) instead of early binding (requires checked references)? I typically develope code using early binding, so that I get the benefit of Intellisense. After the code is debugged, I convert to using late binding. Although I’ve never done any work in 64-bit environments, I suspect that late binding would be useful here as well. There are methods available to use conditional branching in VBA code, depending on 32-bit vs. 64-bit. Try searching the Access Blog at Microsoft–I’m pretty sure they have posted on this topic.

Tom Wickerath
Microsoft Access MVP
2006 – 2011

Hello –

First, a quick word about the declaration of the recordset variable….I recommend that you always use an explict declaration for the type of recordset (DAO or ADO) that you want to use. Here is an article I wrote that discusses this in more detail:

ADO and DAO Library References in Access Databases
http://www.accessmvp.com/TWickerath/articles/adodao.htm

I have some Query by Form examples available for download, which you are welcome to have a look at. These samples increase in complexity, with the Custom Dialog Box sample being the easiest, followed by the Elements sample (demonstrates how to iterate a multiselect listbox). The Chap08QBF sample came from chapter 8 of the book “Access 2000 Power Programming”, written by F. Scott Barker, with a few enhancements that I added:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
http://www.accessmvp.com/TWickerath/downloads/elements.zip
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

http://www.seattleaccess.org/downloads.htm
See the download “Query By Form”
Tom Wickerath, February 12, 2008

To help you get started with VBA programming, you might want to have a look first at this download on the Seattle Access site:

DAO – Back To Basics Compilation/Demo by Tom Wickerath, Jan/Feb 2007


Tom Wickerath
Microsoft Access MVP

after inserting and deleting the field in front of the short desc field (which happens to be the last field in the sheet), i was able to link properly to the field. i had our staff run the exported data which populates the table (they have to do this daily) today and we’re back to square one. the table doesn’t recognize the field again. i dont’ want to have to insert another manual step unless i absoultely have to.

i just don’t understand why it won’t recognize the last column of data automatically.

Hi –
You haven’t mentioned which product and version that you are using…If you are using Microsoft Access, the problem might be related to your choice of field name. Note that these three words are considered Reserved words:

Short
Description
Desc

So, a combination of reserved words that includes a space character, such as “short description” or “short desc” might be enough to confuse the situation, whereas I would expect that “ShortDescription” or “ShortDesc” should be okay. For a complete list of reserved words in Microsoft Access, see Access MVP Allen Browne’s listing:

Problem Names and Reserved Words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

Tom Wickerath
Microsoft Access MVP

Access MVP Armen Stein has a very good relinker available for free. One of the features allows one to easily relink to a BE file that is in the same folder as the FE.
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

The download is named “J Street Access Relinker”.

> Lookup fields are a ruse to show you something other than what is really there.

I agree (and so do many of my MVP friends). Have a look at the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom