Calculated Fields for hierarchical ID.

[New.User]

Hello All,


My requirement was not precisely put forward in my previous thread https://www.accessforums.net/showthread.php?t=71639, I apologize for that. I will explain it now.
I have 3 tables tblEquipment, tblSubEquipment and tblParts (image posted)
I have unique ID for every new equipment. You can see from the tblEquipment unique ID for Lathe Machine is Lathe-123. You can also see from the image what each of the letters designate. Now when I want to create a database for a new lathe machine, using the same sub equipments and parts, obviously its series number will be different, say Lathe-456.
What I want is for a new Lathe machine I want only the series number to change but the sub equipment number and the part number to remain the same (depending upon the selection).
Something like this Lathe-456-01-01
How do I make this happen?

Some useful info:

Form One:
User creates a new equipment and adds the existing sub equipment and parts to it to build a new data.

Form two:

• One combo box to select the Equipment.
• Three filtered textboxes depending upon the combobox selection viz. textEquipment, textSubEquipment and textpart.

Regards
New User

[isladogs]

Two things
1. When you add e.g. a new lathe, you create a new RECORD not a new DATABASE
2. Users should never work with tables directly to add/edit data- only via forms

If I'm understanding you correctly you want to use cascading combo boxes on a form for this
Do a forum or Google search and you will find plenty of hits

Here's one example:

[New.User]

Yeah you are right. I missed that part of the information. I am using a cascading combo box. Users are never working with the tables. My bad.
Might sound silly but what should I type in google to search?

[isladogs]

Somewhat confused by your answer. If you need to search then try cascading combo boxes

[New.User]

You mean caascade combo box for code builder? okay i understand. Let me check.

[New.User]

By the way where did you get the example of the post code builder from?

[Minty]

It's all Colin's own work. Very neat.

[isladogs]

Thanks Minty.
@New.User - There are plenty of examples online with code

[New.User]

Thank you guys. Not many cascading combo boxes give an idea as you showed. I am still looking.

[isladogs]

I can't provide my example as the main data source has 2.6 million records - which is why I need cascading combos in the first place!

However there's a good looking example here: http://www.fmsinc.com/microsoftacces...cascading.html
I haven't checked out the code but I expect it will do what you need

[New.User]

Thank you Colin. I am aware of how the combo box works but the example you provided in the postcode builder is a bit different. The value of the first cbo must populate the second cbo and also concatinate with the other values and so on. I did not find an example that shows the one similar to yours.

[isladogs]

Ah - you may be pleased to know you are mistaken.
No concatenation involved - it just wouldn't work - at least not for my example
The principle is EXACTLY the same in my example - see attached video clip (approx 20 sec)

The 5 combos each use different tables as their record source.
First the area is selected (PostcodeArea table), then the second combo based on PostcodeDistrict table is filtered for those records in the selected postcode area
Similarly for combos 3 to 5

The only extra is the online map that is downloaded once the postcode is selected

Any questions, do get back to me
HTH

[Minty]

If you don't mind me jumping in, I'm not sure your data structure is quite correct.
You appear to be storing the "parent" descriptive text values concatenated with the "child" descriptive text items as your ID fields, this is not correct.
You should only store the ParentID then pull in the descriptor in the Child Record.

So your actual Equipment list would simply have these values stored ?

MachineID	SubEquipID	PartID
1	1	1
1	1	2
2	1	1
2	2	2

Unless I have misunderstood the data? Quite possible - I've only had one coffee so far.
And as Colin has pointed out your concatenated value should really only be exactly that, not stored.

[New.User]

Thank you Colin for making me realize that i was wrong else i would have gone in wrong direction. Also thank you for the video, I got an idea after seeing it.

Regards
New.user

[New.User]

Thank you for Stepping in Minty. I will take your suggestion as well but please give me some time as there is a slight different scenario when I compare it with Colin's video. I will tweak mine to match his video and get back to you.

Regards
New.User