Creating a invoice number field that increases with each new field.

[Gadgets]

I have an invoice (form) that I would like to have a field that when you click on new it gives you the next number in sequence. I would like to start it at 4000.



Thanks,
Chad.

[JoeM]

OK. Let's say that your Invoice Number is stored in a table name "InvoiceTable" and the field name is "InvoiceNumber".
Then, on your Form, you could put the following line of code in a command button or after update event (or however/whenever you want it to run):

    Me.InvoiceNumber = DMax("InvoiceNumber", "InvoiceTable") + 1

Note that "Me.InvoiceNumber" is referring to the name of the InvoiceNumber textbox on the Form (which is bound to the InvoiceNumber field in your table).

[Gadgets]

Do you leave the InvoiceNumber as test or number? I am getting a debug error.

[JoeM]

Number. You cannot do mathematical functions on Text, at least not without converting them to Number first.

[Gadgets]

I put the code in the date field under "On Dirty" so when the date is entered it should populate that InvoiceNumber field but nothing happens.

[JoeM]

Try putting it in a Command Button first, and test that.
You can also just try returning the calculation to a MsgBox to make sure that the calculation is being done properly.

Once you are sure it is working properly, then you can try to add it to something else.
I would use AfterUpdate or BeforeUpdate instead of "On Dirty", and I would also maybe add a check to make sure that it is blank before running that. Otherwise, if you ever change the date, it will overwrite the current Invoice Number with a new one.

[Gadgets]

I don't know if you can help me with this, but I have a form that I enter information in. Once I close the database, and then open it back up, I can't see what I just put in there. but if I go into the tables, I can see it just fine. Am I missing something?

[JoeM]

Is the Form set to Data Entry only (so it always open to a blank form)?

[Gadgets]

I have never changed anything. Where would I find that at please.
Thanks.
chad.

[June7]

It is a form property setting. Look at the Properties Sheet, Data tab.

[Gadgets]

Data Entry is set to "No".

[JoeM]

Once I close the database, and then open it back up, I can't see what I just put in there. but if I go into the tables, I can see it just fine. Am I missing something?

Can you be more explicit in what you see when you go back in to the Form?
Does it show you a record count at the bottom and allow you to bring up other records?
Is it just certain fields that are blank, or every field?
Is your Form a Bound Form?

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gadgets]

I actually took the master copy and opened it, it was working as it should. So i deleted the one on the server and re-split the DB and it works now.

Now back to the Auto number thing, Is it possible to take the record id and jump it to 40000?

[June7]

Do you mean an Autonumber type field? Review http://www.access.mvps.org/access/tables/tbl0005.htm

If you mean to generate a custom unique identifier - can be any value you want. Common topic. Start with https://www.accessforums.net/databas...try-21975.html