Avatar of SteveL13
SteveL13
Flag for United States of America asked on

How increment number by 1 when text field is one of three choices

I have a form which has a field which has been populated from an option group on a separate form for the creation of a new record.  This text field, named "txtDrawingType", has either been populated with a "M", a "P", or a "I" from the option group in the separate form.

On this same form is a field named "txtDrawingNumber".  This field can hold a value between:

If "M" in field "txtDrawingType" -  1,000 to 29,999
If "P" in field "txtDrawingType" -  30,000 to 39,999
If "I" in field "txtDrawingType" -  40,000 to infinity

Here is the challenge.  For example, if the drawing type is "M", and the last record written for a "M" type drawing is 1,205 (for example), then the new "txtDrawingNumber" needs to be 1,206.

As another example, using a drawing type of "I", if the last record written had a drawing number of 50,234 then the new record needs to have its drawing number be 50,235.

I have no idea where to begin.  Can someone help?

Thanks,

Steve
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Dan Violet Sagmiller (He/Him)

This is typically a bad practice.  to lock in ID's in such a way.  I presume this is all stored to a database.  It would be better to have an extra field which stores [Type] {"M", "P" or "I"} and let the ID auto increment.  This design only works if you purposefully hope that your users will never store more than 29k M's, or 10k P's.
jerryb30

Are The numbers and drawing types in the same table?
Jeffrey Coachman

I'm with hpdvs2.
Because if we did devise this system for you , we would also need to know what happens when the "P" value is 39,999...? ( and when M reaches 29999)

So can you take a step back and explain the meaning behind the M, P and I designations?
...perhaps there is an alternate method.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SteveL13

ASKER
To all so far... I totally agree with you that this is a bad way to do this.   But this is what the end user wants and say the numbering scheme will NEVER go beyond the given ranges.  So, that being what it is, I have to do it this way.  And yes, the numbers and drawing types are in the same table.
jerryb30

on some event
me.FieldtoHoldValue = dmax("fieldWithIMP", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
jerryb30

Rather
me.FieldtoHoldValue = dmax("fieldWithNumber", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

OK but you still have not told us what should happen when a the M and P limits are reached...
SteveL13

ASKER
I'm getting a "You can't assign a value to this object ... on....

Me.txtDrawingNumber = DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
jerryb30

Is this an unbound textbox?
If you type in the debug window
?  DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
do you get a value?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

Check the ControlSource properties of that control...

It should look like this:
ControlSource: DrawingNumber

*not* like this:
ControlSource: =DrawingNumber

Jeff
SteveL13

ASKER
It is DrawingNumber not =DrawingNumber

But, and this may be a clue... for whatever reason, the DrawingNumber field is a text field.
jerryb30

Can you post a small db with some dummy data?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
To Jerryb30...

Is this an unbound textbox?  - No
If you type in the debug window
?  DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
do you get a value?  Yes.  And it IS the next incremental number I would expect.
Jeffrey Coachman

Jerry,

I'll leave this in your capable hands

;-)

Jeff
ASKER CERTIFIED SOLUTION
jerryb30

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jerryb30

Jeff, your input is always welcome.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jeffrey Coachman

I'll still monitor.
I just want to step aside to avoid confusion with too many experts posting...