compare status and return result (1 Viewer)

PWG

Registered User.
Local time
Today, 15:19
Joined
Jun 13, 2019
Messages
56
I have a form called Jobs on that form I have a sub form Parts.
As i enter part numbers each one returns a status for that part. ie: in stock, no stock or on order.
What I want to do is look at all the parts and return a result if they are all in stock so that I can then use that to change the status on my main form to Job ready.
I need a code that will look and them all and return a result
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:19
Joined
Oct 29, 2018
Messages
21,358
Hi. You might be able to use DCount() for this one.
 

plog

Banishment Pending
Local time
Today, 17:19
Joined
May 11, 2011
Messages
11,612
You didn't provide table/field names, so I can only give you a general solution. Here it is:

You should have a Status table that translates your description to a Yes/No to determine if a part is available or not(e.g. In Stock=True, no stock=false, on order=false, etc.). With that you can create a query to determine if every part for a Job is available or not:

Code:
SELECT JobID
FROM Parts
INNER JOIN Status ON Parts.StatusID = Status.StatusID
GROUP BY JobID
HAVING (MAX(IsAvailable)=0)

(*the above might not be valid SQL)

Save that query and then you can do a DCount into it from your Jobs form to see if a record for it exists (Dcount=1)--if so all parts available, if no record (Dcount=0) then its parts are not ready.
 

Users who are viewing this thread

Top Bottom