Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2019, 04:48 AM   #1
sree4dream
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
sree4dream is on a distinguished road
Exclamation 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
Attached Images
File Type: jpg Picture4.jpg (109.6 KB, 16 views)
File Type: jpg Picture1.jpg (66.3 KB, 19 views)
File Type: jpg Picture2.jpg (65.6 KB, 16 views)

sree4dream is offline   Reply With Quote
Old 07-18-2019, 05:16 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,107
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Serial Numbering in Access Form

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-18-2019, 05:50 AM   #3
sree4dream
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
sree4dream is on a distinguished road
Re: Serial Numbering in Access Form

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 is offline   Reply With Quote
Old 07-18-2019, 06:44 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Serial Numbering in Access Form

Quote:
Originally Posted by sree4dream View Post
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
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-18-2019, 06:58 AM   #5
sree4dream
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
sree4dream is on a distinguished road
Re: Serial Numbering in Access Form

Thanks.. bro 👍🏻
sree4dream is offline   Reply With Quote
Old 07-18-2019, 07:00 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Serial Numbering in Access Form

Quote:
Originally Posted by sree4dream View Post
Thanks.. bro 👍🏻
Good luck and let us know how it goes.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Tags
serial number

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
auto Serial Numbering for Each Quotation rehanemis Modules & VBA 3 04-09-2014 06:52 AM
Weigh Scale Serial to Access Form Text Box roundup Modules & VBA 0 03-18-2013 12:50 PM
Access and Serial Port Communication WhiteNite1971 General 3 03-18-2011 01:07 PM
Table setup for component serial numbering system maximisus Tables 4 12-08-2010 06:31 AM
Serial numbering system gear General 10 04-19-2007 09:22 PM




All times are GMT -8. The time now is 07:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World