MS Access ‘SendKeys’ Error – Office 2000 on Windows 7

Topic

New Reply

I am requesting help in constructing VBA code that replaces “SendKeys” macros that are in my Access 2000 database (running on Windows 7).

By way of background, about 15 years ago I constructed a database in Access 2.0 to manage my contacts (addresses, phone numbers, etc.). I have nine different categories of people I track and have a separate form for each category, which, when opened, alphabetically sorts the category. Since I know little about VBA code, I used mostly macros to make it all work.

I’ve subsequently upgraded the database through Access 97, now on to Access 2000. Most recently I have started running Windows 7 (64-bit) and that’s when I started having the problem I’m requesting help with.

I have buttons that, when clicked, are intended to provide page up/page down commands which advance back and forth through the records. To make this feature work I used a “SendKeys” macro, which no longer is working.

Ever since I’ve upgraded to Windows 7, when I click on these “page up”/”page down” buttons I receive the following message: “The ‘SendKeys’ action requires the Access Utility Add-in too be loaded.” “Re-run Microsoft Access or Microsoft Office Setup to reinstall Microsoft Access and the Microsoft Access Utility Add-in.”

Google produces multiple hits regarding this message, and it appears that using VBA code to replace the SendKeys macro is the consensus answer. However, since I don’t know how to write code, I am stymied in trying to fix the problem and thought I’d turn to the forum members for assistance.

In case it matters, my database does contain some code already, which was provided by CompuServe forum members back when I first constructed the database.

(And “yes” I know I could/should upgrade to a newer version of Access, but economically it would work better for my retirement finances to instead get this older version to work, assuming it’s possible.)

Any assistance is appreciated.

Reply | Quote

Replies

Aha! Another old Compuserve member!

Is each of your forms a single, continuous form (perhaps with your page up/down buttons in either the header or footer)? Or are the records in a subform with your buttons sitting on the main form? Knowing the situation would make it easier to be more specific on the code.

The basic premise would be to use the recordsetclone property of the form/subform that contains the records. When you click the Page Down button (for example), the general logic would be:

1 – set the .bookmark property of the recordsetclone = bookmark of form/subform.
2 – Use a Do/loop to read the next record in the recordsetclone, repeating as many times as you think would represent a page.
3 – set the .bookmark property of the form/subform = bookmark of the recordsetclone.

Reply | Quote

Mark, I feel privileged to have you respond. I have visited your liquorman.net site (“Access tips, links & downloads”) in my attempts to find the answer to this issue. You provide lots of resources to folks and I appreciate your spirit of helpfulness.

