Serial Numbering in Access Form

Hi all,

Can you help me solve this

In my main table I have 20 separate entry for for serial number ie (Serial 1, Serial 2... Serial 20) (Data type in Number)

also

In my main table I have 20 separate entry for for quantity ie (Quantity 1, Quantity 2... Quantity 20)(Data type is Number)

My data entry form has both serial (1 - 20) and quantity (1-20)

I need serial number column to be auto inserted in form as I change value for quantity ie (Serial NO 1 =1 (Auto); When I change value for Quantity No 1; By default Quantity 1 value is =0

Serial No column must auto update itself if i delete a quantity ie (Serial No 2 =null, when quantity 2 = 0 & Serial 3 gets value AS 2 )

looking forward to a VBA or Control Source Expression solution

You must understand that when you come here asking about what you want to do, we have all seen people who wanted to do that sort of thing before. We know (from their own descriptions and from our personal memories of the mistakes we have made) when you are heading in a difficult and potentially unmanageable direction. Therefore this might come across as very harsh but it is not intended as a personal attack. It is merely directed towards what appears (to me) to be a really awkward design choice.

First, you DESPERATELY need to look up "database normalization" because the pictures you are showing us as picture1 and picture2 represent badly denormalized tables. You will fall flat on your face if you ever need to add enough columns to reach 256 of them - because Access won't go past 255. My immediate advice is to stop what you are doing and learn about this vital topic because otherwise, your data will become unmanageable (if it hasn't already). Access is NOT like Excel. What you are showing is what we call "Excel thinking" - which works great if you are doing this in Excel but will not work at all or will work very badly in Access.

To research database normalization, you can use this forum's SEARCH feature (in the thin menu ribbon/bar just under the place where the top of the page shows your login name.) SEARCH is 3rd from the right on that bar. If you search within the forum, you can simply specify "normalization" because this is a database forum. IF you search on the open web, you must specify "database normalization" because by itself, "normalization" could refer to mathematical, political, diplomatic, legal, and chemical topics. It's a popular word, so you need to qualify the search by adding "database."

Second, as a separate question, your requirement that the serial number must change when you delete a quantity to zero means you can't usefully use this serial number as anything but a temporary display field because it will have no other permanent meaning whatsoever. It is at most a temporary ordinal position. You need to consider what you are trying to do because this design choice will lead you down a confusing and potentially intractable garden path.

Thanks for your advice, i will surely check access data normalisations and re design my tabes.

But my question still remains how to auto number in an access form using vba, yes it must be a temporary calculated serial number as you said

sree4dream said:

Thanks for your advice, i will surely check access data normalisations and re design my tabes.

But my question still remains how to auto number in an access form using vba, yes it must be a temporary calculated serial number as you said

Click to expand...

Hi. After you understood database normalization and have redesigned your table structure, you can take a look here for some VBA examples on how to generate your own sequential number. Good luck!

Thanks.. bro

sree4dream said:

Thanks.. bro

Click to expand...

Good luck and let us know how it goes.