Unique Sequencial Numbering using another field

Hello,
I have a MS Access 2007 Database with numorous tables. What I need is to create a unique sequencial number for a field that is based off of another field. I have done tons of research and tried different approaches with no avail. In my table: tblRotationMissions, I have a field called RotationTMRNum (number data type, Long) that needs to be autmatically generated but with a field: RotationNum (number data type, Long) with a - then the sequential number. RotationNum actually comes from another table: tblRotations. For example, when someone enters a mission request and enters the RotationNum I then need the RotationTMRNum to autmatically generate with something like: 1401-0001. And so on, then restart when 1402 is entered for RotationNum. Everything that I have read on this has said NOT to do this because of the complexity, but this is what my boss wants. I created another field that I designated as not visible on the form called Sequence (Number data type, Long) for the sequence numbers obviously. I tried the following code on the form for the RotationNum AfterUpdate Event:

Private Sub RotationNum_AfterUpdate()
Me.Sequence = Nz(DMax("[Sequence]", "tblRotationMissions", "[RotationTMRNum]=" & Me.[RotationNum] & "'"), 0) + 1
Me.RotationTMRNum = [RotationNum] & "-" & Format([Sequence], "0000")
End Sub

When I tried entering in the RotationNum it came up with this error:

Run-time error '3075'
Syntax error in string in query expression '[RotationTMRNum]=1".

And it highlights the first string of code.

I am not too great at code writing so I am sure that is where the problem is or maybe where I put the code? Please help!

Thanks so much,
Fran
francoeonaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marajahCommented:
Hi!
I think you haven't to store the value of the serial number. You could use the substr function to extract this value from the last inserted, then calculate the max and assign a +1 to it. Then reconstuct the string and assign it to the destination field.
0
AccessGuy1763Commented:
I think it's just this part that's causing it to fail... I don't know what you have this little extra part in there:

[RotationNum] & "'"

Open in new window


So just remove that part I think:

Me.Sequence = Nz(DMax("[Sequence]", "tblRotationMissions", "[RotationTMRNum]=" & Me.[RotationNum]), 0) + 1

Open in new window

0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I do what you want a lot. The trick I have learned is to only store the sequence number not the RotationTMRNum.

RotationTMRNum will be calculated as need for display only but NOT stored.


The control source for a textbox on forms and reports will be:

= [RotationNum] & "-" & Format([Sequence], "0000")

Open in new window



This makes it very easy to increment the field Sequence
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

francoeonaAuthor Commented:
marajah, hello and thank you for replying so quickly! How whould I do what you are suggesting exactly? I am not too good at code.... (blush)

AccessGuy1763, hello and I also thank you for replying so quickly! I changed the code like you said and now I get the following error:

Run-time error '-2147352567(80020009)':
The value you entered isn't valid for this field.

And the 2nd string of code is highlighted.....maybe I have something wrong in there as well?
0
francoeonaAuthor Commented:
TheHiTechCoach, thank you so much for responding! Will I still be able to use that field in other tables for relationship purposes? I have numorous other tables that need to be related to the missions table but based on the RotTMRNum. I have a feeling this is not possible.....
0
AccessGuy1763Commented:
As you said, the field is a Long data type.  The "-" part isn't valid.

