Click here to Skip to main content
15,436,765 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i built one program but facing problem what i try to do is when i select items from combobox eg. Primary vertical(PV), secondary vertical(SV) etc. next textbox will auto take serial no. like PV-01/today_date, next entry will be PV-02/today_date and so on and when next day again i try to add entries serial no. started from PV-01/.....
I AM USING VB 2010 with access database for this.

What I have tried:

dont know what to do i am new in vb
Posted
Updated 1-Sep-21 23:05pm
Comments
CHill60 1-Sep-21 3:36am    
There is insufficient information here for us to help you. As you are using Access do you have an Autonumber column on your table - that will automatically increment as you add new records. Add the "PV-" and date in the UI
GOBIND KUMAR SHARMA 1-Sep-21 10:46am    
in access database id column is there but i want another column which is serial no but serial no is in form PV-01/01-09-21, PV-02/01-09-21 and next day again started from serial no which is PV-01/02-09-21
Maciej Los 1-Sep-21 14:48pm    
Does your database is used in multi user environment?

1 solution

This is not a good design, because anything you try to do will not be guaranteed to work in a multi-user environment. By this I mean that you are essentially counting the number of rows entered today so far, and adding one. But what if another user is inserting a record at the same time? You will get two identical references.

You might be tempted to "capture" somewhere the last auto-number id of the previous day and subtract that from the auto-number generated for each record - but it sounds like you want the numbers to be sequential, and auto-numbers are not guaranteed to be that, so this would also be a bad design decision.

Consider - how important is it that you store this "id" in this format? If you need to know how many records have been added in a day then query for that information rather than recording it.

Is it important that it is done immediately? If not then consider updating the previous day's records with a sequence - this will work for a multi-user environment (assuming only one user can run the update job) because you can't time travel to add new records for that day.

The only other solution I can think of is that you have another table that will be used to generate the sequence which you can reset at the beginning of each day. But that is even clunkier

By far the better solution is to just use the auto-number generated automatically when you add a new record, and capture the date the record was inserted. Then you can format that anyway you like. Just accept that there can be gaps in the sequence and it won't restart daily - don't try to combine MI into a persisted data item.
 
Share this answer
 
Comments
Maciej Los 2-Sep-21 7:00am    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900