Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need Invoice number assigned before printing (2000)

    Hi Again,

    Still working on this billing database. I've a form called frmInvoice that thanks to help from Hans and Pat, assigns the next invoice number and prompts the user if they wish to save the invoice before closing. These work beautifully. The user has the choice to print the invoice (by means of opening a report) directly from frmInvoice. This too works fine. My problem is that the invoice number is not actually assigned until after the form is saved/closed. Therefore if the user chooses to print the invoice without first saving the invoice, the invoice number is not on the invoice. I've tried a variety of approaches but none seem to work consistently. My goal is that user not have to remember to save the form before printing the invoice. I've tried putting an msgbox prompt in that remind them to save the form and based on what they choose, the report opens. The problem with this is that if they did not save the form, and choose "NO" and then they go and save the form they are again prompted as to whether they wish to save the invoice. This seems redundant and will surely irritate them over time.

    The code is as follows:

    Private Sub Form_BeforeUpdate(Cancel As Integer)


    If Me.TextInvoiceNo.Value > 0 Then
    Exit Sub
    Else:

    Dim dbs As DAO.Database, rs As DAO.Recordset, lngInvoiceNo As Long
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("Control")
    rs.Edit
    rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
    lngInvoiceNo = rs!NextAvailableInvoiceNo
    rs.Update
    TextInvoiceNo = lngInvoiceNo
    End If

    'Asks whether invoice is to be saved before closing form

    If MsgBox("Do you want to save this invoice?", vbQuestion Or vbYesNo) = vbNo Then
    Me.Undo
    End If


    End Sub


    My goal, is to have the invoice number actually assigned to the form (I guess in otherwords saved to the form) right from the very beginning or shortly thereafter without any user input other than their normal entries on the form.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Invoice number assigned before printing (2000)

    Hi Leesha,

    If you want the invoice number to be assigned at an early stage, you can move the code for it tot the Before Insert event of the form. This event occurs when the user starts typing in a new record.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim dbs As DAO.Database, rs As DAO.Recordset, lngInvoiceNo As Long
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("Control")
    rs.Edit
    rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
    lngInvoiceNo = rs!NextAvailableInvoiceNo
    rs.Update
    TextInvoiceNo = lngInvoiceNo
    ' Clean up
    rs.Close
    Set rs = Nothing
    Set dbs = Nothing
    End Sub

    To make sure that the record (invoice) has been saved, you can do so explicitly in the On Click routine of the button that opens the report:

    If Me.Dirty Then
    ' Record has been modified, so save it
    RunCommand acCmdSaveRecord
    End If
    DoCmd.OpenReport ...

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Invoice number assigned before printing (2000)

    Hi Hans,

    That worked beautifully re setting the invoice right from the beginning! And, I had always wondered what the "on dirty" command meant and now I get an idea of how it is used.

    My problem is now as follows:

    I put the save command on the click evenr for cmdPrintInvoice. This works fine. However, the user is prompted as to whether they wish to save the invoice or not (not an altogether troublesome thing) due to the code you gave me awhile back to put behind the form before update event:

    If MsgBox("Do you want to save this invoice?", vbQuestion Or vbYesNo) = vbNo Then
    Me.Undo
    Exit Sub

    End If

    If the user selects yes we are fine and the invoice opens and is can be printed. If the user selects no the record is not saved as would be anticipated however the invoice opens and there is nothing on it. I'd prefer that the invoice does not open at all in this instance but am not sure how to keep that from happening as the event that is prompting the msgbox is not the same one that is prompting the invoice to open.

    I tried taking out the save event from cmdPrintIInvoice, but if that happens, the invoice opens blank as well due to the record not being saved yet.

    Thanks,
    Leesha

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Need Invoice number assigned before printing (2000)

    If you are assigning an Invoice No. somehow, I'm not particularly thrilled about the idea of doing it in the BeforeInsert event. This means that someone else can assign that same # before you actually write it back to the table.

    I'd assign it in the form's BeforeUpdate event (and only IF Me.NewRecord=True). If the user selects to Print the invoice, to me this implicitly says "Save it", so I don't know why you need to even ask.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Invoice number assigned before printing (2000)

    Mark,

    If you look at the code, you will see that the new invoice number is written to the Control table immediately.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Invoice number assigned before printing (2000)

    Hi Mark,

    Looks like Hans already clarified when the invoice number is actually written. Honestly, I'm glad he pointed that out because I would've missed it!!

    To answer you questin re why the save prompt..................I don't actually need the the prompt to come up with when the invoice is printed. Apparently it does due to the before update code asking whether the user wants to save the record. The reason I put this in originally is because I found that when exiting the form, the record was automatically saved by Access and often times this resulted in invoices that were meant to be deleted due to an error or whatever. I figured if the user was prompted as to whether they wanted to save the changes, if they said no that would eleviate having bogus data. The original purpose was not to have the user prompted when they went to print the invoice. Now I'm trying to find a work around.

    Leesha

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Invoice number assigned before printing (2000)

    There is a problem if you put the code in the BeforeInsert, and that is, if the user decides not to proceed with the invoice then you will have assigned the invoice number for nothing, this will leave holes in your invoice number range. I would put the code in the BeforeUpdate event instead.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Invoice number assigned before printing (2000)

    You're correct.

    But I think that Leesha should move the question whether the record should be saved to the beginning of the BeforeUpdate event; in the code as she posted it at the beginning of this thread, the new invoice number is generated and after that, the user gets the question. If (s)he replies No, the newly generated invoice number will be wasted.

    And perhaps Leesha should also set Cancel = True if the user replies No. When the user clicks the button to open the report, RunCommand acCmdSaveRecord will invoke the Before Update event, and this will raise error 2501 (action canceled). This error can be trapped and used to exit the On Click routine before opening the report.

    But I don't know what other consequences this might have.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Need Invoice number assigned before printing (2000)

    But that would only be a practical solution if there were no required fields.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Invoice number assigned before printing (2000)

    OK guys, I'm totally confused as to which way to go. I'd like the invoice numbers to be in sequence but it isn't as much of show stopper as having the report open the errors etc. when the user choses to saving the invoice.

    BTW, I tried switching the code back to the before update event and placing the question at the begining of the code as Hans suggested. If the user selects no, the following error comes up: The Mircrosoft Jet Database Engine can not find find a record in the table tblDemographics with key matching fields AccountID.

    I'm not sure if the suggestion re cancel = true would eliminate that. However I also wasn't sure exactly how to code it.

    Leesha

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Invoice number assigned before printing (2000)

    Post a zipped version of your database Leesha.

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Invoice number assigned before printing (2000)

    Here ya go. I put in the forms with the changes that Hans made to frmAR.

    To open frmInvoice use frmInvoiceAccountName
    To open frmAR use frmARAccountName

    Thanks!

    Leesha
    Attached Files Attached Files

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Invoice number assigned before printing (2000)

    Try this, that is move the msgbox to the top of the code and insert the couple of bolded statements :
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    'Asks whether invoice is to be saved before closing form

    If MsgBox("Do you want to save this invoice?", vbQuestion Or vbYesNo) = vbNo Then
    Me.Undo
    Cancel = True
    Exit Sub

    End If

    If Me.TextInvoiceNo.Value > 0 Then
    Exit Sub
    Else

    Dim dbs As DAO.Database, rs As DAO.Recordset, lngInvoiceNo As Long
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("Control")
    rs.Edit
    rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
    lngInvoiceNo = rs!NextAvailableInvoiceNo
    rs.Update
    TextInvoiceNo = lngInvoiceNo
    Set rs = Nothing
    Set dbs = Nothing

    End If
    End Sub

    Let me know how it goes.

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Invoice number assigned before printing (2000)

    Hi Pat,

    I tried the new code and now if I say no to saving the record I get an error message that says there is no current record due to the following code being highlighted:

    Private Sub cmdGenerateBill_Click()
    If Me.Dirty Then
    ' Record has been modified, so save it
    RunCommand acCmdSaveRecord
    End If

    DoCmd.OpenReport "rptInitalInvoiceSingle", acViewPreview

    The reason there is a code to save here is so that invoice number will be saved for the record will be saved before the invoice opens, otherwise the invoice was opening without an invoice number on it.

    Leesha

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Invoice number assigned before printing (2000)

    Leesha,

    Logically, you can't say no to saving the record and then expect to generate an invoice for it, especially since the Me.Undo in the code to cancel the save reverses the record. If you were entering a new record, it has been cancelled.

    Which line of code is highlighted? The code engine doesn't highlight a whole block of code, so you need to be specific.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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