The excess space is because the detail section is pulled down that far. Drag it up to the bottom of the lowest control (the subform in the sample).
You should tweak the form to fit your needs. For the PDF name you don't have to bring those extra fields in, in VBA you can use dLookups to retrieve them from the table. As for the Submit button I think is not needed as the form/subforms are bound so the data gets automatically saved. You could add some validations in the BeforeUpdate event(s) and add msgbox prompt to PDF button and close form from there. One of the validations could be checking a boolean variable boPDFCreated that you set to True after the PDF is created. Note that the PDF report could be automatically attached to a email message addressed to the managers.
Cheers,
Vlad
Sometimes the Run Numbers would not be sequential. If a run has to be aborted during the run, that number is skipped. Since the current method requires the operator to scroll through the Runs via the little nav buttons at the bottom, using tabs across the top might be a good option as well.Presuming they are sequentially numbered, it would be trivial to pull the maximum run number and loop the code that many times. You'd still have to have created the maximum number of subforms. I might put them on tabs instead of stacking them one above the other.
That said, I probably wouldn't go this direction anyway. I might have a "runs" combo next to the date textbox. It would get populated with whatever runs exist when they chose a date (like cascading combos). Changing the combo would change the run displayed below.
Thanks. It was actually in the main form, my laptop was just not showing the bottom of the grid detail area so I could not drag it up. Then I remembered you can set that via the properties box There is still about 3/4" below the Run Summary textbox and before the nav controls for the subform that I can't get rid of. I've got no extra space in the details or footer section of that form.
Thanks. I really should have the lab on the form so they know what lab was used, but it is good to know that the parts to the file name do not have to be actually on the form.
I agree that it is not needed, but for people that are not used to working with Access, it feels odd just leaving a form with new entries in it and not taking some action to save it. That said, I think I'm just going to use the PDF button as a "placebo" for submitting the form.As for the Submit button I think is not needed as the form/subforms are bound so the data gets automatically saved. You could add some validations in the BeforeUpdate event(s) and add msgbox prompt to PDF button and close form from there. One of the validations could be checking a boolean variable boPDFCreated that you set to True after the PDF is created.
This was how I originally wanted to do this, but my manager does not like the thought of the DSR being emailed out without him being able to review it before it is emailed. Never mind the fact that he can review it right on the screen before clicking the PDF button. Old habits die hard I guess.
Just give him another button to "Review" the DSR aka Open the same report in Print Preview.
Cheers,
That would still make you have to handle a dynamic number of tabs and subforms. It would be easy to populate a combo with whatever the existing run numbers were for a given date, and let the user choose which run was displayed below with it.Sometimes the Run Numbers would not be sequential. If a run has to be aborted during the run, that number is skipped. Since the current method requires the operator to scroll through the Runs via the little nav buttons at the bottom, using tabs across the top might be a good option as well.
That might be an option. I'll mess around with that once I get the other tweaks worked out.
If they don't like the nav buttons, I'll dig into making the tabbed version to see if they like that.
I think one subform with multiple records to navigate through (either with the built in bottom ones or some big custom ones on top) is a better option. Borrowing from Paul's suggestion you could replace the navigation buttons with a listbox on top of the screen by the date to show all the runs (and any other pertinent info) and use its AfterUpdate event to do the navigation.
Cheers,
Right now I'm working on changing the output path and file name and am having problems. First, I want to change the path to C:\Users\uptonb\Desktop\Data Base\DSR_Library. I also want to pull some of the record values out of it to use in the file name.
I know that the OutputFile string has to include the full name, including the path. This code (provided by Vlad) currently works to save the file in the same location as the database:
I thought I could change the CurrentProject.Path portion to the full path and it would save in that location, but that does not work:Code:DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)", CurrentProject.Path & "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf", True
I also tried putting parentheses around the path portion, but that was no good either.Code:DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)",
C:\Users\uptonb\Desktop\Data Base\DSR_Library& "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf", True
The full path needs to be a string, so you need to enclose it in double quotes (and you were missing a slash at the end):
If you want the path to be dynamic (to save the report in a Data Base\DSR_Library for each user you will need the following code (save it in a standard module):Code:DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)", "C:\Users\uptonb\Desktop\Data Base\DSR_Library\" & "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf", True
Now you can build your PDF path:Code:Option Compare Database Option Explicit #If VBA7 And Win64 Then 'x64 Declarations Public Declare PtrSafe Function SHGetSpecialFolderLocation _ Lib "shell32" (ByVal hwnd As Long, _ ByVal nFolder As Long, ppidl As Long) As Long Public Declare PtrSafe Function SHGetPathFromIDList _ Lib "shell32" Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, ByVal pszPath As String) As Long Public Declare PtrSafe Sub CoTaskMemFree Lib "ole32" (ByVal pvoid As Long) #Else 'x32 Declaration Public Declare Function SHGetSpecialFolderLocation _ Lib "shell32" (ByVal hwnd As Long, _ ByVal nFolder As Long, ppidl As Long) As Long Public Declare Function SHGetPathFromIDList _ Lib "shell32" Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, ByVal pszPath As String) As Long Public Declare Sub CoTaskMemFree Lib "ole32" (ByVal pvoid As Long) #End If Public Const FOLD_PERSONAL = &H5 Public Const FOLD_DESKTOP = &H10 Public Const MAX_PATH = 260 Public Const NOERROR = 0 Public Function SpecFolder(ByVal lngFolder As Long) As String Dim lngPidlFound As Long Dim lngFolderFound As Long Dim lngPidl As Long Dim strPath As String strPath = Space(MAX_PATH) lngPidlFound = SHGetSpecialFolderLocation(0, lngFolder, lngPidl) If lngPidlFound = NOERROR Then lngFolderFound = SHGetPathFromIDList(lngPidl, strPath) If lngFolderFound Then SpecFolder = Left$(strPath, _ InStr(1, strPath, vbNullChar) - 1) End If End If CoTaskMemFree lngPidl End Function Public Sub MyMkDir(sPath As String) Dim iStart As Integer Dim aDirs As Variant Dim sCurDir As String Dim i As Integer If sPath <> "" Then aDirs = Split(sPath, "\") If Left(sPath, 2) = "\\" Then iStart = 3 Else iStart = 1 End If sCurDir = Left(sPath, InStr(iStart, sPath, "\")) For i = iStart To UBound(aDirs) sCurDir = sCurDir & aDirs(i) & "\" If Dir(sCurDir, vbDirectory) = vbNullString Then MkDir sCurDir End If Next i End If End Sub
Cheers,Code:DIm strPDFFullName as string,strDesktopFolder As String strDesktopFolder = SpecFolder(FOLD_DESKTOP) 'check ir Data Base\DSR_Library subfolder exists and create it if not: If Dir(strDesktopFolder & "\Data Base\DSR_Library", vbDirectory) = "" Then MyMkDir (strDesktopFolder & "\Data Base\DSR_Library") 'now set the full file name strPDFFullName =strDesktopFolder & "\Data Base\DSR_Library\" & "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf" 'finally create the PDF DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)",strPDFFullName , True
The path has to be in quotes, plus I suspect you need a backslash after the last folder:
Code:"C:\Users\uptonb\Desktop\Data Base\DSR_Library\" & ...
Ack! Slow fingers, sorry.
The full path needs to be a string, so you need to enclose it in double quotes (and you were missing a slash at the end):
Code:DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)", "C:\Users\uptonb\Desktop\Data Base\DSR_Library\" & "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf", True
Cheers,Thanks guys. I meant to say I tried placing it in double quotes and not in parentheses in my last post. I believe the issue was the missing \ at the end.
My production database is going to be on a network drive and the PDFs will have to be saved to a network location as well. I tested this save location code on the network drive:
This returns a "Run-Time error '2501' The OutputTo action was canceled". Looking around the net, it appears that this could be caused by network restrictions. However, I wanted to check with you guys to see if anything in that path stood out that might cause that error?Code:DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)", "\\one.mda.mil\Public\TSC\Test Events\DSR Library\" & "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf", True
What are you guy's thoughts on limiting the results of the Execution Date combo? With just one and a half event's data entered, there are over 50 values showing in the combo list. It is nice that they are sorted by day and by Event name, but it is just too much for the operators to have to scroll through to find the date they need to create a DSR for. A year from now and that combo will have hundreds of values they would have to look through.
Would it be better to use two cascading combos right on the DSR_frm, for Event and then Date? Or would it be better to use a separate form to allow them to select the Event and Date and have that form open the DSR_frm? I already do this for a couple of other forms, so the operators are used to that work flow. If I do the latter, I'd want the DSR_frm to be opened with the relevant Run Data already in the subform.
If you type
\\one.mda.mil\Public\TSC\Test Events\DSR Library\
into a Windows Explorer window, can you navigate to that folder? Can you manually add a file there?