Creating an Incrementing Work Order Number

[TriniGal]

Hello,

I have a database that tracks leak issues for my company. Everytime that an issue is not resolves, a work order is created.

At first I was using the Autonumber field as a PK and to generate the workorder numbers. If the leak issue wasn't fixed right away, I would just copy the leakID and put it as the workorderID. After stumbling upon information about AutoNumbering, while researching another topic, I found out that autonumbering is not the way to go, if you want the numbering to mean something, and there is no order is something gets deleted.

There is a found date, repaired date, and RWO#(requested work order) for each leak. I need for everytime that the repaired date field is not filled in (meaning the leak issue wasn't resolved), for a RWO# to be created automatically or maybe I was thinking have a button, that when clicked would create a RWO#. I need to have it incrementing, not by how many leaks are entered, but by how many leaks are not repaired. Also, how would I save this RWO# that is created back to the table?

At first I tried the DMax Function, but that created a RWO for each leak, no matter if it was repaired or not. I can't think of another solution.

I thank you in advance for you help, as I'm new to all this VB stuff.

 

[Lilliabeth]

Since you aren't much for VBA, you should look into using macros.

Here is a sample:

http://www.dataschenk.com/newsletters/Tips/AcSetValue.htm

You will need to create a variation where there is a condition:

[DateRepaired] is null

There is a Conditions button on the toolbar that will produce a column for the condition.

You could even add an action (Msgbox) to inform the user that a workorder number was created.

 

[Pampers]

I think the problem here is, when to use Dmax. Only if a repair date is not filled in. So you need something like

If IsNull(repairdate) = true then
RwoNo = Dmax("field", "domain") + 1
End If

Next step is to decide what triggers this code. You might consider the AfterUpdate-event of the form.

Pampers
Just let it go...

 

[Lilliabeth]

Pampers,

What are you disagreeing with?

I think the problem here is, when to use Dmax. Only if a repair date is not filled in.

Like I said:

You will need to create a variation where there is a condition:

[DateRepaired] is null

 

[Pampers]

Hi Lilliabeth,
With nothing really. Just wrote in down in VBA if he is not going to use a macro. And I pointed out that what kind of event he can use to trigger the code.

Pampers
Just let it go...

 

[Lilliabeth]

And I pointed out that what kind of event he can use to trigger the code.

Do you think it matters whether you use the Before Update or After Update event of the form?

 

[Pampers]

Sure, there different events. If you use AfterUpdate, it assings a new value after every update (which can cause an unwelcome renewal of the WorkOrderNo). BeforeUpdateEvent might be a better option, or mabye the BeforeInsertEvent. It all depends what suites the designer the best.

Pampers
Just let it go...

 

[Pampers]

And maybe you want to use the following code to handle the first addition in an empty table:

Nz(DMax("[InvoiceNo]","tblInvoice]"),0) + 1

Pampers
Just let it go...

 

[Lilliabeth]

Oh, sorry, I was not clear. I know there are different events. Since you said that you had pointed out what kind of event to use (After Update) and the link I had supplied used Before Update, I thought maybe you felt the After Update event would have been more appropriate. But I see that is not the case. Thanks.

 

[Pampers]

I think the AfterUpdate event could be usefull if it is placed on the control of RepairedDate itself. But changing the RepairedDate would insert a new WorkOrderNo, which is something to developper may or may not want. The other two events are on the FormControl.

Pampers
Just let it go...

 

[TriniGal]

Thank you both for your suggestions. I will try them out now, and let you guys know as to the outcome. I will keep you posted.