Combo box used as criteria for tabulated subforms

[WindSailor]

I have a main form which has a combo box and four tabs containing subforms off of built forms of queries.
The user is to select a value from the combo box on the main form and then the subforms should refresh using that criterion from the combo box, and he or she should just be able to tab through the four different forms using the tabs located on the main form and enter the their data on those specific subforms.
How do I make the subforms refresh with the criteria from the main combo box?

Originally I had used the criteria from the combo box to open a form from a query (one at a time), using…

stDocName = “ABCD”
DoCmd.OpenForm stDocName, , , stLinkCriteria

Which worked fine, but opening and closing or minimizing and maximizing individual forms was a pain, so I tried using them as subforms on a main form and just tab through them, but I can’t get it to work properly...

 

[suepowell]

hi,

You need to make sure that the master child links between your main form and your subforms are correct.

The master link will be the name of the combo box on your main form, and you need to have the same field on each of your subforms so they can be linked together. The name fo the field does not have to be the same in all cases, as long as you put the correct field name in the child link for each subform.

Hope this helps.

Sue

 

[WindSailor]

OK... I finally got it.
You have to make the main form and the subform from the same source and link the two items (in my case it was a query).
I originally tried to make a generic form and add subforms to it, and when it came to link the items I came up with the error "Can't build a link between unbound forms".
I also tried to do it (provide input from a combo box to a field in the query and have it sorted) programmatically (kind-of) and it did fly, but it was UGLY and full of holes, a beginners way to make it fly.

If you can add to this it would be great...

Does the main form and the subforms have to be always from the same source, if not what are there other ways to link subforms?

Am I limited to linking subforms from the options listed in the wizard?

Any additional information or links would be great!

Thanks abunch!

 

[Happy YN]

in the onchange event of the combo, why don't you put code to requery the form and its subforms
me.requery
me.subform1.requery (not sure of correct syntax to reference subform)
me.subform2.requery
etc

 

[WindSailor]

Thanks for the info...
Unfortunately that didn't work. I tried requerying the form, tabs, subform etc. with no luck. When using the syntax of Me. the intellisense didn't see the items or details of or in the subform, just the subform itself. - (tying to expand off of your idea and pass a value to one of the fields in the subform to see if it would sort that way) - This reply is in using a generic form and attaching subforms (no master/child links) - my original form was with one generic form and 4 subforms all from different queries. I also tried variations of the DoCmd... with no help so far, keep in mind my programming skills are not very good, and I may have missed something.

I tried briefly to change the record source and the row source of the combo box to see if I could get it to link, but the behavior of the combo box changed and I couldn't get the desired results. Honestly, I still really don't fully understand the Master/Child links on how to get them to apply and link to other various forms and queries.

The quick and easy way to by-pass the link issue was to put a criteria in the chosen field in the query to sort by in [] for instance... [Enter Dept. Number], have the combo box on the main form to list the option to enter a new Dept. Number and if yes, have code to close and reopen the form. The form would open by default with a new prompt and the subform would be correct. Not exactly what I wanted.

Do you know of another way to do this programmatically or otherwise that would apply to a generic form with multiple subforms from different queries?

Thanks!

 

[Rich]

Me.YourSubformControlName.Requery as the subform name appears on the main form property sheet and not the actual name of the subform in the db window. Use the CodeBuilder to obtain the correct references

 

[suepowell]

Hi

No the form and subforms do not have to be from the same source, but there must be 1 field that appears in the source of each from and subform, so access knows which record from the subform relates to which value on the main form.

Eg, If the value on the main form relates to a product with a unique code, that product code must be in the record of each subform.

so if one subform relates to the supplier for that product, and another relates to the customers, and another to the details of the specification, these details are probably stored in separate tables, but with the product code in each table so they can be related together.

as long as the product code is included in each subform, and is used in the child / master links, it should work.

You can create your own links when using the wizard, just click on the button to define your own, (can't remember the layout of the form off hand).

If you need more help either post or email me the part of the database you are having trouble with, or I will put together a sample if you want me to..

Sue

 

[WindSailor]

Thanks Sue.
Sorry for the delay.

Here was my basic mistake, which I totally missed researching the issue of Master/Child links; when I created my ‘generic’ form, I used the “Create Form in Design View” option. To my understanding what in essence I did was create an ‘Unbound’ form. Hopefully other people will catch what I obviously missed, and thanks to you Sue and your information, I finally got it.

To basically summarize what I have learned, you can not create a Master/Child link relationship with an ‘Unbound’ form; the main form itself has to be ‘bound’ to a table, query etc. somewhere in the relational sequence (for what you want to do) in order for the relationships or links to work, and then you can add your control item(s) etc., establish and organize your links etc. on all concerned forms and then work with the data that you have; at least this is the issue in Access 2003 that I have found.


Rich,

I will go back through and use the intellisense in the CodeBuilder to check and see if I had missed anything. The problem I had before assuming that I didn’t miss anything (again this is with an 'unbound' form) is that it would refresh but it wouldn’t use the combo box as criteria to refresh by.


Again, many thanks!