When opening Form auto generate Next number in field from SQL List

[RWJ_2006]

Hey I am REALLY new at VB and teaching myself as I go. I have run into an issue and not been able to find a solution so far. What I want to do is when I open my new work order form the form generates the next WO number. I have already setup my SQL column as Identity and Set the increment to 1 and Identity seed to 1000. I linked the text box to the WO column but I don't know what code to write so that when it opens it pulls the next WO number.

Thanks for your help
Ray

[jmcilhinney]

The reason that you don't know what code to write is because there is no code to write. The ID is not generated until the record is inserted into the database. If you're using a DataTable then it will generate a temporary ID that may well be the same as the final ID but you cannot rely on it being so and should therefore never use it as anything other than a temporary value. It's there more to support foreign keys than anything else.

[RWJ_2006]

Dang I was hoping there was a way to get it to do this so that the person making the new entry could see the WO number before they saved and closed the form.

[si_the_geek]

I understand why that seems like a nice idea, but it has a bad side that means it isn't really worth it.

What happens if they write the number down and use it, without actually saving the record?

The number will be wrong (refer to a different record), so there is a very good chance they will accidentally cause the other record to be edited when it shouldn't be - thus causing data errors (for both 'copies' of the record).


There is also the issue that if two people are in the process of adding a record at the same time, do they get the same number? (even if they don't, what happens if the record with the lower number is not saved?)

[jmcilhinney]

Dang I was hoping there was a way to get it to do this so that the person making the new entry could see the WO number before they saved and closed the form.

There is an option that makes it appear to work the way you want from the user's perspective but requires a bit of a hack under the hood. You could save the record as soon as the user indicates that they want to create one, in which case the ID will be generated straight away. The user can then enter their data and when they save the record in the UI, your app will actually update the existing record rather than insert a new one. In that case though, you will have to allow nulls in all your columns in the database because the record will be completely empty when you insert it. You will also have to ensure that the record gets deleted if the user closes the Add dialogue without saving the record. I wouldn't recommend that course of action unless you absolutely MUST have the ID before saving the record.