Full Version: Multiple Parent/child Relationships With Form/subform
UtterAccess Forums > Microsoft® Access > Access Forms
toons
I currently have a form with an imbedded datasheet subform. the form has 5 fields that will ultimately be used to filter the records displayed in the subform. at this time I can only get 1:1 to work. how do I get the subform to recognize the 5 potential filtering fields from the main form, that can either be used 1 at a time or maybe even altogether and drilling the displayed data down even more.
The main forms fields are: xmit_sr;from_sr;vendor_sr;code_sr;action_sr
the corresponding subforms fields are: xmit_no;xmit_from;vend_id;xmit_code;xmit_action
Otried putting the field names in the subform's parent/child properties like that, but none of the fields worked. only when I tried one at a time. as always, your help i greatly appreciated.
vtd
>>that can either be used 1 at a time or maybe even altogether and drilling the displayed data down even more.<<
inkMasterFields/LinkChildFields don't work that way. If you specify 5 Fields, Access will only select the rows/records that have all 5 Field values that match the 5 corresponding Field values in the MainForm.
toons
thats what I figured. is there some sort of way, possibly in VBA, that it could use If Then statements to determine which fields to use in the filter. Like, If a field is Null, then it's not used, but if it contains a value the user enters, that value is used to filter and display data in the subform?
vtd
In this case, it may be easier to base the SubForm on a parametrised Query using the references to the Fields on the MainForm as Parameters. Your code can then just Requery the SubForm when appropriate.
heck Access VBA Help on the Requery method.
toons
I just tried that, but adding criteria like [Forms]![frm_xmit_lookup_list]![xmit_sr] in each corresponding field still wants all fields to match accordingly, or be Null if I dont enter values in the main form. So when I enter a value in to 1 field at this point, nothing loads in the subform because the rest of the fields arent matching up at the same time also.
vtd
Add the Is Null ... For example, for the Column [xmit_no], the criteria should be:
Parent]![xmit_sr] Or ([Parent]![xmit_sr] Is Null)
toons
doesnt that basically state that if the field [xmit_no] must equal [xmit_sr] or it has to be Null? what if it was another value altogether?
vtd
>>doesnt that basically state that if the field [xmit_no] must equal [xmit_sr] or it has to be Null?<<
No. The condition means that
if [xmit_sr] is Null, the combined condition is always True so all [xmit_no] values will be selected subjected to other condition.
* if [xmit_sr] is not Null then [xmit_no] must be equal to [xmit_sr].
toons
yes, but using 1 or more of the fields in the subform. going back to the field names
The main forms fields are: xmit_sr;from_sr;vendor_sr;code_sr;action_sr
the corresponding subforms fields are: xmit_no;xmit_from;vend_id;xmit_code;xmit_action
let's say
Otype 400 in to [xmit_sr] and nothing in the other fields of the main form. the subform should display the record for the value 400 in the [xmit_no] field. in an instance like this, there will probably only be 1 result.
in a different instance, I might select the 2nd option from the [code_sr] dropdown on the main form and not specify any criteria in any other fields on the main form. this can easily have many records load in the subform linking back to [xmit_code].
in a 3rd instance, I might select the 3rd option from the [action_sr] dropdown AND the 1st option on the [code_sr] dropdowns, and nothing else. this would obviously filter the subform data a little further since 2 criteria has been specified.
In just about every case, none of the fields on the main form with corresponding fields in the subform will have null data. those are mandatory fields. I've tried switching it to Is Not Null, but when I do that and specify at least 1 value in the main form it isnt filtering, but instead just displaying all records.
vtd
Sorry, I edited the post when you replied. Please re-read my ealier post.
toons
so will that work with the explanation I made in my last post? I can ty it again, but it wasn't filtering based on any criteria before
DIT:
well, it does filter on 1 or more criteria, so it captures that much. Im guessing that until something is specified in the search criteria it will be displaying all records in the subform, and not an empty form until I start entering values in those fields. thanks for helping with getting the filter working properly!
vtd
Just try it...
The problem is that you need to ensure that "other" Controls on the MainForm must be Null and not some invisible value such as empty String or white-space characters.
Remember that you have to requery the SubForm...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.