how to generate a unique key in a form linked to an excel table 1

[ronrsr]

How can I generate an unique integer key?

I can't make it an autokey field, since it's a linked table -

is there some way I can generate a unique key, say, max(other_ids)+1?

thanks,

-rsr-

 

[RickSpr]

Do you need the to store the unique integer on the spreadsheet? If so, you might try putting a counter field elsewhere on the spreadsheet and using an Excel macro to generate it before you get to Access.

If you only need it within Access, maybe you could use an Append Query to copy the spreadsheet data to an Access table that has an Autonumber field as its key.

Just how unique do you need it to be? Does it only have to be unique within the spreadsheet you're currently linked to, or across all spreadsheets that you might ever link to, or what?

I think you're going to have to give some more information about how you're using this linked table. Rick Sprague

 

[ronrsr]

the number need only be unique -- preferably the next sequential integer, but even that's not necessary --

If the db were stored in access, i would use autonumber, but it's in excel, and I'd love to be able to generate this automatically when a new record is created.

-rsr-

 

[RickSpr]

Well, you still didn't describe how you're using the linked table, but I gather you're letting Access add the records to it. I'll assume that Access is the only way records are added, so you don't need to backfill records that already exist but have no unique key.

The following expression returns a value one greater than the largest value currently in the SeqNbr field of a table called ExcelData:
DMax("SeqNbr", "ExcelData&quot + 1

You could use this expression as a field in an Append Query or in code that adds a record using DAO. Rick Sprague

 

[ronrsr]

can I use it in the DEFAULT property of a FORM element?

=rsr=

 

[RickSpr]

I think so. The Help file says that the Default Value property can be an expression, so it should work. You need to put an "=" sign in front of it to use it there. The only thing is, I'm not sure whether it will be calculated only once, when the form is opened, or each time you move to a new record. You'll have to try it and see.

Rick Sprague

 

[ronrsr]

thanks Rick, that worked really well.

-rsr-