Problem with Max Value (Acc 97 sr2 on 95b)

Topic

New Reply

I have a NewCustomer form tied directly to the table Customer. This form is set to open in Data entry mode. The AccNo field is a text field that has either a 650000 number for account customers or a C00000 number for cash sale customers.

I have a list box squashed to show only one value that shows the last C number entered using the row source as:
SELECT Customer.AccNo FROM Customer WHERE (((Customer.AccNo) Like “C0????”)) ORDER BY Customer.AccNo DESC;

The salesmen are allowed to put up new customers as cash sale accounts only, and at the moment they manually type in the next C number from the one shown.

I have attempted to automate this step by doing the following:
Dim begin, finish, cnt
begin = “C00000”
finish = Right(Listbox.Value, 5)
finish = finish + 1
cnt = Len(finish)
AccNo.Value = Left(begin, 6 – cnt) & finish

on formload but I have found that the listbox has no value until a value in the box is clicked, so I get a null error and the process is not automatic.

How can I achieve having the form open into Dataentry mode with the next C number in position?

Regards, Allan

Reply | Quote

Replies

There are a number of approachs to solving your problem. I would consider making the Cash ‘number’ an integer rather than text and increment it using code. To distinguish it from an Account sale you can concatenate the ‘C’ to the number to display it on your form. Code like this in the On Current event of the form will lookup the last number assigned and increment it by one:

If Me.NewRecord Then
On Error Resume Next
Me!CashSale.DefaultValue = Nz(DMax(“[CashSaleID]”, “tblCustomer”), 0) + 1
End If

I would make the ID field invisible and put an unbound field on the form with the Control Source set to:

=”C” & Format([CashSaleID],”00000″).

This will give you the ‘look’ that you want while still storing an integer in the ID field.

hth,
Jack

Reply | Quote

Unfortunately, the table is well under way and the codes are mixed in together

eg
650012
651059
C00359
650126
etc

The 65???? codes are not set by me.
As cash sale customers move to Accounts, their C code is changed to a 65 code with no relation between them.

How do I write a Dlookup or SQL code to return me the highest value of the C codes. If I can do this all in code, I can increment it as above and then assign it to the text control. (Can I???)

Regards, Allan

Reply | Quote

This code will find the highest number that starts with a ‘C’ and add one to it:

Me.CashID = Format(Mid(DMax(“AccNo”,”YourTable”,”(Left([AccNo],1) = ‘C’)”),2)+1,”00000″)

The code above is courtesy of Mr. Ricky Hicks with whom I have been discussing this with. I was of a mind to add another field for Acct. Type and place a C or a 65 there and just let the other field be an integer and increment it and then concatenate the Acct. Type for viewing purposes.

Good luck!

Jack

Reply | Quote

If you want the “C” as the prefix then make a slight change to the code:

Me.CashID = “C” & Format(Mid(DMax(“AccNo”,”YourTable”,”(Left([AccNo],1) = ‘C’)”),2)+1,”00000″)

HTH
RDH

Reply | Quote

Many Thanks Jack and to Ricky as well,
That bit of code was just what I needed.

How would I cope without places like this!!!

Regards, Allan

Reply | Quote

“Our” pleasure. Continued success with Access…

Jack

Reply | Quote

You are very welcome …..

I glad it made Jack scratch his “old” head ….. LOL

RDH

Reply | Quote