Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

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

    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
    "Heading for the deep end"

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

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

    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
    "Heading for the deep end"

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

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

    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
    "Heading for the deep end"

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    "Our" pleasure. Continued success with Access...

    Jack

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    You are very welcome ..... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I glad it made Jack scratch his "old" head ..... LOL

    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •