noob Dmax+1 frustration

[Laplandz]

Hi all, i am struggling to make what i thought would be a simple DMax +1 auto entry on a form, what i have, put simply is:

1. a JobSheet table with a field called JobNumber (its a LongInt.. number field with no other settings) and cannot be autonumber'd as that is assigned to the PK to reference multiple other tables/queries etc (which seems to be working )
2. a NewJobForm which needs to have a new JobNumber every time some-one starts to book a job in (incremented by 1), its extremely important that the number is generated before any other details are entered as the engineer will need this info for the paper copy of the job sheet prior to "booking in".

can some-one tell me or point me in the right direction to what the correct "expression builder" format should be and which place to put it in the form property sheet as i've tried many, many attempts with constant errors...

hope i've explained this well enough...

thankfully yours

Luke Aplandz

 

[John Big Booty]

Welcome to the forum.

Have a look at the On Current event of Form1 in the attached sample.

 

[gemma-the-husky]

the trouble with picking the number at the current event is that two users working simultaneously will get the same number.

The alternative, of picking and updating the number from a separate "next number" table - gives the opposite problem. Each user gets a unique number - but someone scrapping an order then "wastes" a number.

The ONLY safe way, imo - is to allocate the number IMMEDIATELY before the stage when the work is committed and will not be rejected - which is therefore the forms before update stage.

An alternative is to store the active work in a temporary table, and append it to the real table at the last minute.

EITHER way, you dont get the order until the last minute.

So the question is, wrt your business process - is WHY do you think you need the order number at the START of the process? The time to get it, is really at the end.

If it is that important then have an order book, and get your engineers to manually get a number in sequence from the book. Then they can infill gaps.

 

[missinglinq]

In line with what Dave has said, the only real way to do this with the number being available immediately and adequately insuring only one user gets a given number is to generate the number and then immediately force a save of the record.

This is easy enough to do, but it does make dumping the record, if the occasion should arise, to be problematical. The record would have to be deleted, rather than simply 'undone,' which will result in missing numbers.

 

[Laplandz]

firstly WOW, you folk don't mess about here, many thanks for such speedy replies, extra kudos to John for the example.
i'm very new to Access and databases in general and was hoping to be able to use expression builder over coding, i've tried to self-educate myself by doing an hour or two a day over the past week (balancing work, social and toddler daughter not easily done!!) and through research for other stuff i've realised i'm gonna have to learn it if i want to "do" access properly.
also after talks to the boss he is happy for me to "do as i like" with regard to our database and thus removed all strange requests (personally i reckon he was trying to make it more difficult then needed) this means the job number can be given at the before_update point.

again many thanks for the remarkable speed in answering, i was dreading being flamed as i see this question has been asked multiple times before here but i couldnt find a post that referenced using the expression builder over code. i've book'd marked this site and am sure it will become my main point of contact for those things i'm unsure about.