Solved

DAO compare records.

Posted on 2008-10-31
12
178 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:Papote
  • 7
  • 4
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:Papote
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:Papote
Comment Utility
Sorry I didn't notice that there was a database attached.
0
 

Author Comment

by:Papote
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Papote,

My sample does as requested.

Why the delete request?

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:Papote
Comment Utility
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
Yes but this this information is only now being revealed.

I posted the sample db 5 months ago, it fulfillied all that you had asked for up until that time.
Therefore, within 21 day of that post you should have accepted it or explained why you could not.

Since that time you were still active on this site, posting over 20 questions.
As far as I know alerts are sent out on questions that are inactive for more than 21 days.

Also bear in mind that this My original sample did all that was requested in the original question.
The "second" sample was an additional request that I also fullfilled above and beyond the original question.

In other words, 21 days past the last post date, my sample did exactly what was requested.
Therefore it should be accepted.

I am glad you found a solution yourself, but this does not change what should have happened 4 moths ago.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
:-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now