Solved

[Access 2000] fill subform based on selection in dropdown

Posted on 2004-04-21
10
666 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
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
Technology Partners: 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

679 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