Default number

[realnine]

I have searched both the querry and forms fora but can not find the solution to my goal. I am trying to get a default textbox invoicenumber (field name of the text field) that is one greater than the last entry. I chose a text field because the user may include in the future characters in the number, e.g. 1024A. But for now it is all numeric. Specifically,what I want to do is open the dispatch table, go to the last redord, get the invoicenumber, add 1 to it and make that the default value in the invoicenumber field of the Form. I can not use a sequential code because the invoicenumbers are not always in sequence. When the out of sequence invoices are posted the value will be entered in the text box

Thanks

So many questions, so few answers
RealNine

 

[Mile-O]

Lookup the DMax() domain aggregate function.

 

[Fizzio]

You could use a Dmax function but the problems associated with this are that
a) if you have a text value eg 1024A, this will not work
b) it will not account for non-sequential numbering.

In the form control, set the default value as follows

=Dmax("[InvoiceNumber]", "TableName") +1

(quicker fingers Mile-O )

 

[Mile-O]

Ah ha, but if you do put a letter on the end of it, you can still, in code, use DMax.

ie.

=Dmax("[InvoiceNumber]", "TableName", "[InvoiceNumber] = " & CLng(Left(Me.txtInvoiceNumber, Len(Me.txtInvoiceNumber)-1)) +1

and then tag on the letter

i.e.

Me.textbox = DMax("[InvoiceNumber]", "TableName", "[InvoiceNumber] = " & CLng(Left(Me.txtInvoiceNumber, Len(Me.txtInvoiceNumber)-1)) +1 & "A"

 

[Fizzio]

smart arse

 

[Mile-O]

 

[realnine]

The Dmax function works when I first open the form but then that number remains the default. Example, when I open the form the next invoice number iw 1501. I enter that record and the default for the next new record remains 1501. It should be 1502.

Here is the code for default value for the invoice field
=DMax("invoice","dispatch")+1

I tried this code for after update event

Private Sub Invoice_AfterUpdate()
Me.Invoice.Requery
End Sub

But that didn't work.

If I use the Dmax approach will that slow the performance when the db gets large?

I think I prefer using the Dlookup function but I don't know how to reference the last invocie field in the last record. I need something similar to gobottom in dbase language.

Can you teach an old dog new tricks?
RealNine

 

[Rich]

DLookup is no faster than DMax, and both are considered slow


Function GetNum() As String
Dim db As DATABASE, MaxNumb As Recordset

Set db = CurrentDb


Set MaxNumb = db.OpenRecordset("SELECT Max(YourTable.YourNo) AS MaxOfYourNo FROM YourTable;")
If Not IsNull(MaxYourNo.Fields(0).Value) Then
GetNum = CStr(MaxNumb("MaxOfYourNo") + 1)
Else:
GetNum = "00000100"
End If
CurDB.Close

End Function