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

    Unanswered: Desperately need help to lock DATE so it doesnt change on Print/Update on Report/Form

    I am using a Form which I call FrmInvoice, on the Form I have a Command Button that opens a Report named rptInvoiceClient@15%". Everytime I raise a new invoice of course it creates Today's date on the Form and on the Report. However, some times I want to reprint an already raised invoice and sometimes I want to reprint the same invoice with interest charge on it. The problem I have at the moment is that everytime I try to reprint it updates the date and changes the old date to today's date. Below is the full code for raising an invoice and i would be most grateful if any one suggest what I need to change in the code to stop date from changing. I have been unable to fix this problem for months.

    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

    If Len(Me.InvNumber) > 0 Then intInvoiceExists = 1

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

    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_InvNumber = Generate_Carl_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
    !ClientAmountLessVAT = Me.txtClientAmount
    If Len(temp_InvNumber) > 0 Then
    !InvNumber = temp_InvNumber
    End If
    .Update
    .Close
    End With

    ' Renewed by emal
    temp_InvNumber = ""

    'Print Invoice
    ReportName = "rptInvoiceClient@15%"
    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
    Any help and advice would be much appreciated.
    Last edited by Emal; 12-06-08 at 19:13.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Are you storing the date of an Invoice in a table somewhere? Invoice or Booking
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    May 2006
    Posts
    386
    The InvoiceDate is stored in the main table 'Booking'. Invoice date is created through frmInvoice which is sourced from (qryInvoice). I hope you can help.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well, your first actionable line of code is:

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

    and then

    !InvoiceDate = Me.InvoiceDate

    That's why your date changes.

    What I suggest you do is do everything you need to do inside the respective areas of your If intInvoiceExists = 1 test.

    IE

    Code:
    If intInvoiceExists = 1 Then
       'Do everything that needs to be done here, which would not include updating the date.
    Else
       'Do everything you need to do here, including updating the date.
    End If
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2007
    Posts
    277
    You should also know that the output of the Format() function is a string. You should always leave a date as a DateTime value and only change it when you display it.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ If the data type of "me.InvoiceDate" is date/time and it's a bound control, that's really not an issue.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2006
    Posts
    386
    Dear All, first of all thank you for your responses. Secondly my apologies for the delay in my reply. I was off sick.

    In response to SatarTrekker, yes, the invoicedate is a field in a table and its type is a date/time.

    I have tried changing the codes related the the If function but it did not help and it still updates date everytime I print the same invoice.

    I would be most grateful if you could suggest a solution to this.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well you'll need to show what you tried to do to correct it so we can find out why it didn't work.

    Post #4 gives you the basic solution, without a crystal ball or some further details I can't really help much further :/
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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