My data base is set up so that each person’s record appears on a one-page, single form. The page up and page down commands navigate back and forth from the previous to next record, etc.. (And of course I can also do this just by using the keyboard keys, but it seems handier to click buttons that are on the form. And “yes”, the page up – page down buttons are in the header.

Please know what a complete neophyte I am regarding “code”. When I look at your info regarding the “basic premise”, it is all pretty-much “Greek” to me. It’s certain that I will have to plead for completed code that I could copy directly into my database.

Your expertise, interest and response are greatly appreciated.

BTW, I still lurk on a few CompuServe forums, but things there are just a shadow of what they were back in the late 80’s when I first “discovered” them (was using TAPCIS at the time).

Reply | Quote

“Next Record” button:

me.recordsetclone.bookmark=me.bookmark
me.recordsetclone.movenext
if me.recordsetclone.eof = true then
docmd.beep
else
me.bookmark=me.recordsetclone.bookmark
endif

Reply | Quote

Mark,

I successfully was able to get the “Next Record” code to run and do exactly what I need; however, when I tried to modify the code to do “moveprevious” (for the pagedown button) I received a debug screen that was highlighting the line “me.bookmark=me.recordsetclone.bookmark”.

I tried a seat-of-the=pants “fix” by changing the line “if me.recordsetclone.eof = true then” to read “if me.recordsetclone.bof = true then”, but that just sent the debug highlight to “else”.

Is this an easy fix that I’m not seeing (since I know nothing about VBA code)?

Also, I looked more closely at your web site and see that advice/help on Access is a paid profession for you. My apologies for not noting this earlier. I’m happy to pay you for this expert advice, if that is the preferred way. (If this isn’t the Windows Secrets “forum way”, please don’t be offended by it … I’m a beginner on both this subject and this forum.)

At T.

Reply | Quote

I’m guessing you were sitting at a new record, which is why you got the error. Here is the code for both buttons:

Code:

Private Sub cmdNext_Click()
   If Me.NewRecord = True Then
      DoCmd.Beep
      Exit Sub
   End If
   Me.RecordsetClone.Bookmark = Me.Bookmark
   Me.RecordsetClone.MoveNext
   If Me.RecordsetClone.EOF = True Then
      DoCmd.Beep
   Else
      Me.Bookmark = Me.RecordsetClone.Bookmark
   End If
End Sub

Private Sub cmdPrevious_Click()
   If Me.NewRecord = True Then
      DoCmd.Beep
      Exit Sub
   End If
   Me.RecordsetClone.Bookmark = Me.Bookmark
   Me.RecordsetClone.MovePrevious
   If Me.RecordsetClone.BOF = True Then
      DoCmd.Beep
   Else
      Me.Bookmark = Me.RecordsetClone.Bookmark
   End If
End Sub

Reply | Quote

Mark, thank you for the “SendKeys” code you’ve posted.

Curiously, while it works OK on some of the forms I’ve created, for some others it somehow stops the alphabetizing of the records at the “On Open” event. This is accomplished via a “master query” that is run as part of an OnOpen macro (see below).

I built the database so long ago, I can’t find/remember the method by which the MasterQuery actually does its alphabetizing, however, if I run the query directly from the ‘list of objects’, it still dutifully alphabetizes every one of my records (and in fact as I mentioned, also alphabetizes the records in some of my forms which have the SendKeys code). So the MasterQuery must be OK, but it is somehow being changed/modified by the SendKeys code, at least in some of the forms.

This may be too much information, but here is the contents of the “OnOpen” Macro for my ChristmasForm. Its four Actions are: 1) Maximize; 2) Run Command – the Command is: RemoveFilterSort; 3) Apply Filter – the Filter name is: MasterDataQuery, it uses a Where Condition that is: [ChristmasActive]=(Yes); 4) Set Value – The Item is: [Forms]![ChristmasForm]![FilterWindow], and the Expression is: “All Active Christmas Records”.

Since you aren’t able to see my database, I don’t know if the above is meaningful or not, but I thought I’d try posting it in hopes it is.

As an aside, I saw that Access has a built in ‘Macro Conversion’ feature so I used it to change my OnOpen macro to VBA code. However, the alphabetizing results are the same for the forms that also aren’t alphabetizing with the macro. (For info, the ‘converted’ OnOpen VBA code is below, but I don’t know how to put it into the nice “box” like you use.)

Christmas Form:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err

DoCmd.Maximize
DoCmd.RunCommand acCmdRemoveFilterSort
DoCmd.ApplyFilter “MasterDataQuery”, “[ChristmasActive]=(Yes)”
Forms!ChristmasForm!FilterWindow = “All Active Christmas Records”

Form_Open_Exit:
Exit Sub

Form_Open_Err:
MsgBox Error$
Resume Form_Open_Exit

End Sub

One again, please know how much I appreciate your assistance.

Regards, Al T.

Reply | Quote

(Replying to myself to finalize the thread)

The code which Mark graciously published is now working perfectly for all of my forms.

I was not able to identify any definitive reason why some forms would open with their records in alphabetical order, while some would not. However, I was able to successfully “work around” the issue by adding a final line to my “OnOpen” macro which has an ‘Action’ of “RunCommand”, and the ‘Command’ is selected as “SortAscending”.

This did the trick and all is now working well.

Mark, thank you for your help!

Reply | Quote