Multiple Tables in Access

[bader]

I need some assistance with a format in Access.
I need several tables to use the same pool of autogenerated numbers (badge numbers) and automatically fill that value upon each record generation. I have found that the NZ(DMax("field name","table name"),500)+1 command works fine from a FORM...not the table itself....as far as auto generating a number.
But I need an autogenerated number that is consistant from the pool to be available and filled for each individual table which represents the department that the badge comes from.

I can't get a table to "see" the value from the pool or fill that value.

I can't get the auto number command to work from the table itself.... it needs to because I have a custom application that doesnt interact with a form created in access.

TIA for any help you guys can provide
Byron

[George Oro]

An AutoNumber field type only will do. If you want some sort of formatting of autonumber then it should pass to an interface.

George

I need some assistance with a format in Access.
I need several tables to use the same pool of autogenerated numbers (badge numbers) and automatically fill that value upon each record generation. I have found that the NZ(DMax("field name","table name"),500)+1 command works fine from a FORM...not the table itself....as far as auto generating a number.
But I need an autogenerated number that is consistant from the pool to be available and filled for each individual table which represents the department that the badge comes from.

I can't get a table to "see" the value from the pool or fill that value.

I can't get the auto number command to work from the table itself.... it needs to because I have a custom application that doesnt interact with a form created in access.

TIA for any help you guys can provide
Byron

[nico5038]

Your only option will be to use another database (eg MS SQL, MySQL or Oracle) that does support code (Stored Procedures) to be executed when an INSERT (or other action) on the table is performed.
The Jet-engine of Access won't support this.

Nic;o)

[NeoPa]

Another possible alternative would be for your application to handle generating the new key field upon creation of new records.