Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

[Access 2000] fill subform based on selection in dropdown

Posted on 2004-04-21
10
Medium Priority
?
680 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 195 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 450 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

704 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