Link to home
Start Free TrialLog in
Avatar of João serras-pereira
João serras-pereiraFlag for Portugal

asked on

selection of current record jumps to a non-selected record

I had this question after viewing linked subforms are yielding error:  ... (800110108).

Hi -

This is a long shot but I am loosing the hope to get the code right.

After sorting out the problems pointed out in the related question, and after cleaning the code, I am getting a weird behaviour on my form.

The form itself should follow a tree-like structure that is elaborated on a set of tables and subtables:

User generated image
so, I m using a master form and 5 subforms (the sequence is a 5 level tree structure) and, as suggested by EE, I am using form filters that are programmed in VBA and (more or less) functioning)

The situation is that, upon opening the form, I am getting the following:

User generated image
at level 3, it should show only 1 "pink" selected record, and shows 3! But, when I click the selection for level 2 (headed "módulo"), then its OK

User generated image
also, if I click on level 4 without previously clicking level 2 or 3, it should do nothing, but selects a record, in spite of a "if" avoiding it.

This is driving me nuts and I am attaching the db.

can anyone help?


/joao
Hierarch04.accdb
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

While you don't select any record on an upper level then no filter can be applied on lower level in case of subdatasheets.
I you switch to Datasheet View this problem will disappear automaticall because to expand a lower level sub datasheet you have to select a record on upper level.
Hi,

there are several problems with this database.
First of all, your data model is wrong. You have one table for each level of your hierarchy and to find out if one record of a subtable belongs to the right one of the upper level you copy all the values of all previous level tables to each lower level. That creates really big problems in querying later and also saves a lot of redundant data.
In a database you usually try to do the opposite: Save as less data as possible to come to the desired result. This is called "normalization", you should first read tutorials about that before you try to create a database or forms.

As each new table only adds a memo field you could handle the complete hierarchy with one table. Moreover the texts you now use as PK would be saved to a lookup table instead handled by a unique ID - and this one is the only PK here and used as FK in the hierarchy table which contains the ID value only, not the text. An additional ID field would then be used to point to the parent record and so each record "knows" where it belongs to.

But keeping your model, your form highlights fields with conditional formatting. But you use an unbound field for that purpose and try to set that in the Form_Current event - that doesn't work in a continous form. An unbound control always has the same value in all records. That means, the unbound control you set in the Current event would have the last value it gets assigned. That's the reason why your conditional formatting shows three records highlighted. You see that if you make all hidden controls visible and watch the values.
Moreover you assign the value of the display field to the fld_hilight in Form_Current so even if it would work it would always result in that both fields are equal so in this case all rows would be highlighted.

As you use a PK built from each PKs of the higher tables you also would need to repeat these values in all formulas down the road. It is not enough to compare the last value as it could be possible that it is contained in another "path" also, so you always would need to compare all previous keys also (that's why I said it is a complicate way).

The simplest way of solving this is by letting Access do the work for you. If you create subforms inside each higher form you can link the records with the link criteria provided by the subform container, Access will filter the subrecords on it's own. The disadvantage is that it looks a little bit ugly. Displaying the forms beneath each other is possible but you need to do everything on your own. But because of the data model it is a lot of work to do in the form design.

Each form of each level should, if I understand your design correct, display ALL records available in each level table and only the records which belongs to the selected one of the higher level form should be highlighted.

First, you should remember that you can assign another form only if it is loaded. The order of loading is: First all subforms and then the main form. So a subform cannot access the mainform (and therefore no other subform on the mainform) until it was loaded. So you should initiate the process in the main form because here you can be sure that all subforms are loaded (in the Form_Load event).

Next you need to chain the forms to their parent form. Each form needs to know the selected record of it's parent form so the "Form_Current" event should be used to inform the lower level form which record was selected. That can be done by using a hidden control which points to the corresponding PK field of the higher level, one for each. The conditional formatting can then compare these control values to the ones of the own table and highlight the text field, if all are equal.

So, for example, a control like this in XL4 would need to have a formula like this:
=[Form].[Parent].[ctl_L3].[Form].[arquivoL3]

Open in new window

To get the value of the field of the upper level. But if you change a record in XL3 it doesn't get recalculated so you need to start that in the Form_Current event of XL3:
Me.Parent.ctl_L4.Form.Recalc

Open in new window

But that also doesn't refresh the other subforms, so you must do that for each lower subform - and for each PK field.

Your model has also the disadvantage that you will have a lot of reporgramming if you ever need an additional level. So you should first reconsider your data model and then in the end create forms.

Cheers,

Christian
Avatar of João serras-pereira

ASKER

Hi Christian -

Thanks for the advice, It is a really long and EXTREMELY useful text. I reckon that it will be better, in the end, to modify the data model. The data model, as is,  is useful to construct the hierarchy, but not to consult it. It think that, by using a single table it will go much faster anyway.

I had a look to the Treeview control, but it looked so complex that I am afraid that I will not be able to cope with it...
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Chrsitian -

Thanks a LOT.

And YES: I do have a 6 level table tree for archiving documents (the documents are the 6th level and all indexing is on the previous 5 levels.

I am JUMPING to your solution right now and see it (hopefully) I can implement it!)
Hi Christian again -

I am getting an error on trying to unzip your database..
User generated image

can you sed to me by other means? my email is joao.serras.pereira@ware.pt

kindest regards

/joao
Hi,

you should not write your email address into a public forum if you do not want to increase spams in your mail...:-)

Please look into the comments of the article, there is an alternative download link from my website.

Cheers,

Christian
it's 32 bit but I am changing THANKS A LOT!