how to get dmax for account number (1 Viewer)

[PhoenixCouriers]

hi all,

does somebody know the code to complete the following.

when i enter the account number (accno) for a given customer the form will look up the last invoice number (invno) for this customer and if there is no current active invoice then it will create a new invoice number + 1 (dmax+1)

I did once have this code but has misplaced it from my database

thanks

 

[Beetle]

Hard to be specific without knowing more about your structure, but here is some (untested) example code that would do something akin to what you are asking. This example makes the follwing suppositions;

1) The form's record source is the Invoice table (or a query therof)
2) The Invoice Number field is a Text data type
3) The AccNo field is a Text data type
4) You have a boolean (Yes/No) field in the table to indicate if an Invoice is "active"

'<code>

Dim strInvoice As String
Dim strSQL As String
Dim rst As Dao.Recordset

strInvoice = Nz(DMax("InvoiceNo", "tblInvoices", "AccNo=""" & Me!txtAccNo & """"), "")

If strInvoice <> "" Then
strSQL = "Select * From tblInvoices Where InvoiceNo=""" & strInvoice & """"
Set rst = CurrentDB.OpenRecordset(strSQL, dbOpenSnapshot)
If rst!Active = True Then
With Me.RecordsetClone
.FindFirst "InvoiceNo=""" & strInvoice & """"
Me.Bookmark = .Bookmark
End With
Else
DoCmd.GotoRecord , , acNewRec
Me!InvoiceNo = strInvoice + 1
End If
Else
MsgBox "No invoices on file for the account number entered"
End If

If Not rst Is Nothing Then Set rst = Nothing

'</code>

 

[PhoenixCouriers]

hi there, thanks for reply.
I have tried this but all i get is the message no invoice on file for account, then i have to manually enter the number ??

 

[gemma-the-husky]

is the account number a string, or a number. that makes a difference