There is no Menu type combo box in frm_Recipe2.
frm_Recipe3 has MealTypeID bound to 2 controls and the combo box row source doesn't include the ID column.
frm_Recipe4 has the same error since its row source is just the text field, not the ID.
ditto for frm_Recipe5.
The record source of frm_Recipe5 includes all of the lookup tables with INNER JOINs. Change them to RIGHT JOINs and you will see all the records:
SELECT tbl_Recipe.RecipeName, tbl_Recipe.Description, tbl_Recipe.Servings, tbl_Recipe.DryIngredients, tbl_Recipe.WetIngredients, tbl_Recipe.Cost, tbl_Recipe.Directions, tbl_Recipe.Suggestion, tbl_MealType.MealType, tbl_RecipeCategory.RecipeCategory, tbl_RecipeType.RecipeType
FROM tbl_RecipeType RIGHT JOIN (tbl_RecipeCategory RIGHT JOIN (tbl_MealType RIGHT JOIN tbl_Recipe ON tbl_MealType.MealTypeID = tbl_Recipe.MealTypeID) ON tbl_RecipeCategory.RecipeCategoryID = tbl_Recipe.RecipeCategoryID) ON tbl_RecipeType.RecipeTypeID = tbl_Recipe.RecipeTypeID;
Why don't you delete qry_MealType? It is just leading you astray. And, why not just use frm_Recipe since it seems to work? Why all the others?
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, "yshopper" <mossj5@...> wrote:
>
> I fixed them before I uploaded. I just uploaded a new 03 version DB.
>
> frm_Recipe2 stores the same date in the menu type combo box. frm_Recipe3 says the Value isn't valid.
> frm_recipe4 has the same error.
> frm_Recipe5 only shows 2 records.
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >
> >
> > Which form doesn't work? I tried the frm_Recipe and it seemed to work like I would expect. Duane HookomMS Access MVP
> > To: MS_Access_Professionals@yahoogroups.com
> > From: mossj5@
> > Date: Wed, 1 Feb 2012 02:22:28 +0000
> > Subject: Re: [MS_AccessPros] Combo Box Lookup
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > I also uploaded the 03 version of the DB to
> >
> > http://tech.groups.yahoo.com/group/MS_Access_Professionals/files/Recipe/
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Moss <mossj5@> wrote:
> >
> > >
> >
> > > here's what I have
> >
> > >
> >
> > > a simple recipe DB with 4 tables; tbl_Recipe, tbl_MealType,
> >
> > > tbl_RecipeType, tbl_Category.
> >
> > >
> >
> > > the tbl_MealType, tbl_RecipeType, tbl_Category have a one to many
> >
> > > relationship to the tbl_Recipe, each meal type can have many recipes, etc..
> >
> > >
> >
> > > If I create a form based on a query and only use the tbl_Recipe fields
> >
> > > that do not include the Foreign Keys to the other tables, then when I
> >
> > > add the combo box and I use the option "Remember the Value for later
> >
> > > use" I get the same data for each record in the lookup combo box.
> >
> > >
> >
> > > If I use a query that includes the Foreign Keys, I get the same result.
> >
> > >
> >
> > > If I create a form and base it on a query and I choose "Store Value in
> >
> > > Field" and I select the Foreign Key OD field, I get a "Value is not
> >
> > > Valid" error.
> >
> > >
> >
> > > If I create a form and base it on a table and choose "Store Value in
> >
> > > Field", then it works.
> >
> > >
> >
> > > It's obvious I doing something stupid, but can't figure it out.
> >
> > >
> >
> > > Thanks,
> >
> > > John M.
> >
> > >
> >
> > > On 1/31/2012 1:25 PM, John Viescas wrote:
> >
> > > >
> >
> > > > John-
> >
> > > >
> >
> > > > You should normally use a query as the Row Source of a combo box, but
> >
> > > > a table
> >
> > > > can work, too. Are you saying it only works using a table and does not
> >
> > > > using a
> >
> > > > query?
> >
> > > >
> >
> > > > John Viescas, author
> >
> > > > Microsoft Office Access 2010 Inside Out
> >
> > > > Microsoft Office Access 2007 Inside Out
> >
> > > > Building Microsoft Access Applications
> >
> > > > Microsoft Office Access 2003 Inside Out
> >
> > > > SQL Queries for Mere Mortals
> >
> > > > http://www.viescas.com/
> >
> > > > (Lahaina, HI)
> >
> > > >
> >
> > > > -----Original Message-----
> >
> > > > From: MS_Access_Professionals@yahoogroups.com
> >
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> >
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of yshopper
> >
> > > > Sent: Tuesday, January 31, 2012 10:16 AM
> >
> > > > To: MS_Access_Professionals@yahoogroups.com
> >
> > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > > > Subject: [MS_AccessPros] Combo Box Lookup
> >
> > > >
> >
> > > > I added a combo box to a form to use as a lookup. Why does this work
> >
> > > > using a
> >
> > > > Table instead of a Query?
> >
> > > >
> >
> > > > Thanks for any insight.
> >
> > > >
> >
> > > > John
> >
> > > >
> >
> > > > ------------------------------------
> >
> > > >
> >
> > > > Yahoo! Groups Links
> >
> > > >
> >
> > > >
> >
> > >
> >
> > >
> >
> > >
> >
> > > [Non-text portions of this message have been removed]
> >
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
Tidak ada komentar:
Posting Komentar