Code to replace SendKeys in My Code

[DJRhino]

Option Compare Database
Option Explicit
Dim strSQL As String
Dim rs As DAO.Recordset
'------------------------------------------------------------
' Open_Instructions_Update
'------------------------------------------------------------
Public Function Open_Instructions_Update()
 
    strSQL = "Instructions_Web_Output"
    Set rs = CurrentDb.OpenRecordset(strSQL)
 
    On Error GoTo Proc_err
 
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
 
    While (Not .EOF)
            With CodeContextObject
        If (.Obsolete = True) Then
            ' Operator Instruction
            DoCmd.OpenReport "Inactive Instruction", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal
            SendKeys "{Enter}", False
            DoCmd.OutputTo acOutputReport, "Inactive Instruction", "PDFFormat(*.pdf)", .[Web Output], False, "", , acExportQualityPrint
            DoCmd.Close acReport, "Inactive Instruction"
            DoCmd.GoToRecord acForm, "Instructions 1 Update", acNext
        End If
 
        If (IsNull(Forms![Instructions 1 Update]!Picture) And (Right((Forms![Instructions 1 Update]![Process Part Number]), 3)) = "-OP") Then
            SendKeys "{Enter}", False
            DoCmd.OpenReport "Inspection Instruction Report (Operator) Output", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal
            SendKeys "Y", False
            DoCmd.OutputTo acOutputReport, "Inspection Instruction Report (Operator) Output", "PDFFormat(*.pdf)", .[Web Output], False, "", , acExportQualityPrint
            DoCmd.Close acReport, "Inspection Instruction Report (Operator) Output"
            DoCmd.GoToRecord acForm, "Instructions 1 Update", acNext
        End If
 
        If (IsNull(Forms![Instructions 1 Update]!Picture) And (Right((Forms![Instructions 1 Update]![Process Part Number]), 3)) <> "-OP") Then
            SendKeys "{Enter}", False
            DoCmd.OpenReport "Inspection Instruction Report Output", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal
            SendKeys "Y", False
            DoCmd.OutputTo acOutputReport, "Inspection Instruction Report Output", "PDFFormat(*.pdf)", .[Web Output], False, "", , acExportQualityPrint
            DoCmd.Close acReport, "Inspection Instruction Report Output"
            DoCmd.GoToRecord acForm, "Instructions 1 Update", acNext
        End If
 
        If ((Right((Forms![Instructions 1 Update]![Process Part Number]), 3)) = "-OP") Then
            SendKeys "{Enter}", False
            DoCmd.OpenReport "Inspection Instruction Report (Operator) w/photo Output", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal
            SendKeys "Y", False
            DoCmd.OutputTo acOutputReport, "Inspection Instruction Report (Operator) w/photo Output", "PDFFormat(*.pdf)", .[Web Output], False, "", , acExportQualityPrint
            DoCmd.Close acReport, "Inspection Instruction Report (Operator) w/photo Output"
            DoCmd.GoToRecord acForm, "Instructions 1 Update", acNext
        End If
 
        If ((Right((Forms![Instructions 1 Update]![Process Part Number]), 3)) <> "-OP") Then
            SendKeys "{Enter}", False
            DoCmd.OpenReport "Inspection Instruction Report w/photo Output", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal
            SendKeys "Y", False
            DoCmd.OutputTo acOutputReport, "Inspection Instruction Report w/photo Output", "PDFFormat(*.pdf)", .[Web Output], False, "", , acExportQualityPrint
            DoCmd.Close acReport, "Inspection Instruction Report w/photo Output"
            DoCmd.GoToRecord acForm, "Instructions 1 Update", acNext
        End If
    End With
            .MoveNext
 
         Wend
 
    End If
 
         .Close
End With
exitsub:
    Set rs = Nothing
Exit Function
 
Proc_err:
   Resume Next
 
End Function
 
 

Looking for a good replacement code for the sendkeys part of this code. "SendKeys "Y", False"

"Y" can be replaced by "ENTER"

[jforbes]

What is the message that is popping up that requires the SendKeys?

I do something very similar and do not get a message that requires sendkeys.

Here is an example of some of the code with most of the non-important code stripped out. Maybe it can help determine the cause of the Message:

Public Function generateDrawingBOMPDF(ByRef sDrawingName As String, ByRef sJobNumber As String) As String
 
    Dim sFileName As String
    Dim sReportName As String
    Dim sDateTime As String
    Dim sTempDirectory As String
 
    sDateTime = getStringDateTime()
    sFileName = sDrawingName & "_" & sJobNumber & "_" & sDateTime & ".PDF"
    sReportName = "JobDrawingBOM"
    sTempDirectory = getApplicationDirectory() & "\Temp\"
 
    ' Create BOM
    DoCmd.OpenReport sReportName, acViewPreview, , "JobNumber='" & sJobNumber & "' AND DrawingName='" & sDrawingName & "'"
    DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sTempDirectory & sFileName, True
    DoCmd.Close acReport, sReportName
 
