How do you do this? (1 Viewer)

[bozrdang]

I have a form that's working pretty well, but there's one thing I'd like to make it do and I can't seem to figure it out. I work for a plastic manufacturing company. Obviously, we make product. These products are produced in molds. The purpose of my form is to create a setup instruction report. It tells the employee which mold to setup, and what product to set it up to run. Most of our products are only made out of one specific mold and most molds can make more than one product, but there are a couple cases where a product can be made from multiple molds AND that same mold can make many products. I assume this lends tblParts and tblMolds a many-to-many relationship.

The problem is that in those couple of rare cases, the product is still USUALLY produced from one mold only and the other capable molds are only used as a backup. I would really like to be able to select the part number from a combo box and have it "autofill" the USUAL mold number that is used to produce the part. I would still need to be able to enter a different mold number for the setup, but I don't want that mold to then be "associated" with that part the next time it is selected in the combo box.

I have attached the db. The form I mention is frmMoldSetup. Any help would be greatly appreciated!

 

[bozrdang]

Anybody?

 

[llkhoutx]

The way your form and tables are setup, any mold can be selected with any part.

You need a table which list all molds for a particular part, this is commonly referred to as a "junction table." Also include a usual indicator (fUsual, yes/no datatype), and a counter field. Each part number has as many records as unique molds.

The counter is incremented each time a mold is used.

Instead of using a combo box for the mold, use VBA to select the least used mold, storing its name in a textbox. Counters all start with zero, so pick the 1st smallest counter.

This way all molds get used. If one really wants to select the usual mode, you could add a checkbox to force use of the usual mold. However, from what you say, that isn't necessary as you want to use all molds.

Hope that this gives you some new ideas.

 

[bozrdang]

Let me clarify myself. I don't really want to use all molds. 99% of the time a part is made from only one mold. Even when there is more than one mold that can make that part, we still only produce it from one of the molds. The only time we ever use one of the other possible molds is if the usual one is broke.

Therefore, I'd like to select the part number and have the usual mold number autofill on my form, but I still need to be able to enter an alternate mold number in those rare instances when the normal mold breaks down and I am forced to produce the part out of one of the backup mold(s). Basically, I don't want to have to enter the mold number unless we are running the part in a mold we don't normally use for that part.

You need a table which list all molds for a particular part, this is commonly referred to as a "junction table."

I thought my tblSetup was a junction table. Is it not correct? I've haven't done much VBA except for some simple command buttons. Could you give me some examples of code that would do what I want?

 

[llkhoutx]

Your tbSetup is not a juntion table, its merely a table with links two tables.

Your form displays all molds, regardless of the part. That's what fundamentally wrong with your form.

A juntion table describes a many-to-many relationship for the same field. Your tbSetup links via two different fields.

The method I described is what you want, except you can eliminate the counter part. Pick the part and cause the mold combobox to update with the "usual mold." You have to use VBA code to pick it out of the juntion table joined to the mold table (a uqery). Store the MoldId in the mold combo box and requery the modl combobox - the allowable values have changed because the part number has changed. With the foregoing, other molds are still available in the combo box for that part, you've just forced the usual one which is identified in the juntion table.

You also have to build the juntion table. Remember fUsual can't be tested for "False" becaue a yes/no field is initialized as Null, not 0 or False.

 

[bozrdang]

I'm getting confused now. I don't understand why tblSetup isn't a junction table.

If I create another table as a junction table (say tblUsualMold), would it's realtionship be independent of my existing realtionships or would I need to change them and create a one-to-many realtionship between tblUsualMold and tblSetup?

I'm sorry if I'm being dense about this, but I'm fairly new to access and I'm tryin hard to figure some of these things out. Thanks for your help!!

 

[llkhoutx]

A juntion table allows a many to many relationship (on the same field).

Your tbSetup uses two different fields, therefore is not a jucntion table.

Example:

tbPart
PartID
sPartDesc
etc.

tbMold
MoldID
sMoldDesc
etc.

JCNtbPartMold - has a row for each mold applicable to a part
PartID
MoldID
fUsualMold

tbPart links to JCNtbpartMold links to tbMold.

JCNtbPartMold data
1 1 0
1 2 -1 (indicates the usual mold)
1 3 0

 

[bozrdang]

So then it would be wrong to use a seperate ID with autonumber in the junction table? It should have a multiple field primary key? Is this true all the time?

I assume that I should leave my tblSetup and it's current relationship with tblMolds and tblParts as they were.

Am I correct in assuming that I then create a query as the row source property for the MoldID combo or is there some VBA needed also?

 

[llkhoutx]

Now you on the right track.

tbMold links to the Juntion table which links to tbPart. tbMold doesn't link directly to the tbPart because their are many molds for each part and a usual one is utilized by default. Many to Many!

The mold combo box gets filled on the part combobox AfterUpdate event with the usual moldID. However the mold combo box still has values, a query recordsource with the junction table and mold table linked and with the partId filtering the junction table.

I'll build an example tomorrow.

Note that your mold table doesn't indicate which is the usual mold and all molds can be used with any part. That's is what's wrong with your form.

 

[bozrdang]

Here is my updated example. So, except for the VBA to make the mold combo box work right, everything is the way it should be?

 

[llkhoutx]

I've attached zip of a 97 mdb, I don't know what version you use, modifying the "example.mdb" you posted.

You should particularly note the following:

1. "frmMoldSetup" is the form I modified. The original form has been renamed and saved as "frmMoldSetup_0riginal."
2. I renamed the junction table to designate the tables involved. This way, one has an immediate reminder of the tables involved.
3. I modified the relationships of your tables. Take a look at menu Tools/Relationships. This correctly reflects the relationships of the tables. The query recordsource for your original form is just a very back way to do things. Absent some compelling circumstance, I think the best practice is to keep a form bound to a table, not a query.
4. Subform "sfrmMoldSetup" is added to display tbParts on the subject form. Note the subform propteries "Link Child" and "Link Parent." The subform tumbles with the PartID.
5. Note the following events of the subject form:
a. OnCurent
6. Note the PartID AfterUpdate event. This is the key to my modification and the use of the junction table. On this event, the "Usual" mold is suffed into the MoldID combo box. This does not preclude selecting another mold.
7. I added am unbound Mold Description textbox to the form to displace the mold name on the form. I assume that you have many molds and numberes may not be descriptive to all users. The code could be modified to display the description instead of the number. That's your call.

Hopefully, I've covered everything to get keep going.

 

[bozrdang]

Thanks a lot! That does exactly what I want. I've been looking over the code you used and I understand most of it except for DAO. I'll look that up in my books and see if I can figure it out.

I was putting this back in my original db and tidying things up and I have one more question. I didn't like my original form that was based on the JCNPartsMolds (frmUsualMold). It was too confusing and I thought it'd be better to select a part from a combo box and display all related molds along with allowing the addition new molds for that part. Basically, a form with a subform. My problem is that since the forms would only be updating the same table (JCNPartsMolds), I can't seem to create a form with a subform that works correctly. I can sort of get it to work by bounding the forms to queries, but when I use the record navigation buttons, I doesn't work right.

Also, is there any way to only allow one mold to be designated (usual mold) for a particular part? Right now, it's possible to select many or all molds as the usual mold.

Again, thanks very much for your help!