Solved

Checking detail file in header/detail relationship

Posted on 2014-04-07
2
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

732 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