If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
I am working on a little time tracking and invoicing database in Access
2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all print together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. Thanks for the help!!! |
#2
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
Karl Burrows wrote:
I am working on a little time tracking and invoicing database in Access 2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all print together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. Thanks for the help!!! I will let someone else who has worked with it more help you out with the specific code, but I will suggest that your DON'T want to use autonumber. Autonumber is NOT designed to provide sequential numbers. It is only designed to provide unique numbers. Most of the time they are sequential but not always. They should never be used for a number the user sees. They work well for internal use. I am guessing you want Access to find the last number used and increment it. There are a number of possible variations. Do you have more than one series of numbers? You mention Excel, what it it's rule in the process? Maybe you want to move Excel out of the process? As I understand what you are doing, you want to choose the first number and have Access fill in the rest. It that is it, and no one comes up with some suggested code, I will check to see if I still have my code from an old projects that did just that. -- Joseph E. Meehan 26 + 6 = 1 It's Irish Math |
#3
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
To get the next number in a sequence, using a starting number:
NextNumber = Nz(DMax("FieldName", "TableName"), 0) + StartingNumber I apologize for not giving an answer to the first question yet, but I just finished an all-nighter on a hot project and my brain is not thinking clearly at the moment. I'll check back on this later in the afternoon and see if anyone has replied yet. -- Ken Snell MS ACCESS MVP "Joseph Meehan" wrote in message ... Karl Burrows wrote: I am working on a little time tracking and invoicing database in Access 2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all print together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. Thanks for the help!!! I will let someone else who has worked with it more help you out with the specific code, but I will suggest that your DON'T want to use autonumber. Autonumber is NOT designed to provide sequential numbers. It is only designed to provide unique numbers. Most of the time they are sequential but not always. They should never be used for a number the user sees. They work well for internal use. I am guessing you want Access to find the last number used and increment it. There are a number of possible variations. Do you have more than one series of numbers? You mention Excel, what it it's rule in the process? Maybe you want to move Excel out of the process? As I understand what you are doing, you want to choose the first number and have Access fill in the rest. It that is it, and no one comes up with some suggested code, I will check to see if I still have my code from an old projects that did just that. -- Joseph E. Meehan 26 + 6 = 1 It's Irish Math |
#4
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
"Karl Burrows" wrote in message
.. . I am working on a little time tracking and invoicing database in Access 2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all print together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. So, we'll assume that you have the reports running individually, so that you can attach them. I'd output them to Word with an individual file name that we can grab and use for the attachment. I've stored the path to that invoice in a field called [InvoiceFile] and written a query (qryInvoices) to retrieve the email address and path to the attachment. Your code will look something like this (aircode modified from code at my website): Function Email(strTo As String, strSubject _ As String, Optional varMsg As Variant, _ Optional varAttachment As Variant) ' ŠArvin Meyer 1999-2004 ' Permission to use is granted if copyright notice is left intact. ' Permisssion is denied for use with unsolicited commercial email 'Set reference to Outlook On Error GoTo Errhandler Dim strBCC As String Dim db As DAO.Database Dim rst As DAO.Recordset Dim objOutl As Outlook.Application 'Dim objEml As Outlook.MailItem Dim i As Integer Set db = CurrentDb Set rst = db.OpenRecordset("qryInvoices", dbOpenSnapshot) Set objOutl = CreateObject("Outlook.application") 'Set objEml = objOutl.createItem(olMailitem) With rst If .RecordCount 0 Then .MoveLast .MoveFirst End If End With For i = 1 To rst.RecordCount If Len(rst!EmailAddress) 0 Then strTo = rst!EmailAddress varAttachment = rst!InvoiceFile Dim objEml As Outlook.MailItem Set objEml = objOutl.createItem(olMailitem) With objEml .To = strTo .Subject = strSubject If Not IsNull(varMsg) Then .Body = varMsg End If If Not IsMissing(varAttachment) Then .Attachments.Add varAttachment End If .Send End With End If Set objEml = Nothing rst.MoveNext Next i ExitHe Set objOutl = Nothing 'Set objEml = Nothing Set rst = Nothing Set db = Nothing Exit Function Errhandler: MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access |
#5
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
I don't have them running individually unless a create a query and report to
prompt for the company name and the have to run the report 20 times for 10 invoices (some companies bill each month, some don't, so I have to check them all for activity). I would prefer not to email them in Word since they can be changed. I am creating them and converting to pdf file format for simplicity. Basically, I need to figure out how to run a "report" that will create individual reports for each company where I can then convert to pdf. Emailing is not a big deal. I also need to figure out once I get each invoice to show separately, how to create a starting number for the first invoice and then add 1 to the invoice number for the next invoice, etc. That can be manual since I may generate other manual invoices sometimes. Thanks!! "Arvin Meyer" wrote in message ... "Karl Burrows" wrote in message .. . I am working on a little time tracking and invoicing database in Access 2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all print together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. So, we'll assume that you have the reports running individually, so that you can attach them. I'd output them to Word with an individual file name that we can grab and use for the attachment. I've stored the path to that invoice in a field called [InvoiceFile] and written a query (qryInvoices) to retrieve the email address and path to the attachment. Your code will look something like this (aircode modified from code at my website): Function Email(strTo As String, strSubject _ As String, Optional varMsg As Variant, _ Optional varAttachment As Variant) ' ŠArvin Meyer 1999-2004 ' Permission to use is granted if copyright notice is left intact. ' Permisssion is denied for use with unsolicited commercial email 'Set reference to Outlook On Error GoTo Errhandler Dim strBCC As String Dim db As DAO.Database Dim rst As DAO.Recordset Dim objOutl As Outlook.Application 'Dim objEml As Outlook.MailItem Dim i As Integer Set db = CurrentDb Set rst = db.OpenRecordset("qryInvoices", dbOpenSnapshot) Set objOutl = CreateObject("Outlook.application") 'Set objEml = objOutl.createItem(olMailitem) With rst If .RecordCount 0 Then .MoveLast .MoveFirst End If End With For i = 1 To rst.RecordCount If Len(rst!EmailAddress) 0 Then strTo = rst!EmailAddress varAttachment = rst!InvoiceFile Dim objEml As Outlook.MailItem Set objEml = objOutl.createItem(olMailitem) With objEml .To = strTo .Subject = strSubject If Not IsNull(varMsg) Then .Body = varMsg End If If Not IsMissing(varAttachment) Then .Attachments.Add varAttachment End If .Send End With End If Set objEml = Nothing rst.MoveNext Next i ExitHe Set objOutl = Nothing 'Set objEml = Nothing Set rst = Nothing Set db = Nothing Exit Function Errhandler: MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access |
#6
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
Hi Karl,
You do need to run the report once for each client. It is certainly possible to run 1 report and have a single page (or more) for each client, but I know of no way to create the separate files to attach. The pdf's can be done the same as Word, so that's not a problem, but you do need separate files. So, what you need to do is run it separate times, but you do not need to stand there and feed it the criteria, you can do that with code and have all the files waiting for you to email. Do that with a recordset, the same way as I showed you in the email code. Instead of sending the email, use: DoCmd.OpenReport "ReportName",,,"ID=" & rst!ID to create the pdf file (assuming that the printer for this is Acrobat or a pdf creator). You will probably need to name your files for the pdf's. To do that, just add the ID or client name to the file name. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "Karl Burrows" wrote in message ... I don't have them running individually unless a create a query and report to prompt for the company name and the have to run the report 20 times for 10 invoices (some companies bill each month, some don't, so I have to check them all for activity). I would prefer not to email them in Word since they can be changed. I am creating them and converting to pdf file format for simplicity. Basically, I need to figure out how to run a "report" that will create individual reports for each company where I can then convert to pdf. Emailing is not a big deal. I also need to figure out once I get each invoice to show separately, how to create a starting number for the first invoice and then add 1 to the invoice number for the next invoice, etc. That can be manual since I may generate other manual invoices sometimes. Thanks!! "Arvin Meyer" wrote in message ... "Karl Burrows" wrote in message .. . I am working on a little time tracking and invoicing database in Access 2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. So, we'll assume that you have the reports running individually, so that you can attach them. I'd output them to Word with an individual file name that we can grab and use for the attachment. I've stored the path to that invoice in a field called [InvoiceFile] and written a query (qryInvoices) to retrieve the email address and path to the attachment. Your code will look something like this (aircode modified from code at my website): Function Email(strTo As String, strSubject _ As String, Optional varMsg As Variant, _ Optional varAttachment As Variant) ' ŠArvin Meyer 1999-2004 ' Permission to use is granted if copyright notice is left intact. ' Permisssion is denied for use with unsolicited commercial email 'Set reference to Outlook On Error GoTo Errhandler Dim strBCC As String Dim db As DAO.Database Dim rst As DAO.Recordset Dim objOutl As Outlook.Application 'Dim objEml As Outlook.MailItem Dim i As Integer Set db = CurrentDb Set rst = db.OpenRecordset("qryInvoices", dbOpenSnapshot) Set objOutl = CreateObject("Outlook.application") 'Set objEml = objOutl.createItem(olMailitem) With rst If .RecordCount 0 Then .MoveLast .MoveFirst End If End With For i = 1 To rst.RecordCount If Len(rst!EmailAddress) 0 Then strTo = rst!EmailAddress varAttachment = rst!InvoiceFile Dim objEml As Outlook.MailItem Set objEml = objOutl.createItem(olMailitem) With objEml .To = strTo .Subject = strSubject If Not IsNull(varMsg) Then .Body = varMsg End If If Not IsMissing(varAttachment) Then .Attachments.Add varAttachment End If .Send End With End If Set objEml = Nothing rst.MoveNext Next i ExitHe Set objOutl = Nothing 'Set objEml = Nothing Set rst = Nothing Set db = Nothing Exit Function Errhandler: MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access |
#7
|
|||
|
|||
Add Invoice Autonumber to Report, Break Out Clients
I'm not really concerned as much with the email aspect of the database. I
am just trying to figure out how to run the reports individually adding an invoice number as I go. Once the report runs, it's very easy top print to pdf and email. I have redone the report and query to prompt for the company name, an invoice number and the date range of the time/materials billed. I am just trying to figure out a way to streamline that a bit more so I don't have to run through that 15 times a month. Examples: 1. Instead of a date range, just have a drop-down for the MMYY of the report 2. Have a drop-down for the company name so I don't have to type it each time (best scenario, of course, is to have the database print all 15 individual reports at once and I can convert to pdf and save) 3. Figure out a way to prompt for starting range of invoice number and then number each invoice that runs from that starting number (I don't mind tracking the invoice numbers in something like Excel because sometimes I may have manual invoices anyway and I need to track grand totals anyway). Thanks for all the help!!!! "Arvin Meyer" wrote in message ... Hi Karl, You do need to run the report once for each client. It is certainly possible to run 1 report and have a single page (or more) for each client, but I know of no way to create the separate files to attach. The pdf's can be done the same as Word, so that's not a problem, but you do need separate files. So, what you need to do is run it separate times, but you do not need to stand there and feed it the criteria, you can do that with code and have all the files waiting for you to email. Do that with a recordset, the same way as I showed you in the email code. Instead of sending the email, use: DoCmd.OpenReport "ReportName",,,"ID=" & rst!ID to create the pdf file (assuming that the printer for this is Acrobat or a pdf creator). You will probably need to name your files for the pdf's. To do that, just add the ID or client name to the file name. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "Karl Burrows" wrote in message ... I don't have them running individually unless a create a query and report to prompt for the company name and the have to run the report 20 times for 10 invoices (some companies bill each month, some don't, so I have to check them all for activity). I would prefer not to email them in Word since they can be changed. I am creating them and converting to pdf file format for simplicity. Basically, I need to figure out how to run a "report" that will create individual reports for each company where I can then convert to pdf. Emailing is not a big deal. I also need to figure out once I get each invoice to show separately, how to create a starting number for the first invoice and then add 1 to the invoice number for the next invoice, etc. That can be manual since I may generate other manual invoices sometimes. Thanks!! "Arvin Meyer" wrote in message ... "Karl Burrows" wrote in message .. . I am working on a little time tracking and invoicing database in Access 2003. Pretty simple database to collect hours billed, materials, taxes, etc. I have about 10 invoices a month and need to figure out a way to have the invoices on separate reports so I can create a pdf to send out an individual invoice out to each client instead of having 10 pages of reports for all clients (each are currently on an individual page, but all together). Second, I would like to have each invoice automatically create an invoice number based on the last one used and starting with a number I need to choose to keep my invoicing in sequence from my Excel sheets I was using before. The database is basically 2 tables. One with client info and the other for time tracking. I then created a query to run all my totals, etc. that show on the report. So, we'll assume that you have the reports running individually, so that you can attach them. I'd output them to Word with an individual file name that we can grab and use for the attachment. I've stored the path to that invoice in a field called [InvoiceFile] and written a query (qryInvoices) to retrieve the email address and path to the attachment. Your code will look something like this (aircode modified from code at my website): Function Email(strTo As String, strSubject _ As String, Optional varMsg As Variant, _ Optional varAttachment As Variant) ' ŠArvin Meyer 1999-2004 ' Permission to use is granted if copyright notice is left intact. ' Permisssion is denied for use with unsolicited commercial email 'Set reference to Outlook On Error GoTo Errhandler Dim strBCC As String Dim db As DAO.Database Dim rst As DAO.Recordset Dim objOutl As Outlook.Application 'Dim objEml As Outlook.MailItem Dim i As Integer Set db = CurrentDb Set rst = db.OpenRecordset("qryInvoices", dbOpenSnapshot) Set objOutl = CreateObject("Outlook.application") 'Set objEml = objOutl.createItem(olMailitem) With rst If .RecordCount 0 Then .MoveLast .MoveFirst End If End With For i = 1 To rst.RecordCount If Len(rst!EmailAddress) 0 Then strTo = rst!EmailAddress varAttachment = rst!InvoiceFile Dim objEml As Outlook.MailItem Set objEml = objOutl.createItem(olMailitem) With objEml .To = strTo .Subject = strSubject If Not IsNull(varMsg) Then .Body = varMsg End If If Not IsMissing(varAttachment) Then .Attachments.Add varAttachment End If .Send End With End If Set objEml = Nothing rst.MoveNext Next i ExitHe Set objOutl = Nothing 'Set objEml = Nothing Set rst = Nothing Set db = Nothing Exit Function Errhandler: MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access |
Thread Tools | |
Display Modes | |
|
|