Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006

    Unanswered: How to stop InvoiceDate changing when reprinting it from a Form onto a report?Please

    Dear All, I had posted this few days ago but didnt have a successful suggestion and I really need help. Please read it.
    I have a command button that 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).

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

    Below is the code behind the command button that creates the invoice:

    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")
    '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")
    'Create new record in Invoice table
    'The autonumber field 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
    End With
    'Steve - - 16-11-05
    temp_SageInvNumber = ""
    'Print InterpreterInvoice
    ReportName = "rptInvoiceClient"
    DoCmd.OpenReport _
    ReportName:=ReportName, _
    View:=acViewPreview, _
    wherecondition:=" = " & Me.AccountRef
    'View:=acViewPreview to preview the fax
    'View:=acViewNormal to print the fax
    End Sub
    I would be most grateful for your help.
    Thank you very much.
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Strictly speaking, if the invoice is being changed (amended) the invoice date should reflect that! Having said that, you're either going to have to add a field to your record that the data is pulled from to hold the invoice date or make the query that you base it on a parameter query that asks for the invoice date. The only other approach I see would be to set up a separate table for holding the invoice number and invoice date and checkig to see if the invoice number already exits in the table. If it doesn't, add date and number to the table; if it already exists, pulling the date from the table to the report.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2006
    Dear Misslingling,
    As you can see from the code that my database is taking its invoice number from another program but the invoicedate is produced by the DB itself. Also, as you can see from the code above, if an invoice number exists then the user gets a message flagging " An invoice has already been raised for this booking" then when we reprint the invoice the old invoice no remains intact. I want the same action to be carried out with the InvoiceDate.

    With regards creating a new table that should hold the invoice numbers and invoicedate produced by the database, This seems a better option than the other two BUT unfortunately, I couldnt get my head around it as to how to create a table which should pull the invoicedate and invoice number from the InvoiceForm (Query on which the invoiceform is based) and save it in that table. I also dont exactly know on how to write the expression/code so that on reprinting an already printed invoice it would check the table that would hold the invoice date and invoice number and check whether an invoice number exists ! if doesnt exist then add the new date.

    I would really, really appreciate if you could please give me live example so that I can follow your step by step instruction. Thank you for your time and assistance.
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    May 2006
    Could anyone please help? I really, really need to get this done with your helps.
    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