Solved

DAO compare records.

Posted on 2008-10-31
12
179 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
ID: 22860604
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
ID: 22861238
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
ID: 22865945
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
ID: 22871586
Sorry I didn't notice that there was a database attached.
0
 

Author Comment

by:Papote
ID: 22871736
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22873973
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
ID: 24340928
Papote,

My sample does as requested.

Why the delete request?

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24342348
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
ID: 24345875
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
ID: 24346039
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
ID: 24349594
:-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

895 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

14 Experts available now in Live!

Get 1:1 Help Now