I'm currently building a database in Access 2010 to track the voiceover recordings we have various (9) voice artists do, as well as tracking their weekly payments. I've got most of it figured out now, except for a few things.

One is the weekly invoices (reports) that I'm generating. I am planning on generating invoices for the voice actors on their behalf to send to our own Accounts Payable dept on a weekly basis. Each invoice will include all voiceovers that have been approved that week for payment.

I have everything set up, but I have an Invoice # and Invoice Date control that is linked to fields in an Invoices table (tbl2012Invoices). The report itself pulls from tbl2012VoiceActorDetails (contains contact info and such for actor) and from the Invoices table (for the Invoice # and Invoice Date fields). The report is grouped by actor. It then has a subreport that is based off a query (qry2012Voiceovers) that filters to pull all voiceovers that were completed by that artist that have been approved for payment, but have not been invoiced yet (Invoice number is null).

When I run this report I would like the Invoice number to automatically generate (and not mess up the filtering, as I have it as invoice # is null) in the format I0000. I would like the invoice date to also automatically show up as today's date. I've read ways on how to do this, except I haven't seen any ways on how to have it do this, and to then UPDATE the Invoices table (tbl2012Invoices) and the table that has all the voiceover information (tbl2012Voiceovers), in order to link each voiceover to it's invoice (one-to-many relationship).

Any ideas on how to do this? I imagine this would involve macros (or coding), which I'm not super knowledgeable with (coding particularly), so you may need to spell it out a little for me.

Thanks!

(IMG:style_emoticons/default/welcome2UA.gif)

Sounds like you want to 'check off' that an artist has received an "Invoice", and that you're intending to use an "Invoice Number" to do so.

A consideration ... if you run the report that generates your invoice(s), and Access automatically 'checks' those off, what happens if the power goes out before the printer finishes? ... or the printer runs out of paper? ... or the printed copy is illegible? ... or ... (you get the idea, right?).

As far as I know, Access isn't smart enough to "look" at the printed invoices and know which ones actually were successful. For that, I've only found that USB technology (using someone's brain) can do the job.

You could still have a way to 'check off' the successfully printed invoices, but you may want to rethink the process so you can insert the human review/approval.

I'm not sure I actually fully understand what you're saying. I want to generate an invoice number to help track payments with our AP dept. I mean I basically want an invoice number for all the reasons all invoices have invoice #'s. So AP, myself, and the actor have a # to refer to when checking on the status of the payment. The invoice number will also group the voiceovers, so I know which voiceovers were included with each payment, if that makes sense.

Because I have the report populating the voiceovers that are ready for invoicing, I want the report to assign one invoice number to that group of voiceovers from that artist. I need these invoice numbers (and the invoice dates) stored in a table so I can refer back to them later in the case I need to follow up with our AP dept on payment status. Maybe I just need a query to populate the invoice number and date before I run the report? And then report will have to pull up the invoices by other means (since the Invoice # would not be null)?

Also, not sure if this is something you need to know, but I will be keeping hard copies (or at least a pdf file on my hard drive) of the invoices when they have printed and sent to AP (and I sign off on each one before sending to AP), so I don't need to regenerate past invoices through Access.

Thanks for the help!

If I'm understanding your point about "checking off" I could add a yes/no field in the Invoice table that I would check off when I sent the invoice to AP, and then I could filter my report with invoice #'s that are NOT null and are not checked off...so now I just need a query that runs right before the report that would generate the invoice numbers and invoice dates. So I guess this should have been posted under the Access Queries topic...

This post has been edited by kl16502: Mar 14 2012, 11:59 AM

Sorry for the confusion ...

You can't trust Access to know that the invoice you "ordered" was, in fact, delivered. YOU have to tell Access that.

Trying to get something automated for this will risk Access interpreting but not being able to verify.

Do you want to record the attempt to create an invoice (Access can do that), or the actuality of an invoice (only YOU can do that).

I can check off if an invoice is actually sent, but I need Access to generate the invoice number and invoice date, so I can use the invoice number as a primary key to group the voiceover payments. See the post that I submitted right before your reply in regards to manually checking off.

I'll see if I can help by focusing on what I see as separate pieces...

You need an invoice number to print on the report. Don't worry about the "must be in this format" part, since you can apply that as needed. How do you want to derive the invoice number? Is that a simple sequential number (i.e., find the previous highest number and add one)? Check the DMax() function in Access HELP for ideas on that.

The second part is putting that invoice number and todays date (check Access HELP for the Date() function) into the Invoices table. You can use the same DMax() function to derive the value of the 'next' invoice number, then use an Append query (see Access HELP<g>) to add a new record into the Invoices table.

Does this get you closer?

Yes, I think it does, I will look into this!

I'm still lost, but I'm going to continue this in the query topic, as I think it is more fitting there. http://www.UtterAccess.com/forum/Update-Qu...i-t1983976.html