Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: How to stop autodate after I have printed a report from a form

    I have a database and on one of the command buttons which creates invoice, I am having a bit of problem with it.
    Basically the command button is called "InvoiceClient" and when I click on the button it creates an invoice (Report) which is called "rptInvoiceClient".
    Once i raise an invoice it automatically produces an autodate (Todays date).

    Sometimes we need to reprint a copy of the invoice BUT when we do so, then the date again changes to a new date and I WANT THE DATE TO REMAIN AS THE VERY FIRST TIME WHEN THE FIRST INVOICE WAS RAISED.

    Of course this date changes on the form and not on the report.

    I would be most grateful if anyone could help.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your invoice issue is much worse than you think!
    address must stay constant if the client moves offices between prints
    prices must stay constant after a 10% price increase on everything
    products and descriptions must remain after you delete them from your 'live' tables because they went OBS
    tax rates must remain constant after a change in the tax law
    ...and yes, the invoice date must stay constant.

    the simple way to handle this is to refuse to re-create invoices from the db. one approach might be to save all invoices as a .SNP so you can reprint the .SNP any time you need.

    if you want to re-create invoices from the db you need to copy all the data you need for your invoice at the time you create the invoice from the 'live' tables into an invoice table (more likely tables). whilst you are doing all that, you store the invoice date. your original invoice and any later reprints of the invoice come exclusively from the invoice tables and absolutely never refer to the live data tables where addresses, pricing, descriptions, etc etc will inevitably change over time.

    whatever you decide, your db just got bigger!

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2006
    Posts
    386
    Dear Izy,
    Thank you very much for your time and advice on how to prevent change of invoice date in case of reprinting it. But what you have suggested is good but not very practical from out point of view. It is not just reprinting now and again, actually, sometimes we raise invoice but forget to print invoice so we have to refer to it when we realise that we have not printed invoice for that job. I was thinking it has to have something to do with the codes behind the "cmdInvoiceClient" button. Below is the code behind this code, could you please suggest where do I need to change codes so that It stops changing "invoicedate" whenever I print it.

    Private Sub cmdInvoiceClient_Click()
    Dim strAccountRef As String
    Dim strSQL As String
    Dim intInvoiceExists As Integer
    Dim ReportName As String
    Dim rsNewDetails As New Recordset 'Making a recordest of new entry, to update fields with financial figures


    'Me.Requery - cant use requery since it's based ona query and sorted by date of order
    Me.InvoiceDate = Format(Date, "Medium Date")
    Me.Recalc



    'New code added by Emal on 25.05.06 to replace the code commented out above.
    If Len(Me.SageInvNumber) > 0 Then intInvoiceExists = 1

    ' If invoice has already been created for this Booking ID then
    ' tell the user, otherwise create an invoice for this Booking ID.
    If intInvoiceExists = 1 Then
    MsgBox ("An invoice has already been raise for this booking")

    Else
    'Create new record in Invoice table
    'The autonumber field Invoice.id becomes the unique Invoice number
    strSQL = "INSERT INTO Invoice ( Bookingid ) SELECT " & Me.AccountRef & ";"
    DoCmd.RunSQL (strSQL)

    temp_SageInvNumber = Generate_Sage_Invoice(Bookingid)
    End If

    'Details for new invoice

    rsNewDetails.Open _
    "SELECT * " & _
    "FROM Booking " & _
    "WHERE id = " & Me.AccountRef, _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic


    With rsNewDetails
    !InvoiceDate = Me.InvoiceDate
    !WorkHours = Me.WorkHours
    !ClientRate = Me.ClientRate
    !ClientExpenses = Me.ClientExpenses
    !JourneyMiles = Me.JourneyMiles
    !ClientRatePerMile = Me.ClientRatePerMile
    !ClientAmountLessVAT = Me.txtClientAmount
    If Len(temp_SageInvNumber) > 0 Then
    !SageInvNumber = temp_SageInvNumber
    End If
    .Update
    .Close
    End With

    'Steve - EBS - 16-11-05
    temp_SageInvNumber = ""

    'Print InterpreterInvoice
    ReportName = "rptInvoiceClient"
    DoCmd.OpenReport _
    ReportName:=ReportName, _
    View:=acViewPreview, _
    wherecondition:="Booking.id = " & Me.AccountRef
    'View:=acViewPreview to preview the fax
    'View:=acViewNormal to print the fax


    End Sub
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Me.InvoiceDate = Format(Date, "Medium Date")
    ...gets the current date when you create the invoice
    !InvoiceDate = Me.InvoiceDate
    ...saves it in your table

    change it to

    !InvoiceDate = Date()
    ...puts the invoice creation date in your table
    then make your report look at invoiceDate, not Date()

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2006
    Posts
    386
    Hi Izy,
    Thanks again for your help. But when i want to change the code from

    !InvoiceDate = Me.InvoiceDate

    To

    !InvoiceDate= Date() The code doesnt accept the two Parenthesis ()

    Since it doesnt accept the two Parenthesis I just left it as

    !InvoiceDate=Date and tried to raise an invoice but it didnt work and still changed the date to todays date.

    your kind help would be much appreciated.

    With regards,
    Emal
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •