Solved

Checking detail file in header/detail relationship

Posted on 2014-04-07
2
235 Views
Last Modified: 2014-04-09
Hi,

I have a car repair business.

The header table is the "order" table with the customers name and address etc.
The detail is each part number used in the repair.

When the part number is unknown I use a dummy part code of "999".

However,I cannot flag an "order" as complete IF it has any details part number of "999".

SELECT Count(*) AS CountMissingNumbers, tblDetail.CustomerID
FROM tblDetail
GROUP BY tblDetail.[windowcode], tblDetail.CustomerID
HAVING (((tblDetail.[windowcode])="999") AND ((tblDetail.CustomerID)=[forms]![job1]![CustomerID]));

Open in new window


See snippet of code which checks for part number ="999".

Question: How do I "embed" this SQL into my VBA?

For purposes of the example, let's say I have a button on the order header which says "Complete Order".  This button calls my SQL check (and rejects the command if there are any 999's)
0
Comment
Question by:Patrick O'Dea
2 Comments
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 500 total points
ID: 39984369
use DCOUNT() function with order# as a param

  If DCount("ID", "Order Detail", "Order_ID=14 AND Part_Code=999") > 0 Then

  End If
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39990196
Excellent - works well
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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