numbering

[LWCARAB]

Hello,

I have one main table containing most data, that table stores data which is identifies by a unique ID (currently 1-96000), there is also an issue number, I need another numbering system per issue (so you could have issue 166, record 26), any ideas?

 

[gemma-the-husky]

just add columns for issue and sequence - when you add a new record you will have to lookup the appropriate values and set them to what you require

i assume your current number is an autonumber - all that is, is a unique identifier to enable you to relate that row to other data in your database. You wont need to, and shouldnt change this. If you need to look up your issue and sequence numbers, you can do so via the existing reference

 

[LWCARAB]

the issue is worked out using a separate table, each month of each year has a preset issue number. I have created a new table column for the new number but how do I get it to enter a number each time I add a new record?

 

[gemma-the-husky]

depends how you are adding new records

if you are using a form, create a txtbox for the issue number, and in the form beforeupdate event, set the issue number to the required value. This textbox can be hidden if you like.

you probably want some extra code to make sure you only do this with new records, as otherwise you might change values for existing records

 

[LWCARAB]

The records are been input via a form, I can put a hidden textbox that finds the issue number based on the date but I still have the article number problem as I need to store them for previous issues but they must start at 1 each month so that rules out an autonumber field.

 

[gemma-the-husky]

when you evaluate the issue number, just find the highest number issued for the month with a dlookup, and add 1 to it.