is it possible to modify the behavior of serial field - more specifically to change it's step and starting point (perhaps even change the current value of the field after the table is done i.e. 1,2,3,4,5... 66, change some parameter, 77,78,79...)

Also where can I get more info on what systemtables are meant for and how are they to be utilized

Naug

I assume you're talking about Autonumber fields?

The short answer is no. The longer answer is that you shouldn't want to do these kind of tricks with autonumber fields, as they are not there to provide meaningful data. The one and only use for autonumbers is to provide an automatically generated unique key for a new record.

If the data is meaningful, you have to use an integer field and populate it yourself.

The system tables aren't supposed to be utilized at all. They are there to give the Access space to hold various bits of vital housekeeping data. If you change this data, your database will probably become corrupted and unuseable.

Having said that, it is sometimes useful to write queries on these tables. For example it is possible to query one of the tables to produce a list of all the reports and/forms in your database. If you do a search through the p2p archives you should find some postings relating to this.


Brian Skelton
Braxis Computer Services Ltd.

so if I want to populate a field with meaningfull data do I have to do so explicitly (i.e. find the maximum previous number, add the step I need to it and put into, say integer field, or is there some way to create a serial number that I have control over?)

The reason Im asking is that in, for example, postgres a service table is created for each serial number which allows to make modifications I described in first post. I have solved my problem in similar maner - i.e. created a table where I put my counter and select/update it as needed, but I resently learned of systemtables and figured access provides similar functionality to postgres.

That's right - Access doesn't have this functionality.


Brian Skelton
Braxis Computer Services Ltd.

Hi,

   If you have a table the has an autonumber field, and it is set to increments of 1, then if you manually change the number from 66 to 77, in this case, then the next numbers it will automatically insert are 78, 79, 80, etc.

   If you need to make a jump in your autonumber, for some reason, then manually bumping it like this will reset the sequence.

   I agree that you shouldn't want to change a PK. It should not have significance.

   If you need to generate a case number, you can do this with regular expressions, or with VBA.


mmcdonal

Does something like this help? I use it to generate sequential invoice numbers which cannot be done by autonumber because the invoice numbers go in a sequence for each client, not for the invoice table as a whole. Of course, you would need to change the +1 to +something.

=IIf(IsNull(DMax("[InvoiceNumber]","Invoices","[ReturnID] = Forms!frmMainForm!Text33")+1),1,DMax("[InvoiceNumber]","Invoices","[ReturnID] = Forms!frmMainForm!Text33")+1)

Clive Astley