Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    90

    Unanswered: Create Auto Number!!

    hello,

    I need suggestion from someone to create autonumber on form. I have one form which has one field that not assigned autonumber in the database design. But in that form I need the increase of number without user key in on it. ANy idea?

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Thumbs up

    Hi,
    IF your Table is designed Properly, your PrimaryKey would be what you would best set as Autonumber. Have you thought about my advice about ReDesigning your entire database??? Really would make your world a lot easier. Read any good books lately??

    BUD
    Last edited by Bud; 12-12-04 at 05:32. Reason: grammatical error

  3. #3
    Join Date
    Nov 2004
    Posts
    90
    Thanks for ur advice Bud,

    Based on ur idea I know that ur empowerment with system development is very high or cathegorized as an expert one. So, Hopefully u can advice more in system dev. especially using access to me my friends...

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by oasislah
    Thanks for ur advice Bud,

    Based on ur idea I know that ur empowerment with system development is very high or cathegorized as an expert one. So, Hopefully u can advice more in system dev. especially using access to me my friends...
    Whoa there.......though it sounds nice to hear, I am far from being any expert. There are many things I still don't know yet, but working at it each day to try to learn and get better at it. This is a good book to read to teach you the concepts of databases and later the design phase. Please, if you read this or ANY book like it, DO NOT SKIP TO THE DESIGN PHASE!!!!!!

    Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition
    by Michael J. Hernandez



    Relational Database Design: Clearly Explained, Second Edition
    http://www.bookpool.com/.x/pbx84hrb8i/sm/1558608206


    That's where so many fail in trying to get to the Visual part of it all....getting to the end but overlooking the beginning. I can spend days, weeks, etc. in the forum trying to teach you all this, but that as stated takes TIME. Here,(the forum) is just for little pointers for sticking points ONCE you know what you are doing with just a hiccup or two along the way. I'm not sure, but maybe someday this week I will go through your entire database again and try to break it all down for you into more digestable portions. Then maybe even re-structure your tables AFTER you understand what you need and how you might want to get there. I can't decide that for you. I suggest yet again, find a very good basic book to read on database design in general, BUT pay close attention to the beginning. Start at point A, then go from there. I am saying this to be of help to you. I know, it doesn't solve your immediate need (from your perspective) but logically you need to get it right from the beginning and as soon as possible. IF this is for your work, ask if they will either pay up front or reimburse you if you take something like a Continuing Education Course at your local college on Access. Barnes and Noble have Online Courses they offer for under $100.00

    NOW, having said all that, for an AutoNumber field....as suggested take your PrimaryKey field and set it to AutoNumber in your table. Setting it to that it will Automatically AutoNumber your field each time you add a new record. EXAMPLE: If you have a table for Employees, create something like EmployeeID as the PrimaryKey. Set it to AutoNumber. Then whenever you add a new employee, they will have a distinct number which differentiates them from others....called UNIQUIE. It will set it to NO DUPLICATES.

    NOW, if you were to Relate the Employees table to the lets say...uh..ummmm, Vehicles table. For the Vehicles table you will have like either VehicleID as the PrimaryKey set to AutoNumber, OR since all vehicles have a Unique ID anyway, you can use the VIN (Vehicle Identification Number) as your PrimaryKey.

    THEN, add the EmployeeID field to the Vehicles table, SET it to Number, Indexed, Duplicates OK. Now you have linked your Employees table to the Vehicles table.

    NEXT, to set the Relationship for your tables, you DRAG the EmployeeID in the EmployeesTable to the EmployeesID in the VehiclesTable and set EnforceReferantialIntegrity. You will see in that box at the bottom that is in a One-To-Many link. Meaning ONE Employee and have MANY Vehicles. NOTE: When you put the EmployeeID PrimaryKey into the Vehicles table, it is now referred to as a [b]ForeignKey{/b]

    I hope this helps you out some now, but I know you will have another problem soon because your structure is NOT in ORDER properly. So help yourself to do better by reading and learning some first. And as always, I say all of this in the friendliest of ways, as was taught to ME also.

    hope you and your friend work this out together and have a wonderful day,
    have a nice one,
    BUD

    btw, thanks for the compliment just the same I'm trying to get there

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    But then again Bud, there are those of us who do not like autonumbers and would rather generate our unique IDs ourselves using a table holding each ID incrementer and a bit of ado code ... (This is for the sad fact that with an autonumber it's assigned upon entry whereas with the custom it's assigned when the programmer wants to ... Usually upon the user saving.) Ex:

    Code:
    Function GetNextInvControlID() As Long
        On Error GoTo Err_GNICID
        
        Dim NextInvControlID As Long
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        NextInvControlID = -1
        
        SQLString = "SELECT [Next InvControlID] FROM BillingSettings;"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            NextInvControlID = TrgRecSet.Fields(0).Value
            TrgRecSet.Fields(0).Value = NextInvControlID + 1
            TrgRecSet.Update
        End If
        TrgRecSet.Close
        
        Set TrgRecSet = Nothing
        
        GetNextInvControlID = NextInvControlID
    Exit_GNICID:
        Exit Function
        
    Err_GNICID:
        
        MsgBox Err.Number & ": " & Err.Description
        GetNextInvControlID = 0
        Resume Exit_GNICID
    End Function
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Nov 2002
    Posts
    154

    Question What does that table look like?

    Hey M Owen, that code looks interesting. What does the table that you are pulling the 'autonumber' from look like? I have at least one instance where, because of replication causing autonumbers to be big, useless random numbers to have to make my own. I do it by doing a dlookup for the biggest ID and then add one to it and set the current ID to that, but yours looks promising for me. Is there only one InvControlID kept in your billingsettings table that you keep incrementing? Thank you in advance for your time and replies.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by feetdontfailme
    Hey M Owen, that code looks interesting. What does the table that you are pulling the 'autonumber' from look like? I have at least one instance where, because of replication causing autonumbers to be big, useless random numbers to have to make my own. I do it by doing a dlookup for the biggest ID and then add one to it and set the current ID to that, but yours looks promising for me. Is there only one InvControlID kept in your billingsettings table that you keep incrementing? Thank you in advance for your time and replies.
    Yes. In this table there is EXACTLY 1 record ... The column in particular has a type of Long ... So, you have up to 2 billion + before rolling over (overflow) ...

    Keep in mind that this table is where I keep ALL of my various unique numbers ... Just different columns.
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi M Owen,

    That is true as I have a database set up that way. But for simplistic reasons I was explaining it that way to oasislah because he is having some problems with the basics and not into the coding part just yet. As I have expressed to him from the beginning from examining his database, the entire structure is in disarray and should be reworked as soon as possible. Until he gets the basics of databases in general from the ground up I didn't want to put him into the Code Mode just yet. But your advice is excellent....just trying to tailor it to the user at hand.

    have a nice one,
    BUD

Posting Permissions

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