Relationship problem when inserting records

[Papote]

I am making a menu ordering system.

These are the main tables:
MenuByCycle
MenuByCycleID (PK)
Year
CycleID

MenuByDay
MenuByDayID
Day
MenuByCycleID (FK to MenuByCycle)

MenuByDiet
MenuByDietID
TypeOfDietID (PK)
MenuByDayID (PK, also a FK to MenuByDay Table)

FoodPerMenu
FoodByMenuID (PK)
MenuByDietID (FK to MenuByDiet Table)
FoodID

In a year there are 3 cycles.
The Menu changes daily depending on which cycle it is on.
There are different types of diets, and each day there is a food menu for each type of diet.
The MenuByDiet is a junction table between MenuByDay and FoodPerMenu.

Via data sub-sheets it works great but once I make a query to add a record in FoodPerMenu table it creates a duplicate MenuByCycle, and MenuByDiet records.


SQL Code:

In the query I set the Right joins so I could be able to enter data in the fields.

SELECT MenuByCycle.CycleID, MenuByCycle.Year, MenuByDay.Day, 
MenuByDiet.TipoOfDietID, FoodPerMenu.FoodID,   
FROM ((MenuByCycle RIGHT JOIN MenuByDay ON MenuByCycle.MenuByCycleID = MenuByDay.MenuByCycleID) RIGHT JOIN 
MenuByDiet ON MenuByDay.MenuByDayID = MenuByDiet.MenuByDayID) 
RIGHT JOIN FoodPerMenu ON MenuByDiet.MenuByDietID = 
FoodPerMenu.MenuByDietID;

[FishVal]

Hello, Papote.

Pay attention to the fields retrieved by the query you've posted. Those participating in relationships are all PKs of the correspondent tables. No wonder, form control bound to such a field will add new record to PK-side table rather than modify FK field value in FK-side table.

Regards,
Fish

[Papote]

The retreived fields aren't PKs. Only in the Order by clause.
I have retrieve the fields CycleID and Year from MenuByCycle table and Day from MenuByDay table to make a where statement it shouldn't appear in the end query. I am missing the MenuByDietID field from MenuByDiet table.

Hello, Papote.

Pay attention to the fields retrieved by the query you've posted. Those participating in relationships are all PKs of the correspondent tables. No wonder, form control bound to such a field will add new record to PK-side table rather than modify FK field value in FK-side table.

Regards,
Fish

[FishVal]

Ok, I've experimented with that a while.
Apparantly, Access join automation creates records properly only if correspondent FK is retrieved within query. Check whether is it so in your query.
Also, relationship between [MenuByDiet] and [FoodPerMenu] tables is not clear.

Regards,
Fish.

[Papote]

This is what I originally had.
zshare.net/download/19770145d4f6257a/
Pass:tester0001.?
Some items are in spanish...

I include a working version of the form used to enter / view the menu MenuByCycleEntry.

I wanted the subforms to link like the MenuByCycle table nested sub-datasheets upto ordering by institution per food menu item.

There are 13 different types of diets for each day.
Each type of diet has it's own menu which contains different food.

[Papote]

I managed to to do this via code. A couple of Ifs and dlookups. Works pretty good controlling the the records that need be created.

I am worried that when I upsize to MS SQL Server 2005, that the Dlookups won't work.