Refer and filter one subform as datasheet to another subform

[jaryszek]

Hi guys,

i have 2 subforms as datasheet view. Now when i am selecting one field from subform1 and see only 5 records in subform 2. Problem is that when i want to filter subform 2 i will see all values possibilities from recordsource.

But i want to see only 5 records to filter from subform2.

I have to somehow refer from subform2 to subform1 selected field and use it as criteria. How can i do it?

Best,
Jacek

 

[Ranman256]

do mean like a form-subfrom-subSubForm

where selecting item in subForm1 filters subForm2?
like 3 deep forms.
This seems to be your solution. I use this.

 

[jaryszek]

Not exactly,

i have 2 separate subforms... not mainform - subform - subsubform.

Best,
Jacek

 

[MajP]

I am guessing because this is not clear. If for example subform1 lists parents and subform 2 lists related child records you can do the following.
1) on the main form put a hidden field called txtLink
2) on the first subforms on current event set the value of txtLink
me.parent.txtLink = me.somePrimaryKey
3) Link subform 2 to the text box in the properties window of the subform control
linkMasterFields: [txtLink]
linkChildFields: [SomeForeignKey]

Now when you move to a record in subform 1, it will filter subform 2.

 

[jaryszek]

thank you MajP

This is not what i meant.

I added sample database to show a problem.

On frmMain when i am on Paul Student subform i need to have filtering only two classes : Red and Black not whole list which are available.

So subform with Classes combobox has to have changed recordset based on criteria from Student Subform.
I would like to avoid VBA for this if it is possible.

Best,
Jacek

 

[isladogs]

In your relationship diagram, change all the outer joins to inner joins.

BTW The attached file contained a table MSysCompactErrors.
This could be an advanced sign of a future crash.
Check carefully that everything is as it should be then compact again.

 

[jaryszek]

Thank you isladogs,

i changed relationships to inner joins, the issue didnt disappeared. Still the same.

Best,
Jacek

 

[isladogs]

It looked ok to me. Perhaps I misunderstood.
See if you can simplify the table structure.
Also look into the sub subform layout as already suggested

 

[jaryszek]

isladogs,

when you have datasheet you will see also all records from recordsource.
So this is not layout or error thing.

As recordset i have to somehow set up criteria from subform1.
Maybe you didnt understand me.

Best,
Jacek

 

[isladogs]

No I don't think I misunderstood after all.
Your table structure is flawed.

Teachers/lecturers teach classes. Classes contain students.
If a student moves to a new class their teacher will change.
However the first teacher still has the same class as before minus one student.
The second teacher has their own class plus one student

Think about how your junction tables and foreign keys need to change

Once you have done that, I suggest you
1. Scrap your subdatasheets. They impact on performance
2. Change your main form to use one of the following
a) classes subform with sub subform students
b) two cascading combos - teachers then classes followed by 'cascading listbox' for students
c) the hidden textbox as suggested earlier by MajP

I've used all three of these ideas in my own databases including the schools apps. All work perfectly if the table structure is correct.

Good luck

 

[jaryszek]

Thank you.

Figure out any other example database model with 2 subforms and one main form.
Still i will have the same problem.
If you are not using 2 or more subforms and you are creating main form- subform - subsubform- subsubsubform - it is ok but not user friendly (how many nested forms you want to use? if you have 15 tables?)- try to help me with my specific issue here where model is different.

This data i figured out, they do not exist in my real model.
Imagine that i have about 10 more tables which are assigned to LecturerStudents tables.

So you will have pararell subforms, not subsubforms and nested subforms. and then you have to use textbox with parent ID like i used here.

I am using this model and i have to use it and i cannot change it.

So now i have to filter my subform based on another subform value. And this is the issue.
here is the link where you have subforms linked:

http://www.fmsinc.com/microsoftaccess/forms/synchronize/linkedsubforms.asp

we can use this model example if you want to.

Best,
Jacek

 

[jaryszek]

Ok this was so easy...

Please find solution in database attached.

Best,
Jacek

 

[jaryszek]

aa sorry i still have error.
Hmm...

Jacek

 

[isladogs]

Well I#m not a mind reader:
I can't see any difference to what you had originally and I don't know what error you have.

I do know the structure I suggested works. I have my own commercial schools databases which are a far more complicated version of what you are trying to do and have been in widespread use for over 15 years
I have forms with 3 or more levels of subform (from memory up to 10 are allowed) and they work fine though I agree it can be complex keeping track of the logic.
I also have other forms with 2 or more separate subforms filtered to values set in the main form. In addition, I have forms with unconnected subforms.

All of these layouts work ...with the correct table designs.
I recommend you go back to the design stage and do what I suggested.

 

[jaryszek]

thank you Ridders52,

I do know the structure I suggested works. I have my own commercial schools databases which are a far more complicated version of what you are trying to do and have been in widespread use for over 15 years

you do not know my whole model - do not be sure
And not always your way is the best to design database, try to be open minded

Uuu 10 nested subforms? O wow and you have to click again and again to expand them one by one? And input data? These are datasheet views?
Do you have any examples, i am interested to see this. I can learn something new.

My Customer wants to have all in one main form. And i have this for him.
A lot of subforms based on juntion tables and comboboxes. You can easily add all atributes and not clicking all the time to open another separete forms with another subforms. All in one place - very user friendly. And also working already one year without complaints.

--->

Ok returning to issue:

I afraid that filtering can be change only via VBA - after selecting record on one subform i have to change recorset on subform and requery it. and thanks to it i can see expected result.

Best,
Jacek

 

[jaryszek]

Ha,

i did this! Very easy and working.

please find screen in attachment.

Best,
Jacek

 

[isladogs]

thank you Ridders52,
And not always your way is the best to design database, try to be open minded

I never said it was. All I said was I have a system which does work and according to you, yours didn't ... but from your last post it does again....????

You asked for advice. I offered it. Up to you whether you take my advice or not

Uuu 10 nested subforms? O wow and you have to click again and again to expand them one by one? And input data? These are datasheet views?
Do you have any examples, i am interested to see this. I can learn something new.

I didn't say that either. I've just checked here and in fact you can 'ONLY' have up to 7 nested subforms (not 10)
I do have at least 3 levels of nesting in a few RARE cases but those are NOT datasheets
Nesting forms is not the same thing as using subdatasheets..
I rarely use subdatasheets as they are generally confusing for end users & cause a performance hit. In fact, I had already suggested you got rid of them

My Customer wants to have all in one main form. And i have this for him.
A lot of subforms based on juntion tables and comboboxes. You can easily add all atributes and not clicking all the time to open another separete forms with another subforms. All in one place - very user friendly. And also working already one year without complaints.

Fine by me. Its your database. Do what your customer wants as long as it works

Ok returning to issue:

I afraid that filtering can be change only via VBA - after selecting record on one subform i have to change recorset on subform and requery it. and thanks to it i can see expected result.

But you've solved that again have you?

Good luck with your project

 

[isladogs]

You asked for an example of complex forms - the attached screenshots show a form with 13 subforms.
It takes about 2 seconds to load despite its complexity

You can't see all details in design view but the subform on the left contains 3 subforms - 2 nested / one separate

 

[jaryszek]

thank you Ridders,

nice solution. It is very similar to mine. And these subdatasheets are very fast in my database also. It takes the same time as in your model to load main form with a lot of paraller subforms linked to textboxes.

So differences are not that big as i thought
But good to know!

Yes problem is solved.

Best,
Jacek