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!!!

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

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

"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

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

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
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

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
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