Link to home
Start Free TrialLog in
Avatar of Papote
PapoteFlag for Puerto Rico

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.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Papote,

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
Avatar of Papote

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.
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
Avatar of Papote

ASKER

Sorry I didn't notice that there was a database attached.
Avatar of Papote

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
Papote,

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
Avatar of Papote

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
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial