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!!
"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. ?????
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.
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.
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
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!
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
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.