Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Generate Custom Number and assign to another field on the form

Posted on 2014-01-06
10
Medium Priority
?
531 Views
Last Modified: 2014-01-12
I am generating a custom record number based on three fields:
Type
SO#
WOID

The custom number is created using the following equation:
= [SO#] & Left([Type],1) & [WOID]

I first used this equation as the source for the WorkOrderNumber field and the custom number was not saved in the field in the table.

So I then created an unbound field to capture the custom number and then I set the WorkOrderNumber field equal to the unbound field.
Neither saves the the custom number and I would like to save this number since it should not change once assigned.

Thoughts?
0
Comment
Question by:btgtech
[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
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39761251
Use a query based on the table instead of the table for your form recordsource. Create a field in the query that concatenates the 3 fields. Now you can use a bound text box to display the custom number.
0
 
LVL 85
ID: 39761881
Peter has pointed you in the right direction, but to expand:

You should not save values like that in a table. Instead, since you always have the "root" values available (i.e. the Type, SO#, and WOID fields) just "build up" that value when you need it. The only reason for that value is for human readability, and you can run into troubles down the road if you stored "calculated" values like this. For example, what if the Type changes? You'd have to be sure that you also update that calculated value, and if the user somehow gets to the table and changes it directly, your data becomes flawed.

So save the "root" values, and use a query as Peter suggests.
0
 

Author Comment

by:btgtech
ID: 39761994
But, if someone mistakenly changes the SO# or the type, then the number will change and I would rather not have to lock down the fields.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 85
ID: 39762162
then the number will change
The number would not change unless you put code in place to insure it changes. That's the entire concept behind NOT storing calculated values, and instead storing the root values - changes made to the "root" values would be reflected in your forms and reports and queries without you having to do anything further. If you stored the full calculated value, you'd have to insure you make the required data changes in all those locations to insure you write back the calculated value correctly.
I would rather not have to lock down the fields.
I'm not sure what you mean by this, but you don't have to lock down anything (and you can't do that in Access anyway).
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39762980
You could save the calculated field in the Form's BeforeUpdate event.  I also do not like creating "meaningful" fields like this because as has been pointed out, changing one of the underlying components either results in a new value if you did it right and didn't store the value but calculated it instead and if you do it wrong and store the value, it doesn't change but now it will no longer relate to the rest of the record.  You need to come to grips with the issue and decide which is best for you.

If Me.CalcID & "" = "" Then
    [SO#] & Left([Type],1) & [WOID]
End If

Open in new window

0
 

Author Comment

by:btgtech
ID: 39763391
The Work Order number is a number that we need to set once.  It does not change for the duration of the work order and for the most part the only time that the other parts may change is due to an entry error.

I will just use the calculated field.
0
 
LVL 85
ID: 39767543
for the most part the only time that the other parts may change is due to an entry error.
I've been doing this a long time, as has Pat, and my experience has always been that forcing non-standard behavior (like this) is never a good idea.

But it's your data, and you're the one who'll have to deal with the mess ...

Also, you really should not have accepted my comment as your "solution". Pat's answer will provide you with the method to store your calculated value, so I've asked the Moderators to change the award.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39768002
Thanks Scott.
0
 

Author Comment

by:btgtech
ID: 39775602
OK,
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

604 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