How to make one combo box required based on selection in another combo box

[pbaldy]

Also, there is excess blank space at the bottom of the DSR form that I can't seem to figure out where it is coming from. I'd like for the form to stop scrolling when they get to the bottom of it (where I have moved the PDF button to). What could be causing this?

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).

[Gicu]

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

[Etoimos]

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.

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.

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).

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.

[Etoimos]

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.

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.

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.

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.

Note that the PDF report could be automatically attached to a email message addressed to the managers.

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.

[Gicu]

Just give him another button to "Review" the DSR aka Open the same report in Print Preview.

Cheers,

[pbaldy]

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 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.

[Etoimos]

Just give him another button to "Review" the DSR aka Open the same report in Print Preview.

Cheers,

That might be an option. I'll mess around with that once I get the other tweaks worked out.

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.

If they don't like the nav buttons, I'll dig into making the tabbed version to see if they like that.

[Gicu]

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,

[Etoimos]

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:

DoCmd.OutputTo acOutputReport, "DSR_rpt", "PDF Format (*.PDF)", CurrentProject.Path & "DSR_rpt_" & Format(Me.DSRExecutionDate, "YYYY_MM_DD") & ".pdf", True

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:

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

I also tried putting parentheses around the path portion, but that was no good either.

[Gicu]

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):

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

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):

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

Now you can build your PDF path:

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

Cheers,

[pbaldy]

The path has to be in quotes, plus I suspect you need a backslash after the last folder:

"C:\Users\uptonb\Desktop\Data Base\DSR_Library\" & ...

[pbaldy]

Ack! Slow fingers, sorry.

[Etoimos]

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):

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,

The path has to be in quotes, plus I suspect you need a backslash after the last folder:

"C:\Users\uptonb\Desktop\Data Base\DSR_Library\" & ...

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:

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

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?

[Etoimos]

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.

[pbaldy]

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?