-
'------------------------------------------------------------
-
' Open_Control_Plan_Final_Output_to__Bruce_
-
'
-
'------------------------------------------------------------
-
Function Open_Control_Plan_Final_Output_to__Bruce_()
-
'On Error GoTo Open_Control_Plan_Final_Output_to__Bruce__Err
-
-
With CodeContextObject
-
If (.Part = "ZZStop") Then
-
DoCmd.RunMacro "Rename Back", , ""
-
End If
-
If (.Part = "ZZStop") Then
-
DoCmd.Quit acSave
-
End If
-
If (.Obsolete = True) Then
-
DoCmd.OpenReport "Inactive Report", acViewPreview, "", "", acNormal
-
DoCmd.OutputTo acOutputReport, "Inactive Report", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.OutputTo acOutputReport, "Inactive Report", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output FMEA], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.OutputTo acOutputReport, "Inactive Report", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output PF], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.Close acReport, "Inactive Report"
-
DoCmd.GoToRecord acForm, "Web Bruce", acNext
-
Exit Function
-
End If
-
If (.Obsolete = False And .[DCP Format] = False) Then
-
DoCmd.OpenReport "Final Control Plan", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Final Control Plan", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "Final Control Plan"
-
DoCmd.OpenReport "FMEA", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "FMEA", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output FMEA], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "FMEA"
-
DoCmd.OpenReport "Process Flow Chart", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Process Flow Chart", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output PF], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "Process Flow Chart"
-
DoCmd.GoToRecord acForm, "Web Bruce", acNext
-
Exit Function
-
End If
-
If (.Obsolete = False And .[DCP Format] = True) Then
-
DoCmd.OpenReport "Final Control Plan", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Final Control Plan", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.Close acReport, "Final Control Plan"
-
DoCmd.OpenReport "FMEA", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "FMEA", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output FMEA], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "FMEA"
-
DoCmd.OpenReport "Process Flow Chart", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Process Flow Chart", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output PF], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "Process Flow Chart"
-
DoCmd.GoToRecord acForm, "Web Bruce", acNext
-
Exit Function
-
End If
-
End With
-
-
'Open_Control_Plan_Final_Output_to__Bruce__Exit:
-
' Exit Function
-
-
-
'Open_Control_Plan_Final_Output_to__Bruce__Err:
-
' MsgBox Error$
-
'Resume Open_Control_Plan_Final_Output_to__Bruce__Exit
-
-
End Function
Code works except for it only runs once. I need it to run until all reports for all records are updated, so I think I need to do some sort of loop but not sure how. This was a macro made for this database by someone else and I'm taking over the development of it, everything work as a macro except the sendkeys. I would get a pop up message asking if I wanted to overwrite a file already there, which I always do, but it would not send it for the pop up like it was supposed to.
Thanks in advance for any help.
6 1265 zmbd 5,501
Expert Mod 4TB
Which version of Access/Office are you using? SendKeys have always had glitches, the foremost being the fact that the keystroke is sent to the object having the focus and there's very little one can do to assure that the correct object has the focus.
If you do not want to save the file, then use the standard Kill Statement to remove the old file first then create your new file. WORD OF CAUTION HERE - the Kill Statement is an immediate action, there is no prompt, there are no undos, there is no recovery. The file is "burned."
As for a loop... let us take a quick look here... it takes a few moments to wade thru code. :)
zmbd 5,501
Expert Mod 4TB
I need to clarify a few things:
1) The original script was Access-Macro, NOT Access-VBA - is this correct?
- this is important because VBA script is Not Macro script in Access.
2)
This was a macro made for this database by someone...
Does the original Macro version loop thru all of your reports during record updates?
((2A)) How are these updates being performed? - this might be where the loop should be issued
((2B)) Can we see the original macro script too?
In Access2010 and newer you can open the macro in design view, select any item therein, <ctrl><a> to select all actions, <ctrl><c>, to copy the macro actions as an XML script to the clipboard.
Once you have the actions copied to the clipboard, come back here, in the post reply, <ctrl><v>
Select the entire pasted XML script and then click on the [CODE/] format tool in the post-box-toolbar.
3) If you are updating several records, then the way this script is written, you are going to overwrite the previous file version during each loop unless something in form "Forms![Web Bruce] " referenced control is being set to a new file name. Are you really sure that this is the desired behavior? If you are updating a single record that's one thing; however, if you are looking several records then you may want to rethink when you call the report - logically, it would seem better to call the report after all of the updates are made; however, you have several reports and conditions being tested (a SELECT-CASE construct might be better - or do you need to test for all three conditions (lines 15,27,43) each time?)
4) CodeContextObject is an unusual method of referencing an object... is this code being called by more than one form or form-control?
<> Lines 9 thru 14 can be combined into one logic block - If (.Part = "ZZStop") Then
-
DoCmd.RunMacro "Rename Back", , ""
-
DoCmd.Quit acSave
-
End If
I'm using Access 2013. I just want it to overwrite the file. I converted a macro into VBA using the convert macro to VBA button. To perform this action I click a button on a form. - <?xml version="1.0" encoding="UTF-16" standalone="no"?>
-
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><ConditionalBlock><If><Condition>[Part]="ZZStop"</Condition><Statements><Action Name="RunMacro"><Argument Name="MacroName">Rename Back</Argument></Action></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[Part]="ZZStop"</Condition><Statements><Action Name="QuitAccess"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[Obsolete]=Yes</Condition><Statements><Action Name="OpenReport"><Argument Name="ReportName">Inactive Report</Argument><Argument Name="View">Print Preview</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Inactive Report</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument><Argument Name="Wait">Yes</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Inactive Report</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output FMEA]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument><Argument Name="Wait">Yes</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Inactive Report</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output PF]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument><Argument Name="Wait">Yes</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Inactive Report</Argument></Action><Action Name="GoToRecord"><Argument Name="ObjectType">Form</Argument><Argument Name="ObjectName">Web Bruce</Argument></Action><Action Name="StopMacro"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[Obsolete]=No And [DCP Format]=No</Condition><Statements><Action Name="OpenReport"><Argument Name="ReportName">Final Control Plan</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">[Part Number]=[Forms]![Web Bruce]![Part]</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Final Control Plan</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Final Control Plan</Argument></Action><Action Name="OpenReport"><Argument Name="ReportName">FMEA</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">[Part Number]=[Forms]![Web Bruce]![Part]</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">FMEA</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output FMEA]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">FMEA</Argument></Action><Action Name="OpenReport"><Argument Name="ReportName">Process Flow Chart</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">[Part Number]=[Forms]![Web Bruce]![Part]</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Process Flow Chart</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output PF]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Process Flow Chart</Argument></Action><Action Name="GoToRecord"><Argument Name="ObjectType">Form</Argument><Argument Name="ObjectName">Web Bruce</Argument></Action><Action Name="StopMacro"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>[Obsolete]=No And [DCP Format]=Yes</Condition><Statements><Action Name="OpenReport"><Argument Name="ReportName">Final Control Plan</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">[Part Number]=[Forms]![Web Bruce]![Part]</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Final Control Plan</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument><Argument Name="Wait">Yes</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Final Control Plan</Argument></Action><Action Name="OpenReport"><Argument Name="ReportName">FMEA</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">[Part Number]=[Forms]![Web Bruce]![Part]</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">FMEA</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output FMEA]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">FMEA</Argument></Action><Action Name="OpenReport"><Argument Name="ReportName">Process Flow Chart</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">[Part Number]=[Forms]![Web Bruce]![Part]</Argument></Action><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Process Flow Chart</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">=[Forms]![Web Bruce]![Web Output PF]</Argument></Action><Action Name="SendKeys"><Argument Name="Keystrokes">y</Argument></Action><Action Name="CloseWindow"><Argument Name="ObjectType">Report</Argument><Argument Name="ObjectName">Process Flow Chart</Argument></Action><Action Name="GoToRecord"><Argument Name="ObjectType">Form</Argument><Argument Name="ObjectName">Web Bruce</Argument></Action><Action Name="StopMacro"/></Statements></If></ConditionalBlock></Statements></UserInterfaceMacro></UserInterfaceMacros>
-
The records at this point will already be up to date. Then I need to export the reports to a specific location so they can be viewed when needed. Its fine if they get overwritten all we want is the latest version.
I'm an extreme novice when it comes to VBA, that's why I converted the macro when I couldn't get the sendkeys to work. It just seems that I need this code to loop through until all records have been gone through.
Hope this sheds a little light on what I need.
After a little more research and trial and error I got it.
Here is the code: - '------------------------------------------------------------
-
' Open_Control_Plan_Final_Output_to__Bruce_
-
'------------------------------------------------------------
-
Function Open_Control_Plan_Final_Output_to__Bruce_()
-
-
Dim strSQL As String
-
Dim rs As DAO.Recordset
-
-
strSQL = "Instructions1Update"
-
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 (.Part = "ZZStop") Then
-
DoCmd.RunMacro "Rename Back", , ""
-
End If
-
If (.Part = "ZZStop") Then
-
DoCmd.Quit acSave
-
End If
-
-
If (.Obsolete = True) Then
-
DoCmd.OpenReport "Inactive Report", acViewPreview, "", "", acNormal
-
DoCmd.OutputTo acOutputReport, "Inactive Report", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.OutputTo acOutputReport, "Inactive Report", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output FMEA], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.OutputTo acOutputReport, "Inactive Report", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output PF], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.Close acReport, "Inactive Report"
-
DoCmd.GoToRecord acForm, "Web Bruce", acNext
-
Exit Function
-
End If
-
If (.Obsolete = False And .[DCP Format] = False) Then
-
DoCmd.OpenReport "Final Control Plan", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Final Control Plan", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "Final Control Plan"
-
DoCmd.OpenReport "FMEA", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "FMEA", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output FMEA], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "FMEA"
-
DoCmd.OpenReport "Process Flow Chart", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Process Flow Chart", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output PF], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "Process Flow Chart"
-
DoCmd.GoToRecord acForm, "Web Bruce", acNext
-
Exit Function
-
End If
-
If (.Obsolete = False And .[DCP Format] = True) Then
-
DoCmd.OpenReport "Final Control Plan", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Final Control Plan", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output], False, "", , acExportQualityPrint
-
SendKeys "y", True
-
DoCmd.Close acReport, "Final Control Plan"
-
DoCmd.OpenReport "FMEA", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "FMEA", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output FMEA], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "FMEA"
-
DoCmd.OpenReport "Process Flow Chart", acViewPreview, "", "[Part Number]=[Forms]![Web Bruce]![Part]", acNormal
-
DoCmd.OutputTo acOutputReport, "Process Flow Chart", "PDFFormat(*.pdf)", Forms![Web Bruce]![Web Output PF], False, "", , acExportQualityPrint
-
SendKeys "y", False
-
DoCmd.Close acReport, "Process Flow Chart"
-
DoCmd.GoToRecord acForm, "Web Bruce", acNext
-
Exit Function
-
End If
-
End With
-
.MoveNext
-
-
Wend
-
-
End If
-
-
.Close
-
End With
-
exitsub:
-
Set rs = Nothing
-
Exit Function
-
-
Proc_err:
-
Resume Next
-
-
End Function
-
Thanks for the help.
Still trying to find a good sendkeys replacement that I can understand. As far as overwritting the file on hand, that is exactly wha twe want to do. The info. on the Database is the controlled data that gets updated, so we export the reports out so everyone can access them quickly on out intranet site. If you have a good sendkeys replacement I would love to see it, and to tell me where in my code I would put it. This way I should be able to use my PC while everything exports.
Thanks again.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: WØCBF |
last post by:
I know this code has worked before but now appears to get a compile
error. The code it seems to choke on line 12.
I receive the following message and it highlights the "rst!" statement.
Error...
|
by: Chad Dalton |
last post by:
The code worked perfectly fine in VB6 for converting a decimal length to
binary hex, but in .NET it only works when the iNum variable doesn't
contain a zero. If zero is passed in then I lose...
|
by: _R |
last post by:
I'm working on a project that entails calling into a legacy C DLL.
The original unmanaged test code (ancient C++) works OK, but
after generating managed C++ shells for calling the DLL via C#,...
|
by: Gary Paris |
last post by:
Cor,
I made the changes you suggested by I get an error message saying "Incorrect
syntax near the keyword 'trigger'.
What is wrong?
------------------------------ Last message from...
|
by: vashwath |
last post by:
Hi all,
Below macro moves specified number of bytes from a specified index of
source
string to a specified index of destination string.
#define STRMOV(src_str,src_ind,length,dst_str,dst_ind)\...
|
by: nevergone |
last post by:
Hello Everybody
In <<Modern C++ Design>Compile-Time Assertions
there is :
template <boolstruct CompileTimeChecker
{
CompileTimeChecker(...);
};
template <struct CompileTimeChecker<false{ };
|
by: Radu |
last post by:
Hi. This code worked fine with SQL2005, but has a problem with
SQL2000.... Which one ????
cnSQL = New
SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))...
|
by: solargovind |
last post by:
Hi,
In VBA code, How can i run Sql query..? I need to delete a record by the below query...
Docmd.runsql(Delete * from Payment_detail_Table where Payment_id=(Select Max(Payment_id) from...
|
by: rednek |
last post by:
Parameter QueryDef Code below worked well in Access97 but won't open a Dynaset in AccessXP (2002). I've referenced the DAO 3.6 Library in VB and added the SP6. The params get loaded OK but the...
|
by: Yolly |
last post by:
i have an access database that needs a macro timer to close down the database automatically.run every night. I don't know how to code this macro timer in VBA. Please help!
Yolly
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
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: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
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...
| |