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

    Unanswered: I really needHelp. I can't keep InvoiceDate constant. It changes when reprinting it.

    Dear All, I had posted this few weeks ago but haven't had 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:


    Code:
    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 Emi 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 - - 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
    I would be most grateful for your help.
    Thank you very much.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Questions:

    Are you saving the invoice date?

    Why not make a reprint function/option where you can control the date issue?

    If you want to "reuse" the existing code, then you need to indicate a reprint and act accordingly ... Pull the original invoice date and use it in your report.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi Owen, Thank you for your response.
    Well, I dont save just the invoice date but after I raise invoice obviously all the data entered against that invoice saves in the database.

    That is a good suggestion BUT suppose the user clicks on the same button "InvoiceClient" then that changes again !! .
    Is there anyway of stoping this invoicedate change once the invoice is raised for the first time? Or even is it possible to stop the reprinting option from the "InvoiceClient" button (once printed, then second attemd should be stopped or not work and the user should only be able to reprint it from the reprint button which you have suggested).

    If there is no other way, then I would be most grateful if you could please explain to me on how to create another code for reprinting an already printed invoice with an existing invoicedate in it which should not be changed on reprinting it?

    Thank you for your help.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    I wouldn't use Date(), it will always give you the current date. Use a text box for the user to enter a date. Or, failing that, you could always have an invoice date which is not Date(), use datediff() to see if the current date Date() is different from your invoice date, if it is then print the invoice date.

  5. #5
    Join Date
    May 2006
    Posts
    386
    Hi DJN, Date() is not used in the code? I think using a text box isnt very practical for our type of use. With regards to using a different date and using IIf() could you please tell me a bit more on how to and where Do i need to write this expression and what is that express? I am sorry as I am not very good with writing codes but I can do some basic staff when it comes to writing codes.

    Thanks for your time.
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    What is Date in this line?

    Me.InvoiceDate = Format(Date, "Medium Date")

    Had second thoughts, no need to get complicated, just use the InvoiceDate that was entered by your user in the report header. Then it doesn't matter how many times it gets printed, it will always bet the same, unless you change it in the table.

  7. #7
    Join Date
    May 2006
    Posts
    386
    Hi DJN, yeah that date! is there but I dont know what i should change in that line? The InvoiceDate is automatically created when I click on the "InvoiceClient" button. Also as I click on the "InvoiceClient" button, the invoice date changes before it opens that report. Also for our accounting purposes we want the invoice date constant on the form and as well as on the report.
    Thank you so much for your advice and instructions.
    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
  •