Automatically Creating an Invoice

[Kelsod]

I have a database that I will use for invoicing, but I would like it to automatically create an invoice for customers based on parameters set for that customer (e.g., monthly, biweekly, etc.). I have tables containing the customer information, the item they are being billing, the price, etc. I want to be able to have access automatically create the invoices and add them onto the invoice table each month. Maybe there is a better way, but I thought that if I created a query for all people that are billed biweekly and all people that are billed monthly, that I could run the queries when applicable and then somehow write a macro that would go through the list of customers and add each of them to the Invoice table and add an autonumber. That way I could click run query, run macro, and then do my invoicing. I don't know if that is the way to go or not. So I'm looking for two things. If that is the way to go then I have no idea what kind of a macro I need and am looking for advice, if not, is there a better suggestion on how to create the invoices?

[June7]

What exactly are you billing? Is this like a rental or membership database and the customers have a fixed amount to be billed each month? Yes, I expect can automate but without knowing details of database structure, can't be specific. I expect will involve code that runs INSERT SELECT sql action.

Review:
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
http://www.blueclaw-db.com/accessque...nsert_into.htm

[Kelsod]

It is a housing database. Each house has a different price associated with it. So I have a tblRenters with RenterID, last name, first name, current house (which is a primary key from the houses table), and Payment Terms. Then a tblHouse which has houseID, address info, and rent. I've created an invoice table with an autonumber that I'm hoping will populate. Then I have my queries that I've done based on whether they are billed Monthly or Biweekly. I don't know if I'm on the right track or if this is the best option. Thanks for your help!!!!

[June7]

Did the references help? Attempt code and post for analysis if you still have issues.

[Kelsod]

Okay I have never created a Macro in my life so bare with me. I don't really understand the brackets either so I just guessed based on the examples. Also, In the invoice table the first column in the table is the autonumber so I didn't include it, because I thought that's what the site had said. Do I just post this into the module section. I really am clueless, but I'm loving trying to figure it all out.

Inset Into Invoice [(RenterID[, Last Name[, FirstName[, CurrentHouse[, City[, State[, Zip[, Payment Terms[, Rent]]]]]]]])] SELECT [Monthly][(RenterID[, Last Name[, FirstName[, CurrentHouse[, City[, State[, Zip[, Payment Terms[, Rent]]]]]]]])] FROM Monthly

[June7]

I don't use macros, only VBA. In VBA would be:

CurrentDb.Execute "INSERT INTO Invoice(RenterID, Last Name, FirstName, CurrentHouse, City, State, Zip, [Payment Terms], Rent) SELECT MonthlyRenterID, Last Name, FirstName, CurrentHouse, City, State, Zip, [Payment Terms], Rent FROM Monthly"

The trick is figuring out what event to put code in. Could be a button Click. Select [Event Procedure] in the event property then click the ellipses (...) to open the VBA editor, type (or copy/paste) code.

Also, probably should not be saving all that renter info into Invoice table. Just save the renter ID and the rent amount and maybe the house ID (because their unit and/or rent could change in the future).

The [] are needed when names have spaces or special characters/punctuation (underscore is exception) or are reserved words.

[Kelsod]

Thank you so much for your help.

[Kelsod]

I was on vacation until today, but I'm back at it now. I'm afraid I might be in way over my head. I'm a bit confused about how to get it all to work. Somehow I need to get a due date onto the invoice. I'm looking for the right way. The best way. Would it be to create a form where I could choose from either the biweekly query or the monthly and then set the date due and click a button that says Create Invoices? Any other ideas?

[June7]

That sounds reasonable.