Link unbound form to bound form

[Papote]

I have a database that in Table form it shows the subdatasheets that make
refrence to the main table. It is basically a Menu ordering system. Goes
something like this:
MenuByCycle (Contains: Cycle and year), MenuByDay (Contains: Day), MenuByDiet
(Contains: TypeOfDiets), FoodItemsByMenu (The actual menu items)
That was my best attempt to normalize the database.
Like I said in the beginning, in Table subdatasheet form I can view the sub
tables and add values. I am trying to port this to Forms.
I have made a form that filters (via Recordset) a subform to the
FoodItemsByMenu table. I can view the subform (datasheet) by each type of
filter, but I want to be able to link the foreign keys from the parent form
so that I don't have to enter them repeatedly. The parent form is unbound
since it is just to search by cycle, year, day and TypeOfDiet. This
information filters the subform so it displays the FoodItemsByMenu table to
view and input new records if necesarry.

I tried using the same query as record source for the main form as it really
doesn't matter, since it says can't build a link between unbound forms, and
didn't work.

Can I try a OpenArgs?

 

[Arvin Meyer [MVP]]

If you are using combo or list boxes for the rowsource of your input data,
you do not need to bind the mainform at all.

My guess is that you are using lookup fields in your tables, which together
with your subdatasheets will destroy performance on more than a few hundred
records. Both of these should be avoided like the plague.

http://www.mvps.org/access/lookupfields.htm

http://www.granite.ab.ca/access/performancefaq.htm

There are other alternatives which are the correct methods of database
design. Have a look at MVP Crystal Long's tutorial:

http://www.accessmvp.com/Strive4Peace/Index.htm

 

[Papote]

I use the combo boxes so in the end it creates a SQL statement using the
Recordset property in VBA that is used to filter a subform.
It works perfect to get the subform table, but I just want to be able to add
records that are automatically linked to the same group of records (Foregin
keys), like a Linked table works using a Form Wizard. Can't use a wizard
since I have a lot of tables that relate to one another.

 

[Papote via AccessMonster.com]

I notice my problem goes back to the query itself.

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:

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;

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