Sendkeys not working anymore (1 Viewer)

[Captain Frog]

Hi
I have a small function that I used many many time to create automatically an error handling whenever I write a new sub or function.
It has been working flawlessly over years, from Access 2 until now. But now, for some reason it does not work anymore.
It uses a form with a text control to copy the template to clipboard.

When I now try to use it, it will fire an error #94 invalid use of null, so I guess it doesn't copy the name of the function as it should. But why?

As I said it worked with access 2, access 10, access 365 until last summer.

Any idea what the reason could be?



Sub CodeTemplate()

'--------------------------------------------------------------

'Purpose :Inser an error trapping code into your sub or function

'Notes lace the caret between the "Function" or "Sub" line and the "End Function" line.

' :and click Makro > CodeTemplate()

' :Needs ZZ_frmTemplate to swap value to the clipboard

'Author :Larroque 06.06.2001

'--------------------------------------------------------------

On Error GoTo Err_CodeTemplate



Dim strMsg As String

Dim strText As String

Dim strName As String

Dim intStart As Integer, intStop As Integer

Dim strType As String



Application.Echo False



SendKeys "{Up 1}" 'place the Sub Name into clipboard

SendKeys "+{End}"

SendKeys "^{INSERT}", True



DoCmd.OpenForm "ZZ_frmTemplate" 'Pass it to the form control

Forms!ZZ_frmTemplate!txtFinal.SetFocus

SendKeys "+{INSERT}", True

SendKeys "{TAB}"

DoEvents 'and to the variable

strName = Forms!ZZ_frmTemplate!txtFinal

DoCmd.Close





If InStr(strName, "Private Sub") Then 'seek the beginning of the sub Name

intStart = InStr(strName, "Private Sub") + 12

intStop = InStr(strName, "(") 'seek the end of the sub Name

strName = Mid$(strName, intStart, intStop - intStart) 'read the name

strType = "Sub"

ElseIf InStr(strName, "Public Sub") Then

intStart = InStr(strName, "Public Sub") + 11

intStop = InStr(strName, "(")

strName = Mid$(strName, intStart, intStop - intStart)

strType = "Sub"

ElseIf InStr(strName, "Sub") Then

intStart = InStr(strName, "Sub") + 4

intStop = InStr(strName, "(")

strName = Mid$(strName, intStart, intStop - intStart)

strType = "Sub"

ElseIf InStr(strName, "Function") Then

intStart = InStr(strName, "Function") + 9

intStop = InStr(strName, "(")

strName = Mid$(strName, intStart, intStop - intStart)

strType = "Function"

End If





strText = "'--------------------------------------------------------------" & vbCrLf

strText = strText & "'Purpose :" & vbCrLf

strText = strText & "'Notes :" & vbCrLf

strText = strText & "'Author : Larroque " & Format(Now, "dd/mm/yyyy") & vbCrLf

strText = strText & "'--------------------------------------------------------------" & vbCrLf

strText = strText & " On Error GoTo Err_" & strName & vbCrLf

strText = strText & " Dim strMsgErr As String" & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf

strText = strText & " Err_" & strName & "_End" & ":" & vbCrLf

strText = strText & " On Error GoTo 0 " & vbCrLf

strText = strText & " Exit " & strType & vbCrLf

strText = strText & " Err_" & strName & ":" & vbCrLf

strText = strText & " strMsgErr = ""Error Information..."" & vbCrLf & vbCrLf" & vbCrLf

