Papote
asked on
DAO compare records.
I am using the DAO recordset in a Menu Ordering Access database with SQL server 2005 backend. I have 2 tables Menu Table and Order table. Since in the form I need to have shown the full daily Menu and let the customer just enter the quantity. The only way I can think of is to pass all the records in the particular day menu to the Order (FKs) table once the customer is to make the order, since almost all the items WILL be ordered anyways. This way I bring up the Order Table subform (with the Menu Table related FK items) and the customer can enter the quantity.
To do this I need to first check via RecordCount if the queried Order table has any records if not Insert them. What I want to do is if there ARE records, to compare them with the menu table. I am trying to find out if there is a simple way to search a record via DAO reordset.
To do this I need to first check via RecordCount if the queried Order table has any records if not Insert them. What I want to do is if there ARE records, to compare them with the menu table. I am trying to find out if there is a simple way to search a record via DAO reordset.
ASKER
Well these are the tables I had:
MenuItem (Table)
MenuItemID (PK)
FoodID (FK from Food Table)
MenuDayID (FK)
Order (Table)
OrderID (PK)
CutomerID (At the moment there are only 6 Customers, FK to Customer Table)
DateOrdered
OrderItem (Table)
OrderID (PK, FK to Oder)
MenuItemID (PK, FK to MenuItem)
Quantity
I've been told to run a append query to use an append query to fill the OrderItem and related Order tables.
MenuItem (Table)
MenuItemID (PK)
FoodID (FK from Food Table)
MenuDayID (FK)
Order (Table)
OrderID (PK)
CutomerID (At the moment there are only 6 Customers, FK to Customer Table)
DateOrdered
OrderItem (Table)
OrderID (PK, FK to Oder)
MenuItemID (PK, FK to MenuItem)
Quantity
I've been told to run a append query to use an append query to fill the OrderItem and related Order tables.
Papote,
"I've been told to run a append query to use an append query to fill the OrderItem and related Order tables."
That is basically what my sample does.
Did you even try it?
JeffCoachman
"I've been told to run a append query to use an append query to fill the OrderItem and related Order tables."
That is basically what my sample does.
Did you even try it?
JeffCoachman
ASKER
Sorry I didn't notice that there was a database attached.
ASKER
Ok, I see how that works but I want to restrict it from compying the same records over and over again everytime I hit the "Load Menu Items" button.
Papote,
Then the code gets complicated in order to account for every user action.
But I can add this one mod.
JeffCoachman
Access-EEQ23864798MenuOrders.mdb
Then the code gets complicated in order to account for every user action.
But I can add this one mod.
JeffCoachman
Access-EEQ23864798MenuOrders.mdb
Papote,
My sample does as requested.
Why the delete request?
JeffCoachman
My sample does as requested.
Why the delete request?
JeffCoachman
Papote,
<I want to restrict it from compying the same records over and over again everytime I hit the "Load Menu Items" button.>
...This is what the secod sample does:
http://filedb.experts-exchange.com/incoming/2008/11_w45/74520/Access-EEQ23864798MenuOrders.mdb
Did you test it?
Jeff
<I want to restrict it from compying the same records over and over again everytime I hit the "Load Menu Items" button.>
...This is what the secod sample does:
http://filedb.experts-exchange.com/incoming/2008/11_w45/74520/Access-EEQ23864798MenuOrders.mdb
Did you test it?
Jeff
ASKER
I went another route and made a routine that creates all the records from the menu table to the order table and if there are records, with a 2 dimension For loop will check each record if different. If the menu was changed then it will add or delete the record from the order table. I used DAO recordsets.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:-)
First, If one Order can contain many Menu Items...
...And One Menu Item can be in many different Orders,...
... then you need a "Junction Table".
See the screenshot for a sample layout.
So, are you basically saying that you want every new order to include all the MenuItems?
If so, then here is a sample to get you started.
You will have to add your own validation for the records
JeffCoachman
100.JPG
Access-EEQ23864798MenuOrders.mdb