Changing main table entry based on last corresponding record in su

[Guest]

I have two tables:

LOT_INFO - contains information on date produced, amount, and if released
for shipment.
INSPECTION_HISTORY - contains information on each inspection that the lot
goes through, liked to main table by LOT#


I have a form with a sub form. The main form alows entry for the data
associated with the lot and the sub form alows entry for each inspection.

A lot can have from one to an infinent nubmer of inspections, basically it
is inpected, reworked if required, inspected, reworked if required etc. The
sub table includes a field that indicates if the LOT must be reworked or not
for each inspection.

What I would like is to have the value of the filed in the main table, that
indicates if the lot can be shipped or not, be dependant on the value of the
field in the subtable that indicates if it must be reworked or not for the
LAST record corresponding to that LOT in the sub table.


Any help would be great.

 

[kingston via AccessMonster.com]

There must be two pieces of information in the INSP table for this to work -
something to indicate the order of the records (e.g. Date/Time) and the
inspection result. You can include a timestamp field in the INSP table that
populates automatically. Then, you would look for the record with the
maximum value of the timestamp and return the corresponding value in the
status field for the lot in question.

One way is to use the DMax and DLookup functions:
LotStatus = DLookup("[Status]","[INSP]","[Timestamp]=#" & DMax("[Timestamp]",
"[INSP]","[LOT]=" & Me.Lot) & "# AND [LOT]=" & Me.Lot)
This assumes your Lot ID is a number.

 

[Al Campagna]

Ed,
You didn't provide enough detail as to what entry in the sub would constitute "can be
shipped", nor do you indicate what the "can be shipped" field is on the main form.
So... I'll use my own example... you intepret for your setup.

Let's say that when a Lot is ready to be shipped, a field in the last inspection record
(ex. Status) would contain a value like "Ship"
And, a field named [CanBeShipped] on the main form is a T/F check box.
Using the AfterUpdate event of the inspection field [Status]...
If [Status] = "Ship" Then
Forms!MainFormName![CanBeShipped] = True
End If ' (no Else needed)

That should do it...
However, that is redundant. Given that a Lot has (or hasn't) a related Inspection
record with a Status of "Ship" is all you need to define a Lot as "shippable"... in any
subsequent query, form, or report. Actually, CanBeShipped could be a "calculated" field.
But, if the above works for you, it's no big deal...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."