Results 1 to 13 of 13

Thread: Invoice Problem

  1. #1
    Join Date
    Aug 2003
    Posts
    160

    Unanswered: Invoice Problem

    Hi!
    I have a table Called tblDeliveryInformation.In that I have DeliveryAgreementNo,DeliveryDate,DeliveryCharges,H andlingCharges,RetentionBonus.

    I have One more table called tblCompanyInfo.With fields Companyname,address,Phone.which is our company.

    With tblDeliveryInformation I built a dataentry form.

    I made a query of these two tables.This query is a parameter query which is based on deliverydate. With this query I build a report.

    If I print an invoice in the morning and it has 4 records on it, I want them all to have the same invoice (let's say #2004001). Now by the afternoon, 6 more records have been added, I want to print invoice #2004002. This new invoice will include only afternoon 6 records. If I enter todays date these two invoices must appear on two pages grouped by invoice number with a page break.

    I know it can be done using code but I don't know how to do that. Could anybody help me out of this with a sample code.

    Thanks
    Vad
    Vad

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    What do you want.....?
    Saludos
    Norberto

  3. #3
    Join Date
    Aug 2003
    Posts
    160
    Each time I print an invoice I want the invoice number to be increment by one If it is not invoiced before, and the same invoice number if it was invoiced.

    Here is the code I wrote:

    Private Sub Report_Open(Cancel As Integer)
    Dim InvoiceNo As Integer
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb

    Set rst = db.OpenRecordset("select*from DeliveryInformation", dbOpenDynaset)
    With rst
    If .Fields("Invoice") = Null Then
    InvoiceNo = DMax("Invoice", "InvoiceReportbyDate")
    .Fields("Invoice") = InvoiceNo + 1
    End If
    End With
    End Sub

    In the above code DeliveryInformation is the table. Report is based on a query which includes some caliculated values.And it is based on a parameter Deliverydate.I don't know whether i have to use table or query in the recordset.

    With the above code the invoice number is not incrementing.It is showing a default value of 0000.

    Any help

    Thanks

    Originally posted by Norberto
    What do you want.....?
    Vad

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Try this one:

    Private Sub Report_Open(Cancel As Integer)
    Dim InvoiceNo As Integer
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCriteria as String

    Set db = CurrentDb

    InvoiceNo = DMax("Invoice", "DeliveryInformation")

    Set rst = dbs.OpenRecordset("DeliveryInformation", dbOpenDynaset)

    strCriteria = " [Invoice] = Null "
    rst.FindFirst strCriteria
    If rst.NoMatch Then
    GoTo Out
    End If
    With rst
    .Edit

    !Invoice = InvoiceNo + 1

    .Update
    End With
    Out:
    rst.Close
    Set dbs = Nothing

    End Sub
    Saludos
    Norberto

  5. #5
    Join Date
    Aug 2003
    Posts
    160
    Thanks For ur Code.

    Still I am not getting the Invoice Number Right.

    In ur code u r using table as a recordset.

    But I build the report using the query InvoiceReportbyDate.This is a parameter query.Which asks for deliverydate.This query includes some caliculated fields based on the table.

    In the code which one is running first code or query.

    Thanks
    Vad


    Originally posted by Norberto
    Try this one:

    Private Sub Report_Open(Cancel As Integer)
    Dim InvoiceNo As Integer
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCriteria as String

    Set db = CurrentDb

    InvoiceNo = DMax("Invoice", "DeliveryInformation")

    Set rst = dbs.OpenRecordset("DeliveryInformation", dbOpenDynaset)

    strCriteria = " [Invoice] = Null "
    rst.FindFirst strCriteria
    If rst.NoMatch Then
    GoTo Out
    End If
    With rst
    .Edit

    !Invoice = InvoiceNo + 1

    .Update
    End With
    Out:
    rst.Close
    Set dbs = Nothing

    End Sub
    Vad

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Why don't assign the new Invoice Number first and after print the report.
    Then you can use my code in the form for assign the new Invoice Number, update the table DeliveryInformation and then print the report for thats new Invoice Number ( Between the first New Invoice Number and the last New Invoice Number).
    Saludos
    Norberto

  7. #7
    Join Date
    Aug 2003
    Posts
    160
    What I want here was, I only want to generate the invoice number at the time of printing only.

    If we assign the Invoice# at the form level:

    Lets say at the time of invoicing it has 10 records.If we add 5 more records at later time the same invoice# will have these 5 additional records.

    This is the problem if we enter the Invoice number At form level.

    If I understood u correctly this is the problem.

    Instead table in the code, can't we put the query.
    I tried to keep the query, it gave me error like "too few parameters".

    Thanks
    Vad

    Originally posted by Norberto
    Why don't assign the new Invoice Number first and after print the report.
    Then you can use my code in the form for assign the new Invoice Number, update the table DeliveryInformation and then print the report for thats new Invoice Number ( Between the first New Invoice Number and the last New Invoice Number).
    Vad

  8. #8
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Send me an sample base in format Access 97 for see the problem and find some solution....
    Saludos
    Norberto

  9. #9
    Join Date
    Oct 2003
    Posts
    706
    I suggest that the best way to approach this problem is to do the invoice-number assignment in (or in a global routine called from...) the button that one pushes to print invoices. The handler assigns the numbers and somehow marks the invoices "to be printed."

    After the user has printed the invoices, =and= has confirmed that they were successfully printed, the "to be printed" mark can be removed. (Jammed ppr happ..q;keaz..ens!)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  10. #10
    Join Date
    Aug 2003
    Posts
    160
    Hi! Here I am sending the Access 97 format.If u didn't get it properly Please ask me again.

    Thanks
    Vad

    Originally posted by Norberto
    Send me an sample base in format Access 97 for see the problem and find some solution....
    Attached Files Attached Files
    Vad

  11. #11
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by vad
    Hi! Here I am sending the Access 97 format.If u didn't get it properly Please ask me again.

    Thanks
    Vad
    In send your base with the update.
    Have a new form call OnvoiceByDeliveryDate (you must conect with your switchboard) this form do all, assign the new Invoice Number and make and preview. Work like you want, no problem.
    I hope that help you.
    Now i must prepare my trip to another city for do maintaining task for
    my system...bye..bye.
    Attached Files Attached Files
    Saludos
    Norberto

  12. #12
    Join Date
    Aug 2003
    Posts
    160
    Thanks for ur quick reply and patience with the Access Baby.
    If I Enter Date into the InvoiceByDeliveryDate form it is coming up with nothing.

    On no data of a report I kept a message box saying "Enter Valid Delivery date".It is coming up with this message box.

    Any Help
    Thanks






    Originally posted by Norberto
    In send your base with the update.
    Have a new form call OnvoiceByDeliveryDate (you must conect with your switchboard) this form do all, assign the new Invoice Number and make and preview. Work like you want, no problem.
    I hope that help you.
    Now i must prepare my trip to another city for do maintaining task for
    my system...bye..bye.
    Last edited by vad; 02-09-04 at 14:49.
    Vad

  13. #13
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by vad
    Thanks for ur quick reply and patience with the Access Baby.
    If I Enter Date into the InvoiceByDeliveryDate form it is coming up with nothing.

    On no data of a report I kept a message box saying "Enter Valid Delivery date".It is coming up with this message box.

    Any Help
    Thanks
    I send your sample base with some change.
    When i prube the old version not erorr give to me.
    But now i make some changes to prevent some situation.
    In the new form enter the date in format mmddyyyy with out "\", that
    field have an input mask.
    The message "enter valid date" in the report is because non found record with that date now i change the message for "Non data with that date".
    The error "Run time error 2051" is normal when you open a form with non data and you use that event.
    I hope this help to you.
    Now i must prepare my another trip to another city for do maintaining task for my system...bye..bye.
    Attached Files Attached Files
    Saludos
    Norberto

Posting Permissions

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