i have a table with a field that i want to have it increment by 1 every time i add a new entry. For instance i created a database that i can enter info and it will print out an invoice, But i have to enter the invoice number manually. I want it to be able to input the invoice number automatically and when i enter a new record the invoice number will be the next number up from the previous one used and no duplicates can be alowed.

There are two ways to do this.

1) Make the InvoiceNumber and AutoNumber. It will be incremented by 1 each time a new record is added. If you need help setting an AutoNumber let me know. Using AutoNumbers for this type of capability has some problems. If you start a new record, the Autonumber is incremented, even if you decide to cancel the record. So your numbering sequence could have some holes.

2) Using a Query. We prefer to use a query. Enter a query like the following one on the Form_Current Event.

Dim dbs as DAO.Database
Dim rst as DAO.Recordset
Dim mySQL as string

set dbs = CurrentDB()

mySQL = "SELECT MAX([Invoice Number] AS MaxOfInvoiceNumber FROM <TableName>; "
set rst = dbs.OpenRecordset(mySQL)
if not rst.EOF Then
Me![InvoiceNumber] = NZ(rst!MaxOfInvoiceNumber, 0) + 1
Else
Me![InvoiceNumber] = 1
End If

rst.close: set rst= noting

---------------------------
If you wanted to you could do this in a function. This works better than Autonumbers because you won't have any holes. (Much better Audit trail).

Good Luck. Let me know if you have any questions.

I was thinking about using Autonumber but i didnt want the holes and i want to start from number 2000. How can i go about doing that. Do i need to write a function or some code. if so where do i do that in. I never coded in access b4.

Add the code to the Form_Current Event. After the code creates the first InvoiceNumber = 1, simply enter 2000. After that record is saved, the next number will be 2001...

I found Form_current() in the vb editor under Form_Workorders and this is what i put inside:
Private Sub Form_Current()
<b>If IsNull(Me![WorkorderID]) Then
DoCmd.GoToControl "DateReceived"
End If</b>

*****the code above was already there****

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim mySQL As String

Set dbs = CurrentDb()

mySQL = "SELECT MAX([Invoice Number] AS MaxOfInvoiceNumber FROM <Workorders>; "
Set rst = dbs.OpenRecordset(mySQL)
If Not rst.EOF Then
Me![InvoiceNumber] = Nz(rst!MaxOfInvoiceNumber, 0) + 1
Else
Me![InvoiceNumber] = 1
End If

rst.Close: Set rst = noting

End Sub

Im confused on the code casue i dont use mysql, this is MS Access 2000 database.

Open the Form in Design View. I assume the Invoice Number is on the main form and there is no subform.

If the Properties Dialog Box is not displayed, there should be a small black box in the upper left hand corner of the form. This is the form selection box. Right Click on it and click on Properties to display the Properties Dialog Box.

CLick on the events tab at the top of the dialog box. You will see a line Current. Click once on the line to select it. A builder Button [...] should appear on the right side of the line. Click on the [...] Button and select code. That should put you in the Code Window.

IF you are not familiar with all this then you may need help. It's real easy to get into all kinds of trouble if you are not familiar with Visual Basic coding in Access. So Beware. If you are not, I strongly suggest you get a book on Access Coding as a reference and read the introduction to coding. You'll save a lot of time and frustration.