Solved

[Access 2000] fill subform based on selection in dropdown

Posted on 2004-04-21
10
674 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 10876110
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
ID: 10876277
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
ID: 10876315
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mazzl
ID: 10876425
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
ID: 10876475
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 65 total points
ID: 10876490
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
ID: 10876546
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
ID: 10876580
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
ID: 10876694
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
ID: 10876749
Glad it worked out mazzls.

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

Nic;o)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

628 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