form with two fields

[Gary Nelson]

In Access2000 frontend, SQL server backend.

I have a form with two fields - StartTime and EndTime. Once the EndTime
has been entered, this signifies that a "plate" has been completed.

I have been asked to create a "PlatesCompleted" field on a seperate report
which will show up on the the form once the EndTime field is filled in on
the form.

Can you offer some assistance as to how I can do this, and where is should
be written.

Below is the stored procedure for the report.
Alter PROCEDURE billm.spBMCoverSchedule AS
SELECT dbo.BookAll.JobNum,
dbo.[Cover Print Process].CvrPrntProcessID,
dbo.[Cover Print Process].CvrPrntProcess, dbo.BookAll.DateIn,
dbo.[Scheduling - AD].dtPrintCvr2,
dbo.[Scheduling - AD].dtPrintTxt2,
dbo.[Scheduling - AD].TxtPrfStatus,
dbo.[Scheduling - AD].CvrPrfStatus, dbo.BookAll.Author,
dbo.Publisher.PublisherCode,
dbo.[Binding Style].BindStyleAbbr, dbo.BookAll.TotalWidth,
dbo.BookAll.AllCovers + dbo.BookAll.ExtraCvrs AS Quantity,
dbo.[Cover Colors].CvrColor,
dbo.[Scheduling - AD].dtBNBInvRec,
dbo.[Scheduling - AD].dtBound,
dbo.[Scheduling - AD].dtShipped,
dbo.[Scheduling - AD].dtBound2,
dbo.[Scheduling - AD].BNBDelivery,
dbo.[Proof Job Type].ProofCode,
dbo.[Scheduling-LoadedDates].PrintCoverDue, Hold, HotJob,

'JobDueDate' =
CASE
WHEN [Hold] = 1 THEN '9/9/99'
WHEN ([DaysOnHold] Is Not Null AND [CustomerDelay] = 1 And [Take
Off Hold] = 1)
THEN (dbo.BoundBook.BoundBook + [DaysOnHold])
ELSE (dbo.BoundBook.BoundBook)

END,

'CoverStatus' =
CASE
WHEN [CvrPrfStatus] = '28' THEN 'RTP, Needs Plates'
ELSE 'RTP'

END,

'Text Status' =
CASE
WHEN [dtPrintTxt2] is not null THEN 'Text Printed'
When [dtPrintTxt2] is null and [TxtPrfStatus] = '12' Then 'RTP'
ELSE 'Not RTP'

END
FROM dbo.BookAll INNER JOIN
dbo.[Scheduling - AD] ON
dbo.BookAll.JobNum = dbo.[Scheduling - AD].JobNum INNER JOIN
dbo.[Job - ED] ON
dbo.BookAll.JobNum = dbo.[Job - ED].JobNum INNER JOIN
dbo.Publisher ON
dbo.BookAll.PublisherID = dbo.Publisher.PublisherId INNER JOIN
dbo.[Cover Print Process] ON
dbo.BookAll.CvrPrntProcessID = dbo.[Cover Print Process].CvrPrntProcessID
LEFT OUTER JOIN
dbo.[Proof Job Type] ON
dbo.[Scheduling - AD].CvrPrfStatus = dbo.[Proof Job Type].ProofCodeID
INNER JOIN

BELOW IS THE VBA CODE FOR THE EndTime field on the form.

Private Sub EndTime_Exit(Cancel As Integer)
If EndTime < StartTime Then
MsgBox "The End Time must be Later than the Start Time"
EndTime = Null
EndTime.SetFocus
End If
End Sub

Private Sub EndTime_GotFocus()
If IsNull(JobNum) Then
MsgBox "You must Enter the Job Number."
JobNum.SetFocus

ElseIf IsNull([f1]![Media]) Then
MsgBox "Please Enter the Cover Media Processed"
f1.SetFocus

ElseIf IsNull(txtStatus) Then
MsgBox "Please Enter the Status"
txtStatus.SetFocus

ElseIf txtStatus = 2 And CorrBill.Value = False And CorrNoBill.Value =
False Then
MsgBox "You Must Check if the Corrections are Billable or
Non-Billable"
CorrBill.SetFocus

ElseIf IsNull(StartTime) Then
MsgBox "Please Enter the Start Time"
StartTime.SetFocus

End If
End Sub