Increment number

[Heidebloempje]

Hi all,


I have a salescontract database with contractnumbers being filled in by hand. It happens sometimes that clients have signed a contract and later on decide to order a new service relating to the existing contract. For that new service a new contract is made.
Now users give the new contract a number that looks like the related previous contract so some wise guy should be able to figure out the relation.

We now want this to be more clear and be able to see the order of which the contract has been sold.
I was thinking of adding two new colums and make a incremental order for each client.
For example I was thinking of this:

Contractnumber Contractincrement
NIBG0001 1
NIBG0001 2
NIBG0002 3

So, when a user decides the new contract is related to a previous one, the Contractnumber field gets the Contractnumber of the previous one and the Contractincrement field is incremented by one.
Hence, if a new contract is not related the Contractnumber should increment.

Am I thinking in the right direction here?
If so, how can I make these kind of Contractnumber ranges since the examples are for client NIBG. So other ranges should be made for other clients. (Like NPO0001)

And, ofcourse, how can I implement the incremental value of the Contractincrementfield when the user enters a related contract in a form?

Thanks a lot in advance.

 

[Addyman]

Dim intIncrement as integer

intIncrement = Nz(DMax("Contractincrement","YourContractTable","Contractnumber = '" & MyForm!MyContractNumberField & "'"),0) + 1

OK, the above is pretty basic but should get you started. When a User either enters a Contract Number, or tries to save a record (you can decide when to trigger the event) the code above should get your next Increment Number. It looks for the Max Increment Number dependant on your Contract Number and adds 1 to it. If the Contract Number doesn't exist in the table, the Nz function will 'set' it to 0, and then add 1, so it will always start at 1.

P.S. You will need to edit the code above to match your table names, form names and form control fields.

 

[Sketchin]

It happens sometimes that clients have signed a contract and later on decide to order a new service relating to the existing contract

What is the reason to make a whole new contract? Could you just have a revision number?

 

[John Big Booty]

The attached sample shows a method you could use.

 

[Heidebloempje]

Thanks all!

I don't know why the sales department stores it as an extra contract, but they do.

I don't care how to fix this, as long as it shows that contracts are related to each other. So I need a way to store the contractnumber and use it more often and have a incremental value (or revision number as Sketchin suggests) and store that too in the contracttable. But only for contracts that are related and automatically. The only thing the user must do is determine if contracts are related.

it should be fairly simple I guess, but I don't see it.
If I simplify my initial post it could just be something like:

Contractnumber      contractversion
100                          1
100                          2
101                          1
200                          1
200                          2

But I want the user to click on something in a form like a checkbox to determine if the contract relates to, lets say contract 100 and then the contractversion of this contract will become 2.

Does that make any sense?