- 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"
11 2046
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
Its a pop up box that asks me if I want to overwrite the file that is there, which I do everytime.
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.
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.
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 5,501
Expert Mod 4TB
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.
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?
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 32,556
Expert Mod 16PB
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 5,501
Expert Mod 4TB
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 :)
Thank you zmbd for the link
Sign in to post your reply or Sign up for a free account.
Similar topics
by: andrew blah |
last post by:
Hello
I have recently released catchmail - a free (BSD license) open source
Python utility www.users.bigpond.net.au/mysite/catchmail.htm
This script processes in and outbound emails and stores...
|
by: Arthur Connor |
last post by:
Is there a way of extracting the Javascript code from the "normal" HTML code
(e.g. similar to CSS code which can be put into a separate file) ?
If you offer a solution: can I determine in your...
|
by: Christian Dokman |
last post by:
Is it possible to interact between client-side code and ASP.NET
(server-side) code and how is this done? For example, I have a
html-table that shows a column with html-buttons. When I click on a...
|
by: James L. Brown |
last post by:
Hello
I'm currently working on a project, that contains one .net (VB) executable and a C++ (MFC) DLL. Now, what I want to do is to call the functions in the dll out of my managed code
//managed...
|
by: José Pérez Hernández |
last post by:
Hi,
Can i generate unmanaged code from managed code with some aplication ?
I'm not so interested in the source code convertion than i do for the
resulting build.
I can imagine that if an...
|
by: MBS |
last post by:
As far as I know PHP code is always a script which is interpreted by the
PHP interpreter, compiled to machine code, then executed by the CPU. Much
like Java, one could say. (I am not talking...
|
by: Mike Wiseley |
last post by:
I want to insert a procedure call to the report close event of 50 or so
reports in a database. As each report is opened and then closed by a user, I
want to log the date this occurrred. I am...
|
by: joshc |
last post by:
I'm writing some C to be used in an embedded environment and the code
needs to be optimized. I have a question about optimizing compilers in
general. I'm using GCC for the workstation and Diab...
|
by: Alan Silver |
last post by:
Hello,
Newbie here, so please forgive what is probably a basic question ...
I see a lot of discussion about "code behind", which if I have
understood correctly, means that the script code goes...
|
by: mesut |
last post by:
Hi there,
how are you colleagues? I try to set a linkaddress in code behind for
a <asp:hyperlinkserver control. but I think I have some syntax
problem. I don't know how to fix it.
What's...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |