Group serial number

[Bright1Light]

"I'm not programmer but I'm interested to work on Ms Access"

I have table on that table there are many cities I want each city to have unique serial

E.g.

New York city, serial start N125 then serial like this N125-0001 next N125-0002 and forward

Then Another city

Florida start F352-0001 then F352-0002

To explain I have field include cities and field for each city have unique code e.g. (New York code is N125, Florida is F352)

I have form I want when I choose city from combo box it run automatically serial of this city on another text box

So now my form I have 3 fields
City (combo box)
Code (auto get when choose city)
Serial (which I need to auto get)

Kindly help me with detail expression and where to put exactly

I found some solution from previous answers but maybe I don't do it right so please explain me the expression and where to put it

Thanks

[NeoPa]

It's hard to know what you want. You seem to be asking for the whole thing to be done for you - which is not appropriate in case you aren't aware.

In principle you need to design a database that has a table for your States where each record has at least the code for the State as well as its name.

You would also require a separate table that holds Cities. This would also need, at least, speces for the name and the code.

From there it's not clear what you intend. Do you expect to add all the cities in some batch process somehow? Do you plan to make a sysytem that allows operator(s) to add cities to the table based on a Form?

Alternatively, what are you asking for? A clearer explanation would be helpful, but remember that the request must fit within the rules, so one simply for work to be done all for you is not acceptable.

[Bright1Light]

Simply
Now let me give example
I have 2 tables
One table have 2 fields for
[ID] [City] [code] (number in code not related to anything
1 New York N256
2 Florida F352
Another table
[ID] [Supplier] [Amount] [City] [code] [group serial]
1 blabla 4321 New York N256 N256-001
2 blabla 5678 Florida F352 F352-001
3 blabla 5467 New York N256 N256-002

I have form for this table include
[City] (combo box called "CCity")
[code] (text box "TCode"get auto value after update "CCity")

What I want is to have
Text box for [group serial] called "TGS"
that also auto give serial based on "TCode" value which based on "CCity"
On that
If I enter New York I get N256-001
If enter Florida get F352-001
another New York get N256-002
So on

Thanks for help

[cetpainfotech]

Here's an expression you can use in the "Serial" textbox control's Control Source property to generate the unique serial number:

=DLookUp("Max(Serial)","YourTableName","City='" & [City] & "'") & "-" & Format(DCount("*","YourTableName","City='" & [City] & "'")+1,"0000")

Replace "YourTableName" with the name of the table that contains the data for your form, and replace "City" with the name of the field that contains the city name in your table.

This expression first looks up the maximum serial number for the selected city from the table using the DLookup function. It then concatenates the city code with a hyphen separator, and appends a counter value that is obtained by counting the number of existing records for the selected city in the table using the DCount function.

The Format function is used to pad the counter value with leading zeros to ensure that it is always four digits long.

To use this expression, simply set the Control Source property of your "Serial" textbox control to the above expression, and set the Row Source property of your "City" combo box control to a query or table that contains the list of cities and their corresponding codes.

Note that this expression assumes that the first serial number for each city should be in the format "N125-0001" or "F352-0001". If you want to start the counter at a different value, you can adjust the second argument of the Format function accordingly.