stop prompt after one time

[steve111]

hi,

this code puts pdf reports into a folder
I am prompted for a number via the query I run
because I have more than one report the query asks me 3 times
could this be modified so I only get prompted once for all the reports in the code

 On Error GoTo Err_Handler
    
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current invoice."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varFolder As Variant
     If Not IsNull([Invoice].[Form]![Invoiceno]) Then
   
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
            varFolder = varFolder & "\" & Me.[CustomerName]
            MkDir varFolder
            strFullPath = varFolder & "\" & "Invoice Number" & " " & [Invoice].[Form]![Invoiceno] & ".pdf"
           
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "Invoice report", acFormatPDF, strFullPath
            
             
         strFullPath = varFolder & "\" & "C of C  No" & " " & [Invoice].[Form]![Invoiceno] & ".pdf"
          
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "C OF C report", acFormatPDF, strFullPath
        
        
         strFullPath = varFolder & "\" & "Despatch No" & " " & [Invoice].[Form]![Invoiceno] & ".pdf"
          
            ' ensure current record is saved before creating PDF file
            Me.Dirty = False
            DoCmd.OutputTo acOutputReport, "Invoice delivery note", acFormatPDF, strFullPath
                 
                    
            
        End If
    Else
        MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
    End If
 Exit_Here:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
        Case FOLDER_EXISTS
        Resume Next
        Case Else
        MsgBox Err.Description
        Resume Exit_Here
    End Select

thanks
steve

 

[namliam]

Either use a form to store the information (and fetch it from the form in the query)

Or alter your sql with the required data using

Currentdb.querydefs("YourQueryName").sql = "Select ... where Anyfield = " & YourNumberVariable

Above works for numbers,
For text
... Anyfield = """ & YourTextVariable & """"
For dates
... Anyfield = #" & Format(YourDateVariable, "MM/DD/YYYY") & "#"

 

[steve111]

hi

the form can have up to 5 or more records and had its own number , 1,2,3,4,5
each number represents a shipping address

steve

 

[namliam]

So what number are you prompted for?

That is the number you need to have stored someplace... or spliced into the SQL

 

[steve111]

the number is stored in the table shipping/invoice address
and the field name is addressno.

this form is linked to the customer form with the customerid no

the query sql is

SELECT invoicedetails.ID, [shipping/invoice address].AddressNo, [shipping/invoice address].[customer id], Customers.CustomerID, Orders.[Exchange rate2], invoicedetails.Invoicetype, invoicedetails.[Invoice Date], invoicedetails.Invoicenumber, invoicedetails.AccountNo, Customers.Currency, invoicedetails.Quanity1, invoicedetails.Invoicetype, invoicedetails.Description1, invoicedetails.Terms, invoicedetails.Price1, invoicedetails.[exchange rate1], Orders.OrderNo, invoicedetails.[vat code], invoicedetails.[Product No1], Orders.NCONo, [Order Details].CDescription, invoicedetails.Method, invoicedetails.Carriage, [shipping/invoice address].[customer name], [shipping/invoice address].[Account line 1], [shipping/invoice address].[account line 2], [shipping/invoice address].[account line 3], [shipping/invoice address].[account line 4], [shipping/invoice address].[account line 5], [shipping/invoice address].[account line 6], [shipping/invoice address].[shipping customer name], [shipping/invoice address].[shipping line 1], [shipping/invoice address].[shipping line 2], [shipping/invoice address].[shipping line 3], [shipping/invoice address].[shipping line 4], [shipping/invoice address].[shipping line 5], [shipping/invoice address].[shipping line 6], [unit price]*[quanity1] AS [Net Amount], invoicedetails.[Shipping costs], [Price1]/[Exchange Rate2] AS [unit price], IIf([shipping costs]="exw",0,[shipping costs]/[exchange rate2]) AS [equiv shipping cost], BACCOUNT.[Account name:], BACCOUNT.[Sort Code:], BACCOUNT.[Account No:], BACCOUNT.[IBAN:], BACCOUNT.[BIC Number:], Products.Pdescription, IIf([Order Details]![CDescription] Is Null,[Products]![PDescription],[Order Details]![CDescription]) AS [Customer Description]
FROM (BACCOUNT INNER JOIN (((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerName) INNER JOIN [shipping/invoice address] ON Customers.CustomerID = [shipping/invoice address].[customer id]) INNER JOIN ([Order Details] INNER JOIN invoicedetails ON [Order Details].OrderDetailID = invoicedetails.orderdetailid) ON Orders.OrderID = [Order Details].OrderID) ON BACCOUNT.[Currency:] = Customers.Currency) LEFT JOIN Products ON [Order Details].ProductNo = Products.ProductID
WHERE ((([shipping/invoice address].AddressNo)=[Enter addrees No]) AND ((invoicedetails.Invoicenumber)=[Forms]![invoices]![invoicedetails].[Form]![Invoicenumber]))
ORDER BY invoicedetails.ID, invoicedetails.[Product No1] DESC;

steve

 

[namliam]

Splash your sql will you providing SQL is so much more than just splashing it, lucky enough only needs a small portion of it.

[shipping/invoice address].AddressNo=[Enter addrees No]

This bit is where it is prompting you for an adress number....

Let me spell out a suggestion for you...
Make a hidden control on your form called MrMailman
Now in your query(s) change [Enter addrees No] to Forms("yourformname")![MrMailman]

and in your code add:
Me.MrMailman = InputBox("Please provide address line number")

 

[steve111]

hi

thank you

it is working ok

I put a combo box in my form as the form is not the shipping form it is the invoice form

the combo box lets me see the address and when your message box comes up I select the number and it works great


steve