Dlookup to search the Matching string and autopopulate

[Axel]

Hello Everyone;
I am new to this forum and need help to build a database. I apologize before hand if I am asking a wrong question. Lemme begin!
I am creating a database for a maintenance program (failure mode and effect analysis) and I have a many to many relationship.
I have 5 tables
1)tabEquipments
2)tabParts
3)tabFailureTypes
4)tabCauses
3)tabMaster (to establish relationship for many to many)
I have a from with two cascading comboboxes comEquipment and comParts.
These filter data to two other combo boxes.
Need of the hour
One of the project requirement is to create a new Equipment add existing Parts to it and populate the respective part’s failures and causes by creating a new record.
Confusing? Will debrief you guys-
In the example I have added a new equipment “EquipmentX” and added existing part “Oil Seal” to it by creating a new record. Now I want to auto populate the Failures and Causes for the previously available “Oil Seal” by creating a new record. How do it do this?

I think I need to use Dlook up to match the string “Oil Seal” and autopopulate its information. But I do not know how to do it.

Kindly Help

I have attached the DB.

[Micron]

Welcome to the forum. First question - is this a school project? If so, I think our responses need to be kept simple so you can complete the exercise. If not, perhaps more in depth responses would be warranted. For a functional FMEA db, I would have some concerns with your current situation.
I think to keep the answer to your specific question simpler for now, cascading combos would probably be the way to go. Choose a part from combo1, combo2 presents a list of applicable failures (or whatever is appropriate for the next step); combo3, the next child level, is filtered according to the value chosen in combo2, etc. You can only DLookup one field from one domain at a time.

[Axel]

Hello,
This is a college project. I have made use of cascading combobox in the sample database attached. I just want to copy the child records if the same parent is added but this time as a new record. Basically I want to copy Failures and Causes to the new record "oil seal".

[Micron]

You have a way of relating parts 1 and 10 to equipment 1 or 3, but no way of relating a failure or a cause to any part, whichever it is you'd want to do. So it's not really possible to auto populate anything as you're trying to do, because you have no basis on which you can make a decision. The simplest answer I can give is that one might use DLookup to lookup the value, but I have no idea where you'd pull it from. While you have "not working" and "motor dirty" for one record, that record's part ID is 1. For the next seal record, you appear to have a different parts id, so nothing is common. Perhaps it is more precise to say that oil seal is 1 in one record and 10 in another, thus you have no common/relate-able factor.

Unfortunately, if this is an FMEA db, then I think you're off on the wrong foot with the table design. I'm thinking more along the lines of tables for
- equipment
- parts
- failures
- effects (or causes, as the case may be)
and junction tables for
- equipment/parts (to associate parts to equipment)
- failures/parts (types?) to associate failure types to part types
- failures/causes? (this one I'm unsure of as I'm not really sure of what it is you're creating the db for).

Even if it's not an FMEA db, your design won't support your request. Suggest you research FMEA type database designs.

[Axel]

You are right in saying that "oil seal" with ID 1 and "oil seal" with ID 10 are two different records. "Oil seal" with ID 1 and "oil seal" with ID 10 even though have different ID's they have a matching string.Can't I have a Dlookup to match the string ?
The common relatable factor is the same string "oil seal".

[June7]

If the search criteria is 'oil seal', which ID should be returned?

[Axel]

The new ID i.e. ID 10. Since it is linked with "Equipment X" with ID 3.

[June7]

Then you need more parameters in the DLookup.

[Axel]

What kind of parameters are you talking about?

[June7]

Haven't examined your db. So can only guess maybe also the equipment ID?

[Micron]

Maybe I can weigh tomorrow since i have seen the db. Been a busy weekend. I'm on my phone right now and it's late here.

[Axel]

This is a crucial part of my project. A little help with this problem will help me go a long way.

[Axel]

Cross posted at https://www.access-programmers.co.uk...31#post1575431.

[Micron]

Ok, so I took a second look. The answer is no, you cannot. I'll repeat why:

- you have no linkage (relationship) between either causes or failures AND parts
- you have no relationship between either causes or failures AND equipment
- even if you did have a relationship between parts OR equipment you have no way to link back through one to another. In other words, no way to get from C to A by going through B.
For the benefit of those who can't download your db because of version issues (or whatever) I've posted your tables for any additional comments that others might want to provide - or even dispute what I'm saying.

CausesID	Causes
1	Motor dirty
2	Cable damaged
5	Contamination
6	Seal worn out

FailureID	Failures
1	Not working
2	Not filtering
5	Slow rotation
6	No control

EquipmentID	Equipment
1	Pump
2	Compressor
3	Equipment X

PartsID	Parts	EquipmentID
1	Oil Seal	1
2	Diaphragm	1
5	Limit switch	2
6	Blades	2
10	Oil Seal	3

MasterID	PartsID	FailureID	CauseID
1	1	Not working	Motor dirty
2	2	Not filtering	Cable damaged
5	5	Slow rotation	Contamination
6	6	No control	Seal worn out

Would be much appreciated if you post back with how your db is received if you stay on this path.
Good luck!

[Axel]

Thank you Micron for you valuable suggestion.
If i cannot autopopulate the Failures and Causes of the old "oil seal" to the new"oil seal" then the only thing I can do is to manually drop down and select.