Max function problem

[Gumbo78]

I am use'n a form to enter customer and order information. If the customer allready exists, the form will auto fill in their Cust_ID and Email so that the user does not have to. If the customer does not exist, I am trying to make the form search through the current Cust_ID's and find the max, add 1, then enter that into the combobox on the form as the new customer's id. This is my current code:

'fill in email and cust_id if customer exists
Private Sub cmbcustomer_AfterUpdate()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

'check if customer exists
rs.FindFirst "Customer_Name = '" & [cmbcustomer].Value & "'"

If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
[cmbcustid].Value = Cust_ID 'fill in cust_id
[cmbemail].Value = Email 'fill in email
Else
MsgBox "New Customer, Entering a new Customer ID"
[cmbcustid].Value = Max(Cust_ID) + 1 'enter new cust_id
End If

rs.Close
End Sub

The error I am getting is:

Compile error:
Sub or function not defined.
and it highlights the word Max in my
[cmbcustid].Value = Max(Cust_ID) + 1 line.

I'm not understanding why it's not letting me use the Max function here. I tried to set up a query to find the max Cust_ID, but that gives me problems trying to add 1 to a string varible. (string because I have to set the initial = to an sql statement) Any suggestions on how I could make Max work in this situation? I thought about make'n a private function named Max but I wasn't sure what I'd put there since it is usually a built in function.

-G78

 

[KenReay]

Hi

You cannot use MAX() in code it is an SQL command

a simplistic solution (assuming table is ordered by CustomerId) is

Else
MsgBox "New Customer, Entering a new Customer ID"
me.recordsetclone.movelast
[cmbcustid].Value = Me.RecordsetClone!CustomerId + 1
End If

but beware this will not work in a multi user environment, why not just use an autonumber ?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gumbo78]

That works now. I'm not use'n autonumber because in the future customers will have set numbers, but right now they are just being assigned numbers for purposes of getting them in the database with their orders. In the future I will force the user to enter the customer id instead of assigning one but for the first initial data dump until the cust_id numbering system is implemented I wanted them to have to do the least amount of clicking as possible. Thanks!

-G78

 

[gol4]

Can't use max that way. You would need to go thru your recordset and locate the largest number

you could use function dmax("field","table&quot

[cmbcustid].Value = DMax(Cust_ID,"customertablename&quot + 1 'enter new cust_id

or open another recordset
set rs = currentdb.openrecordset("select max(custid) as b from customers&quot
[cmbcustid].Value = rs.Fields(0) +1