Customize incremental ID field (1 Viewer)

[xaysana]

Hi there,
I am sorry if i posted this in missed category.
I have update form "frmpro" in this form, I have 3 columns "ProducerID, Producername and Villagename". I use these to generate a serialno. Here is my functional code.

=Format([cbproducer].[column](0),0) & Left([cbVillage].[column](1),2) & Left([cbproducer].[column](1),2)

Here is the result:
ProducerID Village Producername
1 Do To
2 Ri Ju
3 To Ad

There are few producers each have unique ID number. for example:
1. Tony
2. Julie
3. Addy.

I also have a table "tblProductions". Fields: SerialID, SerialNo, etc...What i like to happen is that each time when i click a button to update tblProductions, it should populate as:

Concat: ProducerID & Villagename(First2letter) & Producername(First2leter)
1DoTo0001
1DoTo0002
...
...
1DoTo9999

2TiJu0001
2TiJu0002
...
...
2TiJu9999

3CaAd0001
3CaAd0002
...
...
3CaAd9999

They should be filled into SerialNo field.

Can anyone help please?

 

[Galaxiom]

This topic has been covered at great length already.
Search the site for "custom autonumber"

This can be done in Google:
"custom autonumber" site:www.access-programmers.co.uk

 

[xaysana]

Thanks Galaxiom,
I found it http://www.access-programmers.co.uk/forums/showthread.php?t=179108

But i still can not work out.
I feel i need something like:
Search ProducerID for unique ID then search for last record (Dmax) after that add one more record after.
Do you any good sample for newbie like me to follow please?
Thank you in advance

 

[Galaxiom]

Put the number part of the serial code in its own field.
The DMax can include criteria.

For example:

DMax("NumberField", "tablename", "ProducerID=" & Me.cbProducer & " And VillageID=" & Me.cbVillage)

 

[xaysana]

Put the number part of the serial code in its own field.
The DMax can include criteria.

For example:

DMax("NumberField", "tablename", "ProducerID=" & Me.cbProducer & " And VillageID=" & Me.cbVillage)

Thanks mate for useful hint. But i could not apply to my project. I think some thing with my database structure. can i send an attachment of what i've been doing so that you can commend it please?
Cheers,

 

[Galaxiom]

Remove the objects that are not relevant to the issue.
Cut the data down to a few dummy samples.
Compact and Repair.
Zip it.
Post zip on this thread.

 

[vbaInet]

I don't see why you are saving those values in the SerialNo field. Why not just concat it on-the-fly by using a DLookup to pull the fields?