showing only a selected record in set based form

[MikeC]

I hope that this is not a trend in my having to request help to
develope my application. The good thing is that with what I do know now,
this project would have been to the point it is at in a manner of days
instead of a month. :-)
I have a one-many set based on 3 tables. Client_Info table is the
parent. Mach table is a child. Prod table is a grandchild.

Forms--
Browse_Companies is a set based tabbed form with embedded browse.
Client_Form_Tab is a set based tabbed form with one embedded browse in
the tab called Machines.
Mach_Entry is a set based tabbed form which will have an embedded browse
based on the Prod table.
Mach_Entry is form I had to change to a set based one so that I could
create the Prod embedded browse.

My problem is I cannot get the Mach_Entry form called up to show
only the record of a particular machine selected--simple enough I would
think. I have used the following ways to attempt this:

Opening a form to show only the previous record selected, Opening a form to show only records that match a control, and Using a global variable to pass a
record value to the form. (All done using Action Scripting). None do the trick for me.
This had not been a problem until I had to change the form I want
to open to a set based one (versus a single table based form in which I had no problem accomplishing what I now cannot).

Changing the basis of the form(from table to set) also caused
another problem in the Machines form in that it would not populate from
the Mach table---but this should possibly become another thread at a
later time.

If needed to ascertain what I am trying to accomplish then I have
attached a sample of my database.
In the attached database navigate in the following way:
Choose the form Browse_Companies --> doubleclick on the company AF
Machine --> choose the Machine Tab --> highlight the 2nd or 3rd
machine and press the Change button. The form Mach_Entry will come up
but always shows the first machine in the list no matter which machine is
highlighted. The Mach_Entry form should show the details of the machine
that was highlighted.

Mike

[Mike Wilson]

Not sure but look at this

Mike,
I'm not sure (very not sure) but I think the issues somehow relate to
1. using calculated fields (incrimental ID's) as your linking fields for a set structure one-to-many.
2. Not having the same field in both the parent and child records for the link to develop properly.
3. Having somehow conflicting Unique-only validation rules
4. maybe other conflicting stuff too that I'm not smart enough to figure out.

I did have to make a bunch of changes to get it to work. What I did:
1. Added a common field to the child table that matched the index field of the parent table.
2. Deleted all your indexes, validation rules, and calculated field rules for your linking fields.
3. Changed the MachineID to different format for easier viewing.
4 Created a new set (Cascade) viewable as Browses in Form brCascade.

But, I got the set to cascade as I believe you wish it to. I created a new set- Cascade with a Form containing Browses- brCascade. This will splay out the cascade so you can see how it works. There ore only enough records in the tables for ClientID-00003-Apollo, Machine A30 and A31 to displaying the cascade enough to see the effects.

This may have butchered your database a bit too much, but there were some conflicts I couldn't find with my level of trace knowledge. Hope this helps.

Mike W

[MikeC]

Mike, thanks for the response. I will check out all that you did for me and post back. With all those possible conflicts present, I am surprised it worked as it did prior to my last changes!

Mike

[Tim Kiebert]

MikeC,

The reason, as far as I can see, why the record you want does not show:
All three forms are based on the same set. When you double click a company in the Browse_Companies form the Client_Form_Tab form opens showing the correct client. This is because you have told it to open using the current record active in the Browse_Companies form. So far so good, because the object of interest at this point is the client info which is the parent table in the set. You have just changed to different view of the same set of records and filtered those records to show only the records applicable to the client of interest.

Next you use the same approach to open the Mach_Entry form. However the main focus is now supposed to be on the machine not the client. Since you are still in the same set and have told alpha to use the current record which is the current client as the parent, the same set of records still apply. In the Mach_Entry form you are still looking at this same set of data but starting with the child table for machines. The parent is still the client and just as when you first open the Client_Form_Tab form, the first child of the parent has focus.

The short version is that when you specified to open the Mach_Entry form using the current record you are referring to the parent client record not the child machine record.

So, how to make it work? ...... To fix the immediate problem the easiest I found (others will probably suggest better) was to create a second set with the Mach table as parent and the prod table as child. Base the Mach_Entry form on the new set (right click the form and choose 'copy to...'. Then when opening the Mach_Entry form from the Client_Form_Tab form specify the records using 'Records that match the value in a control on the current form'. Match up the Mach_Id field. ( in the attached file I have used a different set for the Mach_entry form)

I have attached your app revised. ..... and took the liberty of a couple of additions.

1/ Changed the basis of the Browse_Company form to the client_info table. -- as you are only using this form to choose a company. Had to ad the client id field to help with opening the Client_Form_Tab form.
2/ changed some of the code attached to some of the buttons on the Client_Form_Tab form.
3/ prompted by the tab label of 'Lanes' I added a Lane table and a lane action table. I used these tables in the Mach_Lane set along with the prod table.
4/ created the Mach_Lane set. and used this as the basis for the Mach_Entry_L form. This way you can use the prod table as a lookup to assign a product to a lane and you can keep track of actions having to do with each lane.

This looks like an interesting project. I can see lots of different info you might keep for such an application.

[MikeC]

Mike and Tim

Mike,

What you gave me is definitely appreciated ! It didn't directly
answer my question, but it is so close to a form I intend to create in
the near future that it will undoubtably save me time in doing so. I also plan to be a bit more aware in keeping things more simple at first so that things I add to the program do not possibly interfere with what is already there---such as adding so many rules, etc. If nothing else it will help me (and others) be able to figure out more easily why something doesn't work. Thanks again.

MikeC

-------------------------------------------------------------------------

Tim,

Your explanation was great---it really helped me understand quite
a bit more in how this database programming works and should eliminate
many additional questions that definitely would have come up later.
What is appreciated as much is your taking the time to revamp
some things in my database to make it work for me---what may have been
simple for you I have taken several days spinning my wheels so to speak
to try and figure out without success...and no wonder considering the
basic knowledge I lacked prior to your simple explanation.

Mike