Autonumber Help

[Switchwork]

Hello Switch here long time no write!

The problem I am now having is with my Invoice numbers. I have obviously been using Invoice numbers when doing the invoice numbers manually.
Now I have set up this database I want to be able to continue with the invoice numbers where I left off. Do you understand??

The invoice number corresponds to the month and year the invoice is for so I would want it to update automatically each month e.g The first two numbers correspond to what month it is at the time of the invoice 12=December, the next to numbers correspond to the year 03=2003 and the next numbers are the number of the actual invoice. For example 1203004 is the last number I used to generate an invoice. Is there a way of setting the database up to do this? I really do not want to do it manually on each report using a label every time I invoice a customer as that will lead to mistakes ya get me?

So if anyone can let me know if this can be done and an exact way of doing it I would be most grateful. You will have to be specific as I do not know a lot about codes or SQL? of when or where it is to be used so Simply explained would be best for a simple person like myself

Thanx for answering in advance!!
Switch!!

 

[WayneRyan]

Switch,

Autonumbers are very useful. They should be used only
to provide a unique ID for a particular record. In
your case it should track the life-history of an
invoice. It can be used to relate other things to
the invoice, such as vendors, products, etc.

It doesn't mean that it can be an Invoice Number. Even
if you could "teach" Access when to start a sequence
it would fail in cases where someone enters one field
and then cancels the creation of a new record.

You should use the BeforeInsert event of your invoice
to construct your own Invoice Number, something like
this:

Dim InvoiceGroup As String
' Get the base component"
Me.InvoiceNumber = Format(Date, "mmyy") & _
                   Nz(DMax("[InvoiceNumber], _
                           "tblInvoices", _
                           "[InvoiceNumber] Like '" & InvoiceGroup & "*'"), 0) + 1

Something like that ...

Wayne

 

[Switchwork]

Thanx for that but how on earth do I do that??
I have no idea where on earth I put codes or anything. Strings?
I don't know what they are??

So if its not to much trouble could you please let me know exactly how i go about inserting this code. If its not to much trouble!!

I am sorry but I don't know about these things. I did ask on a previous question bout how important it was to learn about codes and things but was not answered. So I would really appreciate some help on how to go about inserting this code or string.
Are they easy to do??
Thanx for the help in the first place.

 

[WayneRyan]

Switch,

You still want the AutoNumber field. It is the one
truly unique thing about each record.

Your new invoice field is "just" another field in
your table/form. However, the users will not be
entering info into the field, you will control it.

Your data entry form will retrieve the Invoice
Number from the table/query. Get it in Design view.
Set the Property for Invoice Number: "Locked = Yes".

In the BeforeInsert event of the form, choose [Event
Function] and put in the line of code from the last
post. This is what will control the InvoiceNumber.
Once the user inserts a record, this assigned number
will be with it forever. The users can't edit it.

You'll have to change the names of things, but give
it a try and let me know.

Wayne

 

[haj1503]

Hi Switch

I agreed with code that wrote by him but if you want to write it, you write it in After Update()

for example; you should be have customerName field, right..?

you put that code after update customer Name then the Invoice Number will create you the unique number with autonumber.

Best regards,