Results 1 to 12 of 12
  1. #1
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162

    Table auto number issue when getting to 10000

    Hi all,

    I have a bit of a problem with a table which previously stored number with 4 digits and has now got to 10000. The problem being when it creates 10000 it puts the data to the top of the table so when the next person comes to create a record it looks at the last entry (which should be 10000 but as thats at the top thinks its 9999) and then re-adds 10000. Is there any way of stopping this?

    Cheers!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Are your users entering data directly into the table? If so, that is not a good practice. All user interaction should be through forms; users should never even see your tables.

  3. #3
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Hi,
    Yes its via a form - the user presses a button to create the next number based on the table which hold the previous numbers.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If you are using the autonumber data type available in Access, the value should not repeat. You say that the user presses a button to create the next number, so that could mean that there is code behind that button that creates an incremental number (but does not use Access' autonumber data type). Is that the case? If so, could you post the code behind that button?

    What is the data type of the incrementing number field in the underlying table?

  5. #5
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    I can see the problem, the auto number has originally been defined as text rather than number (dont ask me why as I didnt create it). I can convert the column using

    Code:
    SELECT Max(Val([PO No])) AS expr1
    FROM [Purchase Req Log 2011];
    And then max it to get the highest number - what a pain in the @rs5e

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad you found the problem. I kind of suspected that might be the case.

  7. #7
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Dont suppose you can see what Im doing wrong here?

    Code:
    Dim lngNextID As String
    Dim strSQL As String
    strSQL = "SELECT Max(Val([PO No]) FROM [Purchase Req Log 2011];"
    
    lngNextID = strSQL
    NewPOID = lngNextID + 1
    Thanks

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You cannot use a SELECT query; you would have to open a recordset based on a SELECT query and then add the new record to the recordset. Typically the DMax() function is used:

    lngNextID=1+DMax(Val("[Po No]"),"[Purchase Req Log 2011]")

    I'm not sure how the Val() function will work in the DMax() so you will have to do some testing. Does the field only contain numbers (as text) or does it also contain alphabetic characters?

  9. #9
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Thats exactly what I was about to write - val wont work with dmax. The reason I was using Val was to convert to the text (all be it they are just numbers) to numbers and then max that. Still stumped...

    Sorry that should read convert from taxt value to number.

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You could try clng() : converts to a long integer number.

  11. #11
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    All fixed with

    Code:
    lngNextID = DMax("Val([PO No])", ....... + 1

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Sorry about that, I put the quotes around the field name rather than the expression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto Number
    By sah in forum Forms
    Replies: 1
    Last Post: 04-30-2012, 07:20 AM
  2. Auto Fill Issue #2
    By Swilliams987 in forum Database Design
    Replies: 9
    Last Post: 01-21-2011, 03:52 PM
  3. AuTo NuMbEr
    By Evgeny in forum Forms
    Replies: 2
    Last Post: 04-25-2010, 04:23 AM
  4. Auto-number
    By rkski in forum Programming
    Replies: 2
    Last Post: 01-13-2010, 02:04 AM
  5. Need help with an auto number
    By Wrangler in forum Database Design
    Replies: 1
    Last Post: 02-10-2006, 03:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums