Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a Mass Invoice (Access 2000)

    I have a program that records invoices and payments for students' school fees. I need to know if it's possible to be able to select some students, then select the fees and amounts that you want on the invoice, then have it do invoices for all the students you selected. As of now, I have to go into each student and create their invoice. But some schools have 400 or more students, and most of the students have the same fees on their invoice. I also want to be able to add onto their invoice if I need to at a later date. Let's say one of those students that I created an invoice for on a mass invoice selection ends up taking an additional course for which I have to charge fees. I want to be able to add that on. My program is set up for only doing one invoice per student. Any help would be greatly appreciated.

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

    Re: Making a Mass Invoice (Access 2000)

    Hi Jennifer, long time no hear.
    Do the students have different invoices details lines? If not you could set up an Invoice Details Template table and generate the invoices from their for the 400 or so students.

    You should post back with more details of what you require.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    Great to hear from you, Pat! It has been a while. For most students a school could have Elementary Fee of $40, Local Levy Fee of $5. They would want to be able to select all the students that they want to do this invoice for (it could be e.g. 350 out of the 400 students, or it could even be for all of them--they might want to be able to choose). Then the program would create an invoice for each student that was selected with the Elementary $40 fee, and the Local Levy $5 fee on it. But now lets say that a couple of those students are taking additional courses which they have to pay fees for. I want to be able to go into each individual student and add those fees--maybe even be able to do another main invoice for the course fees then select the students that are taking this course and add that line onto their original invoice (if they have one). Do you know what I mean?

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

    Re: Making a Mass Invoice (Access 2000)

    As I suggested you can setup a template table holding 2 records (Elementary Fee of $40 and Local Fee of $5) and write some VBA code to read thru all students and for each student simply write all the Template table entries (all 2 of them) away for each student. I presume you would have to write an Invoice head record for each student before writing the Template records as they have to be linked (and with Referential Integrity).
    After the job is finished you would then go into an amend Invoice for to add the additional lines to the couple of students you mention.
    Does this help?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    I'll see what I can do. I don't have much experience with VBA still, though. Could you kind of walk me through it? How would I get the invoice table to create invoice numbers for the selected students and then populate the invoices with the invoice template?

    Here's what I'm thinking...I create a table with Elementary Fee and the amount of $40, and a Local Levy Fee and the amount of $5. Do I need to create a relationship with this table? Do I need a primary key in this table? Then could I write some VBA to go down the list of the students I selected by going to the next record? I'm just not exactly sure how to write this. By going to the next record all the way through my list I would create a blank invoice wouldn't I?

    This is just a generalization of it. I'll have to sit down and think about this a while. I'll work on it maybe on Friday. I'll let you know of my progress.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    Here is some code that might help out. You will have to adapt to your situation, but it gives you a starting point.

    This code is in the OnClick event of a command button on a form. This is from my church management database where we have a number of members who are on PAR (Pre-Authorised Remittance) which means that every month a set amount is automatically debited from their bank account. All of the data resides in a table and a sub-table. The table contains the account that the amount is deposited to (general, building fund, missions, etc) and the sub-table the individuals (by envelope number) who deposit to that account. These tables live in their own little unrelated world in the database as their sole function is to generate a deposit to the accounting records, which is analagous to what you want to achieve.

    The on click processes the deposit. It collects all of the information for each account and sets the main deposit up, then generates an SQL statement that dumps the detail information into the deposit details table. All the bookkeeper has to do is put in the date of the deposit and hit OK. The field names and their uses should be self-explanatory.

    (I believe it was Pat who actually worked out the SQL for me, so this may save him some redundancy!) Post back to me if you can't parse the code from here.

    Peter

    Private Sub OK_Click()

    On Error GoTo Err_OK_Click

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strAcct As String
    Dim varDepDate As Variant
    Dim varDepDesc As Variant
    Dim lngDonID1 As Integer


    Set db = CurrentDb()
    Set varDepDate = Forms!Form1!DepositDate
    Set varDepDesc = Forms!Form1!DepositDescription

    Set rst = db.OpenRecordset("PAR", dbOpenDynaset)
    Do While Not rst.EOF
    With rst
    strAcct = rst.Fields("Account").Value
    lngDonID1 = rst.Fields("DonationID").Value
    Set rst2 = db.OpenRecordset("tblDonations", dbOpenDynaset)
    With rst2
    .AddNew
    ![Account] = strAcct
    ![ContributionDate] = varDepDate
    ![DepositDescription] = varDepDesc
    ![PARID] = lngDonID1
    .Update

    End With
    strAcct = ""
    .MoveNext
    End With

    Loop
    Dim sSql As String
    sSql = "INSERT INTO tblDonationDetails ( DonationID, EnvelopeNumber, Amount, Comment )"
    sSql = sSql & " SELECT tblDonations.DonationID, PARDetails.EnvelopeNumber, PARDetails.Amount, PARDetails.Comment"
    sSql = sSql & " FROM tblDonations INNER JOIN PARDetails ON tblDonations.PARID = PARDetails.DonationID"
    sSql = sSql & " WHERE (((tblDonations.ContributionDate)= Forms!Form1!DepositDate));"
    DoCmd.RunSQL sSql


    Exit_OK_Click:
    Exit Sub

    Err_OK_Click:
    MsgBox Err.Description
    Resume Exit_OK_Click

    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    I just want to make sure I understand your code correctly. First, I will show you exactly what I want to be able to do. I want a form that shows the following:

    Invoice Number: (this is automatic in the Invoice table)
    Date: (type in the date that goes on all the invoices)

    [subform]
    FEE NAME AMOUNT
    Elementary 35.00
    Local Levy 5.00

    Total Invoice $40.00

    [button to press that will open a window and show the list of all your accounts by student name with a check box beside. You then click on the ones you want to do this mass invoice for--or there should be something that will check them all.] (When you close the window, it should go back to your Mass Invoice form, and have another button that you press to process the Mass Invoice.)

    (On the subform you select from a drop down box, the Fee Name. Then type in the Amount you want to charge for each fee name. And it will show a total for the invoice.) (I have the subform setup to show when you go into each individual account. Do I use this same subform [InvoiceDetails subform] on my form for this Mass Invoice?)

    As for your code:

    I'm unclear as to what "DAO" means. I looked in my book, and it doesn't really explain it. It just says that it stands for Data Access Object and is used because the line utilizes methods and statements belonging to the DAO code library. I have no idea what this means. They had an example, but only had DAO.Recordset. They didn't have a DAO beside "Database". Why is that?

    So would I do my code like this?

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strAcct As String
    Dim varInvDate As Variant
    Dim varDepDesc As Variant (Do I leave this line out because I have a subform that lists the invoice details???)
    Dim IngDonID1 As Integer (I'm not really sure what this is???)

    Set db = CurrentDb()
    Set varInvDate = Forms!MassInvForm!InvoiceDate (I've called this whole form MassInvForm. Do I then set the variable to be the invoice date I've entered on the form???)

    Set rst = db.OpenRecordset("tblStudent", dbOpenDynaset) (Should I be opening the table that all my students are in? I'm not sure why you are opening the recordset then opening the dynaset. Don't you just have to open the recordset?)

    Do While Not rst.EOF
    With rst..........[Now, I'm pretty much confused with the rest. Could you explain what each line is doing? I know that is kind of a lot to ask, but I'm having a tough time figuring out what tables of mine I use because I don't know what your tables are like.]

    I appreciate your help. Is it worth taking a class in VBA even if it is not specific to Access?

  8. #8
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    First some general comments as you packed a lot of questions into your post <img src=/S/grin.gif border=0 alt=grin width=15 height=15> .
    DAO does indeed stand for Data Access Objects which is one of the several flavours of VBA. The point to "Dim db as DAO.Database" as opposed to "Dim db as Database" is to explictly state to Access which flavour you are using thereby reducing the possibility for conflicts with ADO (which is the default flavour for Access 2000 and later. DAO, as I understand it is not being developed further. One of the real gurus can give you more complete information.)

    The code I supplied you is to be used in a completely separate form and tables than the ones in which the real invoices are created. In the database the code is used, these storage tables contain the values for a series of monthly transactions that are identical. You enter into this form (and therefore the storage tables) the individuals and amounts that you want to be able to process regularly and then press the button to add this information into the real receipt records (in your database the real invoices).

    Clearly for your use this has to be adapted somewhat as the principles are the same but the application is different. I will try to play with this a bit and see if I can come up with a sample that is more in line with what you need, but it may take a couple of days.

    Finally, your specific questions:

    1) All the Dim declarations at the beginning. These depend on what objects need to be declared for the code to run. Don't worry about them right now.
    2) Set varInvDate = Forms!MassInvForm!InvoiceDate
    You will want the Forms!xxx to reflect the box in your storage form from which the mass procedure will run, not the actual invoice form.
    3) dbOpenDynaset
    This is not opening another recordset, this is specifying the type of recordset. Open up help in VBA (hit alt - F11 to open the VB Editor) and look up RecordsetType Property. This gives and explanation of what is happening here.
    4) Do While Not rst.EOF
    A recordset has a Beginning of File (BOF) and an End of File (EOF) which is the point in the recordset AFTER the last record. This line just says "Do all the stuff that follows this line of code and keep doing it until you get past the last record (EOF). All of this stuff is the actual copying and pasting of information. This is a loop that looks at each record in turn (in your case each student) and copies the appropriate information from the storage table into an invoice for that student. It then goes and does the same thing all over again for the next student. In essence it is doing exactly what you have been doing by hand, it is just able to do it a computer speeds instead of people speeds!

    4) As to taking a class in VBA. It probably won't hurt, but again, perhaps the gurus can comment. I am entirely self taught. I view Access as a bit of a computer game where the goal is to beat the evil Microsoft into submission... The beauty is there are endless levels of skill. The two books I use all the time are "Access 2000 Developer's Handbook" by Getz, Litwin, and Gilbert (Sybex) and "Beginning Access VBA" (WROX Publishing). In terms of understanding how VBA works, Beginning Access VBA is first rate.

    Hope this helps for now. I will post back as soon as I can.

    Peter

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

    Re: Making a Mass Invoice (Access 2000)

    Actually, DAO and ADO are object models rather than flavors of VBA. You select one and work with the objects it contains. You can't work with objects the other contains unless you have a reference set to that model as well. If you have references set to both, then you absolutely MUST indicate the object model in the declaration for things like Recordset, Propery, Field, Index, Parameter, etc. If you don't specify, Access makes assumptions about which one you're using and there's a good chance it will get it wrong. Not all objects exist in both models, and they have somewhat different purposes. ADO is designed to access data in a wide variety of file formats and to deal with it much the same way regardless of what format the data is in. DAO is optimized for Jet databases and has specific objects that relate to Access interface objects like forms and reports. Since those objects aren't data, strictly speaking, ADO has very limited facilities for dealing with them.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    Thursday TV was boring so I got it done tonight. A nice excercise in writing code for me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. You will see, the SQL stuff all dissappeared as it wasn't necessary. A third recordset was added to add stuff into Invoice details as this is all hard-coded rather than looking up stuff in a reference table and then copying it.

    I told you a guru would give you the DAO answer. [memo to Charlotte: Object model just wouldn't come to my brain. Flavour gets the point across even if it is a silly analogy!]

    The attached database will automatically create invoices in the invoices table according to the boxes checked in the client's form. You can then emend the invoices as you wish after they are created. The mass invoices should always be created first , otherwise you will create duplicate invoices.

    This is cobbled together from various old databases, so the naming is VERY inconsistent. I deal with receipts a lot more than invoices so you will see that pop up. In addition, the code for the on click event of the button on the Mass Invoice Form only takes into account the three possibilities for the two accounts you mention Elementary is ture, Local Levy is true, both are true. Both false is filtered out by the Mass Invoice query. If you have many more options this code will get clunky and other options should be considered.

    This isn't necessarily the most elegant solution, but it will do what you want. Others can feel free to make it more elegant.

    Peter
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    One final note: I abandoned the need for the reference tables in your circumstance. It didn't seem to be necessary. The check boxes in the Client table serve this purpose.

    Peter

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

    Re: Making a Mass Invoice (Access 2000)

    Would you either describe what your table structure is and what you expect to be populated, or better still, send a zipped copy of your database with all sensitive data taken out.

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

    Re: Making a Mass Invoice (Access 2000)

    Hi Jen
    I have taken PeterN's database and provided another alternative.

    I have changed the following tables:
    Accounts
    Added an extra field called Selected. This needs to be set for the fee to be automatically added in this run
    Clients
    Added an extra field called Selected. You set this if the Client needs to be invoiced automatically in this run.
    Invoices
    Change ClientID from a loopkup to a text field. I just don't like this feature.
    InvoiceDetails
    Change Account from a lookup to a text field.

    You will need a couple of forms to set the Accounts selected as well as the Clients required in this run. In the Clients for I would provide a button to set them all to selected. I would also allow them to change the Selected field on this form.

    Queries required
    q1
    Generates an Invoice record where the Clients.Selected is True. The field Invoice.InvoiceAdded is set during this query.
    q2
    Generates an InvoiceDetail record where the Accounts.Selected is True and InvoiceAdded is True.
    q3
    Sets Invoices.InvoiceAdded field to False where is it True.
    q4
    SetsAccounts.Selected field to False where is is True.
    q5
    Sets Clients.Selected field to False where it is True.

    You could put the above 5 queries into VBA code behind a button like:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "q1"
    DoCmd.OpenQuery "q2"
    DoCmd.OpenQuery "q3"
    DoCmd.OpenQuery "q4"
    DoCmd.OpenQuery "q5"
    DoCmd.SetWarnings True

    This should simplify things as well as speed them up as adding records via VBA vs SQL is a lot slower.
    If you have any questions, then please post.
    Attached Files Attached Files

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Mass Invoice (Access 2000)

    Hi Pat,

    I've tried what you suggested, but I'm still having a bit of a problem. I want to be able to do up a form so that I can select what fees I want to put on the mass invoice, then type in the amount to charge for each fee because the fee amounts are necessarily the same each time. So I want to select the students I want to invoice, then click a button and go to the invoice template to enter the lines I want on the invoice. I select each fee by dropdown box, then type in the fee amount. Then I click a button to process the invoice I created for the selected students.

    I'm emailing you my database because I couldn't zip it small enough to put on this post. Anyway...I did up the "frmSelectStudents" with the "SelectStudent subform" to select the students I want to invoice. Then there is a button on that form to "Create Invoice". That goes to the "frmSelectInvDetail" with the "SelectInvDet subform" to do the invoice. But that part does not work. I'm not sure what I'm doing wrong. The queries I've created are "GenerateInvoice query", "GenerateInvoiceDetail query", "ClearInvAdded query", and "ClearSelected query". I know I'm probably just missing one little thing here, but I don't know what it is.

    I really appreciate your help on this.

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

    Re: Making a Mass Invoice (Access 2000)

    Hi Jen, sorry I have taken so long to get back, I have been rather busy.
    In addition to my previous I have added an extra field to the table InvoiceDetails (it shows the fee charged).
    Just translate what I have done into what you have got. I have tested mine and it works just fine.
    If you have any problems, just post back.
    Good luck.
    Attached Files Attached Files

Posting Permissions

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