Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2019, 09:09 PM   #1
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Knowing Previous number

hello all ,
In my form the first field to enter is kaizen number, & I have 5 depts., so the number would be a1,b1,c1,d1&e1 and so on .
Now as the list is getting long, I have to check each time what was the last number given to a particular dept. & I have to write the next number.
Is there any way to simplify this problem??
Thank you in advance.

Ravi Kumar is offline   Reply With Quote
Old 11-05-2019, 09:32 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 573 Times in 569 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Knowing Previous number

Certainly can be dealt with.

Alpha sort rules apply. a15 will sort before a2 so if you searched for Max(fieldname), a2 would return instead of a15. Placeholder zeros can assure sequential order: a00001 a00015.

So if you stored identifier parts in separate fields (Prefix, Number) an expression can concatenate to a sequential value when needed:

Prefix & Format(Number, "00000")

Or code can split value and recombine with formatting when needed.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 11-05-2019 at 09:43 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Ravi Kumar (11-05-2019)
Old 11-06-2019, 09:51 AM   #3
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 630
Thanks: 78
Thanked 69 Times in 69 Posts
vba_php is on a distinguished road
Re: Knowing Previous number

Quote:
Originally Posted by Ravi Kumar View Post
I have to check each time what was the last number given to a particular dept. & I have to write the next number.
r u saying that your next numbers would be like "a2, a3, etc..." for records pertaining to your 1st department and "b2, b3, etc..." for the 2nd department? or r u saying that each future record would need to be "f1, g1, h1, etc..."?

June is giving a solution for the 1st scenario.

vba_php is offline   Reply With Quote
The Following User Says Thank You to vba_php For This Useful Post:
Ravi Kumar (11-06-2019)
Old 11-06-2019, 08:34 PM   #4
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Knowing Previous number

Yes Sir it is exactly as you described...
Ravi Kumar is offline   Reply With Quote
Old 11-06-2019, 09:27 PM   #5
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 573 Times in 569 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Knowing Previous number

vba_php described two scenarios. Which one applies to you? The second one doesn't seem feasible.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Ravi Kumar (11-09-2019)
Old 11-06-2019, 10:35 PM   #6
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 630
Thanks: 78
Thanked 69 Times in 69 Posts
vba_php is on a distinguished road
Re: Knowing Previous number

Quote:
Originally Posted by Ravi Kumar View Post
Yes Sir it is exactly as you described...
Ravi,

This answer of yours is very vague! You didn't answer my question, or June's latest message. Please clarify WHICH situation I described applies to your situation. That way we can help you.
vba_php is offline   Reply With Quote
The Following User Says Thank You to vba_php For This Useful Post:
Ravi Kumar (11-09-2019)
Old 11-07-2019, 01:33 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,600 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Knowing Previous number

Start by keeping the two fields separate. Then you can use a DMax() to find the highest numeric value for the department and add 1 to generate the next number.

Me.NextNum = Nz(DMax("SeqNum", "yourtable", "DeptLetter = '" & Me.DeptLetter & "'"), 0) +1

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Ravi Kumar (11-09-2019)
Old 11-09-2019, 03:16 AM   #8
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Knowing Previous number

Quote:
Originally Posted by vba_php View Post
Ravi,

This answer of yours is very vague! You didn't answer my question, or June's latest message. Please clarify WHICH situation I described applies to your situation. That way we can help you.
Hello sir , I am very sorry , I was not in town hence I couldn't answer to your questions.
My requirement is like this : While entering kaizen details I give unique names to it for index purpose like for production department it would be PRD-01 , PRD-02 and for calibration department CD-01,CD-02 & so on .
As now it has become sufficiently big , before giving the number I need to first look at the last number allotted in my datasheet ,again come back to form & enter the same .

So I need to know whether this can be simplified in any way ??
Ravi Kumar is offline   Reply With Quote
Old 11-09-2019, 03:17 AM   #9
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 86
Thanks: 106
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Knowing Previous number

Quote:
Originally Posted by Pat Hartman View Post
Start by keeping the two fields separate. Then you can use a DMax() to find the highest numeric value for the department and add 1 to generate the next number.

Me.NextNum = Nz(DMax("SeqNum", "yourtable", "DeptLetter = '" & Me.DeptLetter & "'"), 0) +1
Hello sir thank you. I Will try & let you know the result.

Ravi Kumar is offline   Reply With Quote
Reply

Tags
form , simplifying

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Importing a table without knowing its name? totempohl Modules & VBA 5 08-14-2013 02:21 PM
Question Method for knowing which objects are being used most? smithp General 6 02-16-2009 07:34 AM
when does being aware of something mean not knowing? sha7jpm The Watercooler 7 07-03-2007 04:43 AM
Knowing which tables are used in queries ejhatch General 1 02-18-2006 11:44 AM
To Pat o anybody knowing how to unlock wizards Alexandre Modules & VBA 2 02-11-2002 04:57 PM




All times are GMT -8. The time now is 08:19 PM.


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