Solved

[Access 2000] fill subform based on selection in dropdown

Posted on 2004-04-21
10
656 Views
Last Modified: 2012-06-22
This should be an easy one for the veterans ;)

I have a table meal, meal-ingredient, and ingredient
I want to show all ingredients of a meal in the subform when you select a meal in de dropdown.

I have a dropdown box, and it is filled with a value from the table “meal”
This table contains an id, name, description
In the dropdown box you select the id, then I want access to look in the table meal-ingredient and select all the ingredient prim_keys connected to the meal. This table looks like

Meal_id | ingredient_id
1            5
1            8
1            10
1            2
2            5            
2            13


so now I know witch ingredient belong to a certain meal, and I want the subform to show all the properties of the ingredients. In a dataform, so I can manually put the amounts in,
like this, it shows : potato, small cut, and then fill in by user: amount  0.5 kg

this should not be so hard, I have now:
choice_menu --> propertys:  on change --> vba code

private sub  choice_menu_change()

Dim strChoice As String
Dim strQuery As String

strChoice =  me![choice_menu].Text
strQuery = Select meal.meal_id, ingredient.ingredient_id, ingredient.ingredient_description, ingredient.ingredient_ammount From ingredient innder join meal_ingredient_matchTabel on meail.meail_id = meal_ingredient_matchTabel.meal_id) On ingredient.ingredient_id = meal_ingredient_matchTabel.ingredient_id where (((meal.meal_id) = “& strChoice & “));

Me.Subform_ingredients.SourceObject = strQuery

end sub

The problem is.. access keeps on insisting that the subform doesnt exist…
Can somebody please help me out here.. I don’t  thing this will be hard.. but I just can’t seem to get it right..


Thanks a lot!
Martijn
0
Comment
Question by:mazzl
  • 4
  • 4
  • 2
10 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Hello mazzl,

This seems like an incredibly hard way of doing something simple.
Why not save the query without the where clause then just add a subform control to your main form using the saved query.

Pete
0
 

Author Comment

by:mazzl
Comment Utility
becease the main form is nog linked to any table.
the dropdown is on it's own.
My problem is, how do i get the drop down to give the value selected to the query as a variable.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hmm to make your statement work use:
Me.Subform_ingredients.form.SourceObject = strQuery

And change your strQuery statement to:
strQuery = "Select meal.meal_id, ingredient.ingredient_id, ingredient.ingredient_description, ingredient.ingredient_ammount From ingredient innder join meal_ingredient_matchTabel on meail.meail_id = meal_ingredient_matchTabel.meal_id) On ingredient.ingredient_id = meal_ingredient_matchTabel.ingredient_id where (((meal.meal_id) = “ & strChoice & “));"

Normally however a linked subform will do te same without all this code.
Just single select the subform and fill under the datatab the name of the combo in the Master linkfield and the meal_id in the child link field.

Clear ?

Nic;o)
0
 

Author Comment

by:mazzl
Comment Utility
Me.Subform_ingredients.form.SourceObject = strQuery
--> doesnt allow the parameter SourceObject .. ? any idea to why ..

single select the subform? --> what do you mean
the main form is not linked to a table in my database, and i do not want to link it
i would only want to do this if i could still select the record in the dropdown menu.
so i don't have to use the "scoll / next" button at the bottom of the page

becease the main form is not linked i cannot attach the subform to it,
"the name of the combo in the Master linkfield and the meal_id in the child link field."
--> i don't get this option....
when i try to link master and child i get the error: not able to link beceause of non dependent foms (translated from dutch) ;)

thanks so far!!... more iedeas? .. i'm stuck.. second day now ...

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Oops, should be:
 Me.Subform_ingredients.form.RecordSource

The main form doesn't need to be bound to a table.
Just the subform bound to the combobox will do the trick. Access will however not show this possibility using the wizard, you need to type this "manual" in the linkage fields under the datatab of the subform's properties.
These properties only get visible after a single-click on the subform.

Clearer ?

Nic;o)


0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 65 total points
Comment Utility
Your main form does not have to be bound to make the subform link work.

In the Master link field just type the name of your combo box.
And in the Child link field put the name of the query link field.

It will work just as if the main form was a bound form.

Pete
0
 

Author Comment

by:mazzl
Comment Utility
Me.Subform_ingredients.form.RecordSource
--> runtime error: this one i have tried like over ten times.. always the same..
: the name you have specifies for the form does not comply with the rules for naming ms access form :(

"Just the subform bound to the combobox will do the trick"
ok that sure sounds like the why to go.. but i have never done that..

subform_ingredients --> right click --> propertys --> data

i just enter: sourceObject: Query.query_select_ingredients
                  subfield link: choice_meal.Text
                  mainField link: meal_id?
(menus translated from dutch)

where getting there?
thanx a lot for helping me out :)
 
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 150 total points
Comment Utility
Hmm, I guess you have a space in the subform's name like:
"subform ingredients"
I never use spaces as they only mean trouble. Try to use a prefix like:
sfrmIngredients

For the error, try:
 Me.[Subform ingredients].form.RecordSource

BTW you can try Dutch on me <LOL>
I would try:
Child/subform link field: Meal_id
Master linkfield: choice_menu

Just make sure the choice_menu returns the ID and not the meal

Nic;o)
0
 

Author Comment

by:mazzl
Comment Utility
Eureka ... it finally worked :)
if you look at how.. it IS easy .. i knew is ;)

dus ik zal maar ff dutch op je proberen.. heel erg bedankt!


For the error, try:
 Me.[Subform ingredients].form.RecordSource
--> do'n know why but still doesnt work.. but who cares .. i don't anymore :)

Child/subform link field: Meal_id
Master linkfield: choice_menu
--> this did work indead.. , you "forgot"  to mention rowSource ;)

Just make sure the choice_menu returns the ID and not the meal
--> yep .. had to make a small change.. . so thanks for the hint

thanx again ..
no i can finally start finishing this dooomed thing ..
thanx .. i love you guys ;)

mazzls



0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Glad it worked out mazzls.

"De mazzel en let voortaan op de kleintjes" :-)

Nic;o)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now