Number Increment without Autonumber...HELP!!!!

[Catarina]

Hi Everyone!

I know this is a quite "old" problem but I have an Invoice database wich the invoice number was an autonumber field, but since it "jumped" numbers once I enter the form and closed it without entering data, so I changed it (afetr seeing some of the posts here" to a DMax function like this
=DMax("Invoice Number, "Invoice Table")+1, the problem is that now it returns the value 0 ALWAYS!!! What should I do ??? Where should I put the expression???
Can anybody help me??!!!

 

[Carol]

On the BeforeInsert of your form, put the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Err

' Set Invoice No to "Max" + 1
Forms!MyFormName!MyTextboxName = DMax("[InvoiceNumber]", "MyInvoiceTableName") + 1
If (IsNull(Forms!MyFormName!MyTextboxName)) Then
' If result was Null (this is the first Invoice), set to 1
Forms!MyFormName!MyTextboxName = 1
End If


Form_BeforeInsert_Exit:
Exit Sub

Form_BeforeInsert_Err:
MsgBox Error$
Resume Form_BeforeInsert_Exit

End Sub

Replace the following fields with your field/table names:

MyFormName = Your Form Name
MyTextboxName = Name of your textbox holding your invoice number
InvoiceNumber = Controlsource table name for your invoice number
MyInvoiceTableName = Your table name which holds the invoicing main information.

If you want your invoice numbers to start at a number other than 1, then change the following line to read a new starting number:

Forms!MyFormName!MyTextboxName = ?

Good luck.

 

[Catarina]

Thanks a lot, Carol!!!
It really was a great help!!