Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Exclamation Unanswered: Make your own Keyfields

    So, I just read another post about autonumbering. I changed my Keys to just plain numbers. The problem I see with it is that I am making forms for an assistant to fill in the info. Is there a way to make the key field "like" an autonumber but still be able to change it. I am making a database for all the employees here. I would make our badge numbers the ID but not every employee doesn't have a badge number.

    I have tried [FieldName]+1 in the default, but it says unknown field.

    Thanks
    Ryan

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'd have to do that programmatically.. I don't know if access supports triggers or not...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Can I get some help. I don't know VBA...

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by rguy84
    So, I just read another post about autonumbering. I changed my Keys to just plain numbers. The problem I see with it is that I am making forms for an assistant to fill in the info. Is there a way to make the key field "like" an autonumber but still be able to change it. I am making a database for all the employees here. I would make our badge numbers the ID but not every employee doesn't have a badge number.

    I have tried [FieldName]+1 in the default, but it says unknown field.

    Thanks
    Ryan
    My ID field in my maintable is produced by a SetValue macro that sets the value of this field with the autonumber. In my case the SetVale macro runs on LostFocus with a fields that has to be filled in when a new record is entered.

    Whe I enter a bulk of records then a form is opened for all the records that don't have an entry in "my namenumber field" and then a SetValue macro runs through all the records and sticks the new autonumber value into my field.

    Mike

  5. #5
    Join Date
    Dec 2003
    Posts
    268

    Example

    dim i as integer
    i = dlookup("MAX([your ID Field]","[Your Table Name]")+1

    You will have to put this on some event prior to saving the record. i.e. where the user enters in the first bit of data.

    Just a suggestion.

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    My ID field in my maintable is produced by a SetValue macro that sets the value of this field with the autonumber. In my case the SetVale macro runs on LostFocus with a fields that has to be filled in when a new record is entered.

    Whe I enter a bulk of records then a form is opened for all the records that don't have an entry in "my namenumber field" and then a SetValue macro runs through all the records and sticks the new autonumber value into my field.

    Mike
    Can you say where to put it?

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Totally Lost

    Quote Originally Posted by mjweyland
    dim i as integer
    i = dlookup("MAX([your ID Field]","[Your Table Name]")+1

    You will have to put this on some event prior to saving the record. i.e. where the user enters in the first bit of data.

    Just a suggestion.
    Can you give a bit more detail?
    Thanks

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by rguy84
    Can you say where to put it?
    Well, that's the problem.

    If you're doing this stuff from a form, it's no problem to add the VBA. If you intend to allow access to the raw tables, then you'll hvae to figure out if access allows triggers.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And then again ... For those of you who are bent on ADO (myself included), here's a means for assigning (re: retrieving) the next unique number ...

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

  10. #10
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Where will that go?

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by rguy84
    Where will that go?
    Question is: what are you building? How are you building it - or - how do you want to build it?

    The code EXAMPLE I used can be put into a form or in a module to be utilised ... Also, the function will need to be changed to reflect the intended applications data table structure and design ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by M Owen
    Question is: what are you building? How are you building it - or - how do you want to build it?

    The code EXAMPLE I used can be put into a form or in a module to be utilised ... Also, the function will need to be changed to reflect the intended applications data table structure and design ...
    I am building a database for my job. Starting off with just over 30 people but will expand up to ~100. I have 3 tables:
    Employees
    -----------
    EmployeesID (PK)
    LastName
    FirstName
    Position
    Team (FK)

    Team
    -----
    Team (PK) --There is about 6 teams

    Information
    ----
    InformationID (PK)
    EmployeesID (FK)
    about 10 other misc fields

    I want all the keys to match. sometimes it does sometimes it doesn't

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, I have a silly question. What's the problem with using the Autonumber?
    It sounds like the Autonumber field will accomplish what you want, so why invent something new to do the same thing? As the old saying goes: Why reinvent the wheel?

    Perhaps you should have an Autonumber field and also your secondary key field, if that better fits your needs.

    One important feature of the Autonumber is that in a multiuser environment, when more than one user creates a record at the same time, you are assured they each get a unique number becasue Access assigns the autonumber when the record lock is requested not when the data is updated. All the other fields are either null or default UNTIL THE RECORD IS UPDATED . You may have noticed that if you start to enter a new record, but then press escape, the "number" that Automuner assigned is consumed - even if the new record was cancelled. This is so that if somebody else initiated a new record during the 2 seconds that you were looking for the escape key, they get a unique number (just in case your escape key is busted and you keep your new record).

    If you are creating your own "Autonumber" in a multi user environment, there is a chance that two records created close enough together get the same number, no matter how fast you update the record to save the number.

    As they say: KISS "Keep It Simple Silly" !

  14. #14
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Yeah, I kind of gave up on the whole autonumber thing. I am a neat freak and I just like to have order. I just hid those fields. I have made my tables link correctly so I open the main table and the second table is linked correctly.

Posting Permissions

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