Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-12-27
17
383 Views
Last Modified: 2012-12-27
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
0
Comment
Question by:SteveL13
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 8

Expert Comment

by:hpdvs2
ID: 38724992
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.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38724993
Are The numbers and drawing types in the same table?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38725063
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:SteveL13
ID: 38725128
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.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38725163
on some event
me.FieldtoHoldValue = dmax("fieldWithIMP", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38725174
Rather
me.FieldtoHoldValue = dmax("fieldWithNumber", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38725175
OK but you still have not told us what should happen when a the M and P limits are reached...
0
 

Author Comment

by:SteveL13
ID: 38725188
I'm getting a "You can't assign a value to this object ... on....

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

Expert Comment

by:jerryb30
ID: 38725197
Is this an unbound textbox?
If you type in the debug window
?  DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
do you get a value?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38725205
Check the ControlSource properties of that control...

It should look like this:
ControlSource: DrawingNumber

*not* like this:
ControlSource: =DrawingNumber

Jeff
0
 

Author Comment

by:SteveL13
ID: 38725213
It is DrawingNumber not =DrawingNumber

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

Expert Comment

by:jerryb30
ID: 38725219
Can you post a small db with some dummy data?
0
 

Author Comment

by:SteveL13
ID: 38725224
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38725227
Jerry,

I'll leave this in your capable hands

;-)

Jeff
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 500 total points
ID: 38725228
Create an unbound textbox to hold the value, and when you save your record, use that to populate the bound control.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38725231
Jeff, your input is always welcome.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38725245
I'll still monitor.
I just want to step aside to avoid confusion with too many experts posting...
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question