Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2006
    Posts
    30

    Unanswered: Field default using ID in Access 2007

    I am setting up an existing Access 2007 database on Sharepoint which assigns a new ID field. Problem is all our databases have a 6 or 7 digit ID which match a claim file number. We tried to use the ID field + a 6 or 7 digit number as a default (= [ID] + 600000) in the claimID field that updates when a new row is created but Access reports an error "the field [ID] does not exist etc." I have tried =Nz(DMax("ClaimID","Claimant",""),0)+1 and =Nz(DMax("ID","Claimant",""),0)+1 as a default. Both result in a error message that "Nz" is an unknown function. Help!!

    Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nz an unknown function? First thing I would do is check your references... go to any code window and Tools - References. My guess is there is something missing or out of order there.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2006
    Posts
    30

    Field default

    the complete error message is:

    "Unknown function 'Nz' in validation expression or default value on 'Claimant.ClaimID'" Tool - References was no help.

    Any ideas how I can create a second field that will autonumber using any starting number? I thought I could do it based on a reference to the ID field but I think the problem maybe that the ID field is not fully created for a new record so Access will not allow a default based on the ID field for another field. ?????

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Where is it exactly that you are putting this Default Value? In the table design? A form text box control? Code?

    I am assuming you are doing this at the form level. You won't be able to use a Default Value in a table that refers to any other field. You can control such things with forms and code though.

    Nz is not an unknown function though... so that error message is still a bit of a concern. You might like to verify it's working by attempting to execute this SQL:

    SELECT NZ(Null,"It works") AS NZTest;

    I'm know there is an easy way to create an AutoNumber field that starts off at 60000 or so too... but I never do it, so I can never remember how. Offhand, I'd say that you could create an AutoNumber field in a table and then import a record that has the value 59999 as an entry for that AutoNumber field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jul 2006
    Posts
    30

    Field Default

    I know how to create an autonumber field starting with any number that is not the problem. When you publish a table to sharepoint it creats a new autonumber ID field starting with 1. Sharepoint will not use the existing autionnumber field starting with a 6 or 7 digit number. So I need another field to create this number with each new record because it is the tracking/file number used in the office.

    This is a field default in a table. I can create a default value that is a date or text string but cannot create a value calculated using another field like the ID field + 600000.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks, that clears things up

    BTW, you can't create a default value in an Access table that refers to other fields either.

    You're going to have to do this at the form / coding level, not the table level.

    If you need to have the data table updated for existing records, then you should use SQL to update the new field with the ID + 600000.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jul 2006
    Posts
    30

    Field Default using ID

    Thanks StarTrekker. =Nz(DMax("ID","Claimant",""),0)+300060 works in a form after the first few are created. It produces dups for a couple of rows then it works. Odd!!! Also odd, to me at least, that it will not work at the table level. I was trying to avoid programing about 20 forms and I am concerned that if a new row is created in a query that it will not produce the a value for the ClaimID. Now I need to see if the form solution works when I publish to Sharepoint. JG

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Best practice is not to let users add data with queries or tables. All data entry should be handled by forms.

    Sorry that you have now to edit 20 forms or so, but also consider this:

    There is actually no need to store such a field in the tables.

    Everywhere you show the ID, you can just add the 300060. The value itself doesn't actually need to be stored in the tables at all. That could mean the editing of every form and every report... yes, but that's probably what I would do if I absolutely couldn't just use the natural autonumber value for the ID. Why? To give freedom and clarity at the table level. You COULD then allow users to add data with a query... the ID stored as an autonumber is fine in such a situation. And it also avoids "weird" things going on!

    You're welcome btw

    Just glad to see you got over the hurdle
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jul 2006
    Posts
    30

    Field default

    The reason I need it to be an actual entry in the table is because there are thousands of pages of documents with the ClaimID as the main reference. Also they have a scan barcode that uses the ClaimID. If the table is removed from Sharepoint and later published again, sharepoint will generate a new and different ID numbers. This may change with upcomming updates to the Sharepoint/Access interface but for now that is the way it works. Have you worked with Access on a Sharepoint site? I am looking for someone to design and setup an Infopath form or Access form on a Sharepoint site. Jim

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, I haven't dealt with SharePoint yet. By the sounds of it, I hope I never will. If it forces the re-creation of PKFs I'd be swearing at it too much.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jul 2006
    Posts
    30

    Field defaut

    This and other issues are minor when compaired to the features Sharepoint provides. The way we do the record ID is not recommended and Sharepoint just forces us to do it the recommended way. We like it so well that we are moving most of our new projects to it. It allows a project to be centrialized on the web. Not only database (Access or SQL) but documents, spreadsheets etc. On a recient project our costs were cut in half because personnel in 5 remote locations were able to connect to the same database through the web.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    InfoPath is easier than Access to build SharePoint forms - or so I'm told
    George
    Home | Blog

  13. #13
    Join Date
    Jul 2006
    Posts
    30
    We have InfoPath and have tried to get it to work without success. I need a pro to help set us up the procedures.

Posting Permissions

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