Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Course Of The Month
1 day, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How increment number by 1 when text field is one of three choices
Posted on 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
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
[X]
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
7
5-
4
- +1
17 Comments
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.
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.
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.
Active today
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.
on some event
me.FieldtoHoldValue = dmax("fieldWithIMP", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
me.FieldtoHoldValue = dmax("fieldWithIMP", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
Rather
me.FieldtoHoldValue = dmax("fieldWithNumber", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
me.FieldtoHoldValue = dmax("fieldWithNumber", "tableWithIMP", "fieldWithIMP= '" & me.txtDrawingType & "'") +1
Active today
I'm getting a "You can't assign a value to this object ... on....
Me.txtDrawingNumber = DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
Me.txtDrawingNumber = DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
Is this an unbound textbox?
If you type in the debug window
? DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
do you get a value?
If you type in the debug window
? DMax("DrawingNumber", "tblDrawings", "DrawingType= '" & Me.txtDrawingType & "'") + 1
do you get a value?
Check the ControlSource properties of that control...
It should look like this:
ControlSource: DrawingNumber
*not* like this:
ControlSource: =DrawingNumber
Jeff
It should look like this:
ControlSource: DrawingNumber
*not* like this:
ControlSource: =DrawingNumber
Jeff
Active today
It is DrawingNumber not =DrawingNumber
But, and this may be a clue... for whatever reason, the DrawingNumber field is a text field.
But, and this may be a clue... for whatever reason, the DrawingNumber field is a text field.
Active today
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.
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.
Create an unbound textbox to hold the value, and when you save your record, use that to populate the bound control.
Featured Post
Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Course of the Month1 day, 19 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.