Replies

bgarth — If you look at post 639613 (sorry, Hans, you posted instructions on how to make this a link but w/ a quick search I can’t find it — I’ll look further, later) —
This post was Han’s in response to your post created in March of this year (07), with a similar question.

Another thought — probably not a real “safe” one, is — I wonder what results would happen if you associated the old extension with the new program? I wouldn’t try that unless it was your last hope, because I have found it mildly difficult to unassociate.

Pat

Thank you! I know Access can be forgiving, but I have been burnt before in another language or two — so, now I avoid ’em! Thanks, Jezza.
Pat

This is intriguing, because I have a couple of people who can’t click on their hyperlinks and get anything to happen, whereas if I do, they work. I probably have an earlier version of the reader than they do — because of your email, I’m checking with them.
thx
Pat

Just as a “pendantic” aside, and obviously not tripping you up here, but, I wonder if SQL is a reserved word, and something you might avoid from using as a variable?
thx
Pat

Thank you, Hans! I so often just cut ‘n paste, and have used the better form before but just forget and revert to the sendkeys format. It’s nice to be praised by you, my hero!
Pat

Besides Han’s suggestion of the scrollbars, what I like to do, especially if the text boxes are small on a crowded form, is “double-click to zoom:”
Private Sub fSomeNotes_DblClick(Cancel As Integer)
SendKeys “+{F2}”
Cancel = True
End Sub
In conjunction to that, you can set your cursor so that be positioned at the beginning so that the zoom doesn’t select the whole field.
I _think_ my users like it!
thx
Pat

Well, you can do everything within Access, but you would have to include the Word library as a reference, in order to get all the Excel functions and run them while within Access. The project I was looking at for the code actually was entirely in Excel, and I got my recordset from a connection to a SQL server. Two other projects are merge files from Access, and neither actually involve a recordset, although I don’t see why one cant’ be sent. Both call a macro in the word merge document. In one I sent a SQL string — here is my call in my Access project: “appword.Application.Run (“GoMrg”), sqlStmt.” You could do it any number of ways.
thx

It is in Excel. You can send — well, you know, I’ve done a couple of different things lately, and I don’t know if I actually passed the record set from Access to Excel as a parameter, but I know that an Excel function can receive a recordset, so I don’t see why not. Let me check what I did in a couple of instances, and I’ll return shortly …
thx
Other Pat

Can you send a DAO recordset to the Excel spreadsheet? If you do it that way, you can capture the right-most column by virtue of the recordset.fields.count, and then do the horrid RC referencing and formula pasting after that for summaries (which you can tell I’m not comfortable with, but did kludge together something that I would paste up to you if you wanted in addition to this). Here’s my code that just pastes a recordset sent from Access in the call to Excel. It first populates the column headers from the field names:

Dim icols As Integer
Rows(“2:2”).Select ‘and do formatting to this row for the headers, if you like
For icols = 0 To incomingSet.Fields.Count – 1
Cells(2, icols + 1).Value = incomingSet.Fields(icols).Name
Next
Range(“A3”).CopyFromRecordset incomingSet ‘voila

thx
Other Pat

Rachel, you might also take a look at a recent Access Watch newsletter by Helen Feddema, found of of Woody’s Lounge Main Portal. The number is “AW #9.09,” titled “Mail Merge in Access 2007.” It’s not specific to 2007, that is, I adapted the idea for my XP. I had already done a couple of merges (thanks to help from Hans, here, about the same “:=” syntax you needed) but found Helen’s recommendation to go to a text file to merge to the Word document to be a simpler, more flexible, and less trouble-bound. Check out that article if you have the time, I think you’ll find it helpful, even if you don’t use any of the code.
thx
Pat

THANK YOU, HANS !
I’m embarassed to say that I removed the “:” when some earlier code didn’t work. I’m also embarassed to show what little I know about Excel VB, that I basically had a “new” and a “create.” Thank you so much! It worked perfectly.
Pat W.

Thanks, Mark.
Yes, there is an index on that field, and that seems to be working pretty fast, but — I have another field that I am using and I am timing out on that. So, I second your sentiment about indexing!
Thanks for responding,
Pat

Hans, hoping this will work for Rudi. The “environ()” used to return such useful information consistently, and probably still does but at one point, either after Windows 98 or Windows XP, it didn’t seem to work any more — not just in Access, but in a couple of other programs that were using something similar to the “environ()” function. In VB, we shifted over to that API call, the “getusername” (I think) which you and others have so kindly posted here at times. That’s been very dependable, whereas the “environ()” may have lost some of it’s “charm.”

Thanks for all you do, Hans.

Pat

We had a very similar problem. It turns out that the recordset that the form was based upon was being invoked up to four times by other operations that got triggered within the form. Also, there was some circular coding, where the “on activate” event was being called over and over. This poor coding didn’t seem to bite badly when the database was in ’97, but, as soon as we converted it up to XP, the problem became chronic.

The solution was to try to streamline the code, and issue record “saves” prior to bopping out to another form that used the same recordset.

thx
Pat

One thing that I’ve used just today, in fact: when I find that I have query permutations upon query permutations, and the query becomes (necessarily) un-updateable, there is one easy fix I’ve found: select the table that you want to update, and do a sub-select on one of your key fields to see if that key field exists withing the vastly permuted criteria query — well, this may not work if you’re working on the same data, but, give it a try. In other words, say I want to update a field in “Plain” table, look for “Plain.ID in (Select subPlainID from VastlyPermutedQuery)”