Increment Field Value

[Malseun]

hi all !



i have 2 field "CLIENT ID" and "CLIENT CODE". when i post a new record, i want the "CLIENT ID" and "CLIENT CODE" to merge to give me 1 field "CLIENT". the CLIENT CODE should be prompted for eg. THO or BLT and added to the CLIENT ID which will compose of 001 or 002. The CLIENT ID should also increment when each new CLIENT record is posted.

The example should make things clear.

CLIENT ID CLIENT CODE CLIENT
001 THO THO001
002 THO THO002
001 BLT BLT001

it should look up the last CLIENT value and then for the new record just continue on the previous entry.

Hope it makes sense!
Thanx!

[Rainlover]

You need to use the Function "DMax" then add one.

Google "DMax Plus 1" for help.

If no luck then give it your best then post back with what you have.

[Malseun]

Thanx Rain, i will quickly have a look and get back to you!

[Malseun]

The function will work, but my problem is i need to use the DMAX function to look up for the Max value in a field created in a Query that merged the Client ID and Client Code, so i can't use the Table reference as needed in the DMAX format NR: Nz(DMax("NEWCOL","CLIENT"),0)+1 "NR" will go lookup the Value in the NEWCOL which is the merging of CLient ID and Client COde... but it returns "1" cause the NEWCOL field in the CLIENT table is null... does it make sense?

[Rainlover]

There may be no need to merge.

Once you have the incremented value you can always show the merged value by concatening the two values in the Query.

Or if you must store the value which is really of no purpose (Just my opinion) then it could be saved on the Form Before Update Event.

[Malseun]

could you please help me with the coding for that increment field ? i have no idea how to find the latest value and then having it stored in NEWCOL

[Malseun]

im terrible at these detailed coding things...

[Rainlover]

I thought you were doing a good job.

I need to find a Sample Data Base that I wrote some time ago.

Will get back to you ASAP.

[Malseun]

Thanx Rain, no i am just struggling to get my head around it... situation is, we have a current CLIENT database, now we want to post new bills under a new field called CLIENT ID... this field should include the Client's surname (first 3 letters) followed by a autonumbering of 001 , 002 etc. But each time you post for eg, Thomas, it should return THO001 ... then when you post Thomas again, it should return THO002... but then if you post Silver it should return SIL001 ... that is my dilemma... that is what i need... but the coding behind it, makes me go boonkers!!! haha

[Rainlover]

The attached is in 2003.

Good Luck. It is only a little complicated but I am sure you can work it out.

Just go to the code and search "Max" to find the applicable code.