strText = strText & " strMsgErr = strMsgErr & """ & strType & ": " & strName & """ & vbCrLf" & vbCrLf

strText = strText & " strMsgErr = strMsgErr & ""Description: "" & Err.Description & vbCrLf" & vbCrLf

strText = strText & " strMsgErr = strMsgErr & ""Error #: "" & Format$(Err.Number) & vbCrLf" & vbCrLf

strText = strText & " Call LogError(strMsgErr)" & vbCrLf

strText = strText & " MsgBox strMsgErr, vbInformation, """ & strName & """ " & vbCrLf

strText = strText & " Resume Err_" & strName & "_End" & vbCrLf





DoCmd.OpenForm "ZZ_frmTemplate"

Forms!ZZ_frmTemplate!txtFinal = strText

Forms!ZZ_frmTemplate!txtFinal.SetFocus

SendKeys "^{INSERT}", True

DoCmd.Close

SendKeys "{DOWN}"

SendKeys "+{INSERT}"

SendKeys "{UP 13}"

SendKeys "{TAB}"

Application.Echo True



Err_CodeTemplate_End:

On Error GoTo 0

Exit Sub

Err_CodeTemplate:

strMsg = "Error Information..." & vbCrLf & vbCrLf

strMsg = strMsg & "Sub: CodeTemplate" & vbCrLf

strMsg = strMsg & "Description: " & Err.Description & vbCrLf

strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf

' Call LogError(strMsg)

MsgBox strMsg, vbInformation, "CodeTemplate"

Resume Err_CodeTemplate_End



End Sub

 

[Uncle Gizmo]

That looks like a very interesting method. I haven't studied it, the only reason I am commenting is because I am looking through the list of unanswered posts and bringing them back to the top so that they get another view. Hopefully someone will be able to provide you with an answer.

However, while I'm here I thought I should mention a slightly different technique I use. I take advantage of a very useful built in ability of MS Access which allows me to keep a compendium of code, easily accessed and pasted straight into a module. Here is a video demonstrating the process:-

3) - OOP's - Creating A Class Step 1 - Nifty Access​

More info on Nifty Access Website here:-

Insert File - Nifty Access

Insert File There's a fantastic, useful, clever feature of MS Access (actually I think it's in all of the VBA products) where you can store code snippets in the normal file structure of Windows. Basically you have a folder which is accessed quickly and easily through the --- >>>Insert >>>File --...

www.niftyaccess.com

 

[Isaac]

You should avoid using SendKeys in any serious application. Precisely because of the heading of this post

 

[Uncle Gizmo]

You should avoid using SendKeys

Yes, that was one of the first "don'ts" I learned!

 

[NauticalGent]

The new Northwind Traders DB Template uses an error handling Class Module that is easy to implement. This will achieve what you are trying to do here and move you away the evil that is "SendKeys"...

 

[Captain Frog]

The new Northwind Traders DB Template uses an error handling Class Module that is easy to implement. This will achieve what you are trying to do here and move you away the evil that is "SendKeys"...

Oh Great I'll have a look immediately. Thanks for the tip!

Oh yeah it is a good idea to handle error in a centralised way, but what I miss is the bit what is most important for later comprehension of the database:

when my code works it automaticaly fills the sub or function with a very usfull header (beside the error handling)

'**************************************************************
' Purpose:
' Inputs:
' Returns:
' Called by:
' Author: Hervé Larroque
' Date: Actual date
' Comment:
'*************************************************************
That I just have to fill like for ex.
'********************************************************************
' Purpose: see if membership has startet in actual year, if so calculate the number of due hours for this year
' Inputs: InYear As Date, ActualYear As Date
' Returns: number of due hours if appropriate
' Called by: rptArbeitsdienst, rptArbeitsdiens-FP and rptArbeitsdientVitrine
' Author: Hervé Larroque
' Date: March 08, 2021
' Comment:
'********************************************************************

I never use sendkey in my datbase (for obvious reasons), but in that case sendkeys is not a functioning part of the DB. It is just a tool I use whenever I write a new sub or function.

 

[Isaac]

I won't lie to you, I did have some things in production that used sendkeys, because the user desperately wanted to control a 3rd-party application that provided me no API (etc) - but we had some long, serious discussions on the risk and we had a user monitoring it who could interrupt it as soon as it got off course, and a unique situation where no harm was done but that was a rare exception of course to my do not use rule.

I think the biggest thing that gets a person is the number of Tabs. If you open your Chrome browser now and start Tabbing, the count of the # of Tabs that it takes you to get to, say, the Ellipsis settings button, will probably be different than the # of tabs that it does me, because of my unique Chrome configuration, set up, add ins, visible bookmarks, 100 possible reasons.

Worse yet, the # of tabs it takes you today probably won't be the # of tabs it takes you a year from now, because of the same reason.

Even within/inside Access, different people's configuration of their screen could cause the # of tabs that it takes to vary from person to person, and from time to time.

Beyond that, I'd say my 2nd biggest hang up was making sure the correct thing / window had the focus. You can go down a long rabbit hole of VBA that alleges to know how to give a window the focus but I spent many months on those and they were iffy at best. Some windows/apps just hate being told to come to the fore.

Trying to keep up with this will be a nightmare at best

 

[NauticalGent]

Oh Great I'll have a look immediately. Thanks for the tip!

You wont be disappointed - and - you are in luck, some of the developers of that template are members here; you will find all the help you need.