Subform records based on OpenArgs value in main form

[tjkalb]

I hope this makes sense because for the life of me, I'm having trouble finding anything that I want, and it's probably how I'm typing my search. I have a form (beforeform) that opens and passes a value to another form (form). The value is a text field called EthYear. Since this field does not exist in the recordsource of the form, I have an unbound field that shows this value (called yearvar). I'm using OpenArgs to pass this value. Works great!

I also have a subform on the form. EthYear does exist in this recordsource. I want to only show records in the subform where EthYear is equal to yearvar in the main form. I've tried so many things that I'm getting myself confused. Since I'm open to all possible solutions, I'm not posting any code. Thanks in advance!:banghead:

 

[JHB]

Do you use the "Master Link Fields" and "Child Link Fields" or a filter or ???
Could you post your database with some sample data + name of the forms, zip it because of you missing 10 post.

 

[tjkalb]

Yes I do have master and child links based on another common field between the form and subform. The field is called cat in master and category in child.

I cannot post the database due to confidential info, and there's too many forms, tables, queries, etc. to post the whole thing even if I delete the data. Let me see if I can copy the tables, forms, ... that this uses and put fake data in it. If I can, I'll post it in the next couple of hours. Thanks for your help!

 

[JHB]

If you've a master and child link then it should work, so I'll wait and see you're able to post the database, then something must be wrong.

 

[missinglinq]

Yes I do have master and child links based on another common field between the form and subform. The field is called cat in master and category in child.

But you're not using the Fields in question, here, in the master and child links...correct? Why not? You can link on multiple Fields, which is what you're trying to do, here, if I read this correctly.

Linq ;0)>

 

[tjkalb]

You cannot link on a unbound field in the form - which is Yearvar and a bound field in the subform - which is EthYear. I attached a mini database. Not all the buttons will work because I didn't include all the forms, tables, queries, ...

 

[tjkalb]

Forgot to tell you (and can't figure out how to edit post)- When you open the database, open the form frmEthEntityCatNameYrLup. This will open a lookup screen. Pick a name from the pull down menu in the first field, and then pick a year in the bottom field. The year is the value I'm using for the OpenArgs in the form that will pull up. Then click Find Entity.

The form that opens is where my problem is. I want to only show records in the subform for that year I brought over in the OpenArgs. This field doesn't exist in the main form's table. It's just an unbound variable field. The year does exist in the subform - ethyear. Ethyear is a text field.

Please let me know if you need further explanation.

 

[tjkalb]

oops! Forgot to compress it.

 

[MarkK]

You cannot link on a unbound field in the form

Yes you can. In the subform control, set the property LinkMasterFields = cat;Yearvar, and set the LinkChildFields = Category;EthYear, and it will work.

 

[JHB]

As MarkK wrote, you can do it when you put it directly, (not using the "wizard").
Database attached.

 

[tjkalb]

Thanks guys! I was trying the parent/child in the wizard and that's why I couldn't get it to work. It works perfectly now! Sometimes you just need someone to brainstorm with when you are having a dah moment and you guys are the best!