473,287 Members | 1,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Group serial number

5 2Bits
"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
Mar 5 '23 #1
3 19231
NeoPa
32,554 Expert Mod 16PB
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.
Mar 6 '23 #2
Bright1Light
5 2Bits
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
Mar 6 '23 #3
cetpainfotech
15 Byte
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.
Mar 23 '23 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Niy | last post by:
what does that mean? what for? I searched a lot but failed to find explanation. Sometimes I really have difficulty finding documentation for some views.
15
by: tom | last post by:
Hi, How do I get the serial number of the harddisk in .NET? I want this to be the same number even if the user has reformatted, so I do not want the volume serial number. Thanx, t
5
by: | last post by:
Hi, Do memory sticks have serial numbers like harddrives? If so how can I get this, I want to uniquely identify a memory stick (removable drive) for authentication. Thanks
79
by: Klaus Bonadt | last post by:
In order to protect software from being copied without licence, I would like to use something like a key, which fits only to the current system. The serial number of the CPU or the current...
3
by: Mark Harris | last post by:
I have an installer which uses a Customer Information page in it, is there an easy way to pass the serial number entered to a custom action? If not, where would i find the serial number in the...
3
by: Jefe | last post by:
Hi group I'm trying to implement a console application which work just lie "Dir" instruction in MS-Dos prompt window so I need to display Volume Serial Number and also the free space, how can I...
14
by: Lauren Wilson | last post by:
Discovered this interesting comment on MSDN: "To programmatically obtain the hard disk's serial number that the manufacturer assigns, use the Windows Management Instrumentation (WMI)...
9
by: Nebojsa4 | last post by:
Hi. First, sorry on my weak English to all. Qusetion: How to read (in VB) Manufacturer serial number of Hard disk drive? Not volume/serial number of C:, D:, etc. partitons. For reading...
1
by: Keshia | last post by:
I have a database that is for keeping track of inventory. I need to have my form for entering new inventory to automatically make a new serial number based on the category of the new item. The...
7
by: mike1hello | last post by:
I have a warehouse database. I have created a query which searches the table for duplicate serial numbers and give me a warning message, that works. My Code Private Sub...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.