End Function

[DJRhino]

Its a pop up box that asks me if I want to overwrite the file that is there, which I do everytime.

[jforbes]

If you know you are going to overwrite the output, you could delete it, if it exists, before attempting to create the .PDF:

If fileExists(sFileName) Then Kill sFileName

The above will wipeout the file in the sFileName variable without so much as a how do you do, so be careful with the Kill method. The above also uses this cool function by Allen Browne. There are other ways of doing this, but this function works quite well:

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long
 
    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
 
    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
 
    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function
 
Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function
 
Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

The only thing that I don't fully understand with your code is the CodeContextObject. It actually makes me a little nervous.

[DJRhino]

CodeContextObject: I don't understand it either. I took a macro and converted it into VBA using the wizard, and this is how it wrote it for the most part, I did need to make a few changes, like adding code to loop through. Everything works as it stands but I can not use my PC while this is running or it messes up the process which takes a long time in this Database.

With the code you posted, where would I put this in my code? Would I paste it over the sendkeys code? I'm very green when it comes to VBA, but trying to learn.

[jforbes]

This may seem a bit harsh, but it's not in my nature to be mean, so please don't take offense as none is intended...

Being new and green when it comes to Programming is perfectly normal; every programmer has to start with no knowledge. The attitude that will get you past the point of being green is one of Ownership. The code you have in front of you is your code even though you may not have initially written it. Take ownership of that code by understanding each line and what it does, and what all the lines of code together are supposed to accomplish.

Once you take ownership and know all the pieces, how they fit together, and the overall goal of the code, you will know exactly where to place the Kill statement. If you don't take ownership the code and then ask for help with it, your basically asking for someone else to fix your code.

[zmbd]

For context this thread is related to:
home > topics > microsoft access / vba > questions > code worked as macro but not completely as vba
We've already worked thru a lot related to the converted macro>VBA.

In post#6 in the above thread; I've provided the information related to how to check for the existence of the old file ( Allen Browne FileExists() and FolderExists() functions ), and the suggestion about how to delete the file if found ( Kill Statement )

As you can see, jforbes Post#4 has provided basically the exact same solution to the issue as already suggested in code worked as macro but not completely as vba >post#6
This may not the only solution; however, it is one of the more straight forward methods and doesn't involve setting additional library references or "late-binding" for the file-scripting library.

As for CodeContextObject(read more) Normally I've seen this used for error logging. It returns a reference to the object (i.e. a command button on a form or maybe the timer_event of the form - then CodeContextObject=Form (not the Control nor the event) from which you can pull the name ) that called for the execution of the code (either VBA or Macro).

Therefor, I can only guess, that the macro that DJRhino converted to VBA using the wizard might be used in more that one calling macro or form so the wizard converted in the most conservative manner so that all of the objects could execute the code. The conversion wizard isn't always the most elegant coder. :)

DJRhino, you will find that we do not typically write code.

Even my own questions here are often answered with pointers to where to find the information or suggestions about how to approach the task from a different perspective resulting in a lot of "AHHA!" or "Doh!" moments for me; however, this has made me a much stronger database designer and coder.

[DJRhino]

I took what jforbes put for code and made a new module and saved it. If my thinking is correct I would need to do a runcode "Module Name Here") in place of where I the sendkeys at now. Is this the correct thinking?

[jforbes]

The line:

If fileExists(sFileName) Then Kill sFileName

will delete the file specified by sFileName. So you would want to delete the File before you attempt to write to that location.

This might work, but I still don't fully trust CodeContextObject:

' Operator Instruction
If fileExists(.[Web Output]) Then Kill .[Web Output
DoCmd.OpenReport "Inactive Instruction", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal

[NeoPa]

Although I haven't followed the thread perfectly it seems to me that the best advice here, supported even by JForbes who's also given alternative code assistance, is simply to use the code provided to delete the file before you try to write to it. That way the prompt you're working to respond to should never occur.

I know things can sometimes seem complicated - especially when coding is not really your thing, but I'm reading a consistent message from both experts here that are trying to help you. Sometimes we forget that what appears straightforward to us can be complicated for those not so familiar with the territory.

I can only suggest that you give it a try. Everyone here believes it's the easiest and simplest, not to say most reliable, way to go.

[zmbd]

DJRhino - I've forwarded a copy of some resources to your Bytes inbox. There are a couple of links there to VBA tutorials that you might find helpful :)

[DJRhino]

Thank you zmbd for the link