Using a Value in an Export (1 Viewer)

[alexfwalker81]

I feel like this might not be possible, but how can I use a value from the query to create a dynamic file name where I've indicated [VARIABLE HERE] in the image below?

The function makedir() will have created the dynamic folder name, ready to be used.

 

[MajP]

FYI. Most people here do not use macros and opt for VBA. If you want to do this in VBA then it is a pretty common thing to do. Maybe someone who works with macros can help.

 

[theDBguy]

Hi. How do you plan on getting the value to use into the variable? Just curious...

 

[The_Doc_Man]

After doing a web search and recalling my own attempts to document macros with an analyzer I once wrote, I have to say I have my doubts. The trick is finding the text of the macro and identifying the line you wanted to edit. I've seen the question asked maybe a dozen times on multiple sites that used VBA behind the main product (Access, Excel, and Word being the ones I saw most commonly) and the consensus was "convert the macro to code, at which point the problem becomes much easier."

It is possible that my search missed an article that contains the answer, but I tried several variants of the question before giving up.

 

[alexfwalker81]

It's fine, I can do it in VBA. I was just being a bit lazy really, and hoping there was a feature in that interface that I could take advantage of. No worries, I'll do it the long way!

 

[alexfwalker81]

Hi. How do you plan on getting the value to use into the variable? Just curious...

That's exactly why I was thinking it wasn't going to be possible, but just wondered.

 

[theDBguy]

That's exactly why I was thinking it wasn't going to be possible, but just wondered.

Actually, I think it is very possible. I just needed to know where the value was coming from.

For example, will the user input it in a box or a form?

 

[alexfwalker81]

Actually, I think it is very possible. I just needed to know where the value was coming from.

Ah, ok - it's [pick_no] in the qry_to_print_BOUN

 

[theDBguy]

Ah, ok - it's [pick_no] in the qry_to_print_BOUN

In that case, maybe you could use a DLookup() action. You could either use DLookup() directly in the file path or create a LocalVar for the DLookup() result and then use the LocalVar in the file path argument.

 

[Pat Hartman]

Here's a vba example.

Private Sub cmdEmail_Click()
    Dim strpath As Variant
    Dim DocName As String
    Dim strSubject As String
    Dim strSuffix As String
    On Error GoTo ErrProc:

    If IsNull(Me.txtPicklistID) Then
        MsgBox "Please create a picklist before sending an email.", vbOKOnly + vbCritical
        Exit Sub
    End If
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    If Me.sfrmIssueProducts.Form!txtCtnCnt = 1 Then
        strSuffix = "Box"
    Else
        strSuffix = "Boxes"
    End If
    strSubject = "PickList # " & Me.txtPeachtreePackingSlip & " " & Me.Customer & " " & Me.sfrmIssueProducts.Form!txtCtnCnt & strSuffix
    strpath = DLookup("PDFPath", "tblDefaults", "DefaultsID = 1")
    DocName = "Picklist-" & Me.txtPeachtreePackingSlip & "_" & Me.Customer & "_" & Me.sfrmIssueProducts.Form!txtCtnCnt & strSuffix
    DocName = strpath & DocName & ".pdf"
    Kill DocName    'delete existing file if any so outputto won't hang
    DoCmd.OutputTo acOutputReport, "rptPicklist", acFormatPDF, DocName
    
    Call Email_Via_Outlook("", strSubject, "", True, DocName)
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 53 'file not found
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select
End Sub