Sometimes even though "numbers" are technically numbers, for a database they really should be a string.  A couple great examples would be Phone Numbers and Zip Codes.  A very good rule of thumb, in my opinion, is to ask yourself whether you would ever attempt to Sum, Add, Subtract, or multiply the field (obviously this would be a strong no for Phone #s and Zip Codes).  If the answer is No, it should probably be a text field.  One important exception would be an Autonumber or Identity field.  But you don't really have a choice there as I don't think you can have those be strings.

TheHiTechCoach is also giving you a great tip regarding this field... really it shouldn't ever be "stored" if the right design is used.  Whichever table has Sequence should have that field and RotationNum as a composite key.  On your forms you can then simply add a text box with a control source as described by Coach.

PS - The answer to your question about the relationships is that those tables that need to be related would be joined by both fields that make up your composite key (Sequence and RotationNum).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
francoeonaAuthor Commented:
AccessGuy, Thank you for the explanation. OK. I got rid of the field RotationTMRNum in the table and made it an unbound text box with = [RotationNum] & "-" & Format([Sequence], "0000") in the box. Tried choosing 1401 for the RotationNum and got 1-0001 for the RotationTMRNum.....what did I do wrong?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
The way I recommend handling this is to use an autonumber as the primary key and in all relationships.  This is usually hidden from the user.

The Sequence and RotationNum are just for show to the user.  Not the real primary key fields They can be set as index with unique values. The user will use the RotationNum for selecting records etc but underneath Access really is using the hidden  autonumber primary key to handle all the relationships.
0
francoeonaAuthor Commented:
TheHiTechCoach, I understand what you are saying. What I believe is the reason for me getting 1-0001 is that the field RotationNum is a combo box with the rotation numbers listed to choose from and the 1 is the ID (PK, Autonumber), I think? I will "play around" with it some more and let you know how I am doing. Thank you so very much for taking the time to try to help me. Have a wonderful evening!

Fran
0
AccessGuy1763Commented:
It needs to be like this:

 = me!RotationNum.column(1) & "-" & Format([Sequence], "0000")
0
hnasrCommented:
If more input is required, upload a simple database showing the issue.
0
francoeonaAuthor Commented:
Good Morning and Happy Friday!

accessGuy1763, I copied and pasted your code into the control box but it came back with this error: The expression you entered contains invalid syntax - You may have entered a comma without a preceding value or identifier.  

TheHiTechCoach, I am embarrased to admit I don't understand how I am going to relate all the other tables with the autonumber PK....the RotationTMRNum was how I was originally going to relate them all but am figuring out if I want that number to be formatted the way my boss wants it will not be able to be used for the relationships. You see, there are two different types of customers who request mission support: Rotation Customers and Installation Customers. So I have seperate tables for these customers and the field that tied them with the other tables was the TMRNum field. Either RotationTMRNum or InstallationTMRNum. Now the InstallationTMRNum can be an easier fix because we could use the date-0001 for the sequence, but can I still use it for the relationships? I have started taking some online courses for MS Access but the website I subscribed to doesn't have anything covering this topic as far as I could find. I have removed the original relationships. I am attaching the database as it is now....it still needs a lot of work. LOL
TMR-Dadabase.accdb
0
AccessGuy1763Commented:
I don't know that it's possible to directly reference a combobox column the way I described above.  I was able to achieve a similar effect by altering the After_Update event for your RotationNum combobox:

Me.Sequence = Nz(DMax("[Sequence]", "tblRotationMissions", "[RotationTMRNum]=" & Me.[RotationNum]), 0) + 1
Me.RotationTMRNum = Me.RotationNum.Column(1) & "-" & Format(Me.Sequence, "0000")

Open in new window


This should work fine if this form is used for Data Entry only, but if it's also used to view records and navigate between records you will probably want to add that code to the Current Event (it might need to check to see if RotationNum is filled before attempting to set the value for RotationTMRNum... it will be blank if you move to a New record).
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Fran,

I still recommend using a auto-number ( or something else taht is system assigned) as the primary key. The users will still use the RotationTMRNum or InstallationTMRNum in combox boxes, etc. The system underneath will really use the autonumber.

You see, there are two different types of customers who request mission support: Rotation Customers and Installation Customers. So I have seperate tables for these customers
You should avoid having duplicate tables. This will only make things a lot more difficult.

I will take a look at your TMRDatabase database you posted. I will make some of the recommend changes and post it.

EDIT: Took a peek at your database. This will be much easier for you to create if you will first take some time to learn about the rules of normalization and redesign your tables.   With the current deign you will continue to run into lots of headaches.  I see lots of duplicate tables that should be combines.
0
francoeonaAuthor Commented:
TheHighTechCoach, thank you so much for taking the time.....I am currently modifying some of the tables based on what you have advised. Plus, the way I had the ClassI through IX tables needed to be restructured. So, you are saying that one Missions table should work? Ok, I will take a look at that as well. I don't remember now why I had split them before, I think something to do with the relationships....thanks again for your help with this.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Plus, the way I had the ClassI through IX tables needed to be restructured.
Great, It should be a single table with a field for the class Type instead of using the table name as this data.

I don't remember now why I had split them before
When making the transition from spreadsheets to relational database this is a common hurdle. You might have been think about how you want to display the data.

How you display data should not dictate the table design. This will make more sense as you continue building your database.

1) Your tables should be deigned to store the data in the most normalized format. Avoid letting how you think the data entry form will work affect the table design.

2) You use forms to enter data that will generally match the work flow. They do not have to look just like the tables. You display the data in a way that best helps the user.
 
3) You use queries to collect the required data from multiple tables.

4) You use reports for presenting data as needed.  Reports rarely look like the forms.

Form design not matching table design example:
You could use separate sub forms to display each Class type (like your original form)   Or you could show all the classes together in a single sub form. Which method you use is based on what is best for the user but it does not change the table design of all classes in a single table.



After you get your database updated please recreated the relationships for all the tables Before posting it again.Also please enter some sample data in all the tables.
0
francoeonaAuthor Commented:
Thank you AccessGuy! That worked!

HighTechCoach, I have been asked to put this project to the side for now to work on something else....(which is extremely irritating) :) But when I do get back to it, I will get what you have asked done and upload it again for you to take a look at. I so very much appreciate you guys helping me out with this! Take care and will be back when possible!

Fran
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.