This is quite challenging for a newbie!
You need to count how many entries in the subform have empty dates.
I am assuming that the subform has link master and child fields.
We need to get the criteria set up correctly.
So look for empty dates in the labour detail table where the link child
field matches the link master field.
Create a query based on the labour table. Use-->
--the foreign key that relates to the WOEDIT table,
--the primary key of the labour table,
--the DateDone field
In the criteria row, under DateDone type
Is Null
Save this query as qChkJobStatus
The DCount expression will be something like this untested air code-->
-------------------------
Private Sub Status_Enter()
Dim lngCount as Long
Dim strWhere as String
strWhere = "[FKeyName] = " & Me!FKeyName
lngCount = DCount("*", "qChkJobStatus", strCriteria)
Debug.Print lngCount
If lngCount > 0 Then
Me.Status.RowSource = "OPEN;Out On Proof;At Printer;On
Hold;Cancelled;Waiting on
Someone Else"
Else
Me.Status.RowSource = "OPEN;Out On Proof;At Printer;Completed;On
Hold;Cancelled;Waiting on
Someone Else"
End If
End Sub
------------------------------------
Replace FKeyName with the name of the field which links the subform to the
main form.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Hi Sunflower,
DCount will help you here.
Use DCount to count how many rows in the subform have null date entries..
The date entries in the subform need to be saved before you use DCount,
but
that should happen automatically when user moves out of the subform to
click
the status on the main form.
VBA help explains how to use dcount.
You want to count how many records in the child table (with the matching
ID
to the main form) have null for DateDone.
If the count is > 0 then you lock the drop down status selector.
You could put the DCount code on the enter event of the drop down so that
it
always checks before users can choose a status.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
I have a form
Form: [frmWOEDIT]
with a drop down status selector
Dropdown field: [Status]
on my form [frmWOEDIT], I have a subform
Subform: [subfrmLABORDETAIL1]
with a text field and date field
Text Field: [Type)
Date Field: [DateDone]
My subform can have several rows (not always the same number) with
[Type], and the [DateDone] will be null until it is entered later.
MY PROBLEM:
I need a way to make sure when someone goes in and enters a
[DateDone], that they cannot change the [Status] on [frmWOEDIT] to
"Completed" unless ALL the rows in the [subfrmLABORDETAIL1] have date
entries in the [DateDone] field.
Any and all help in greatly appreciated- Hide quoted text -
I am a newbi, so I wasnt sure how to code it the way you suggested...
Here is my failed attempt
In the "On Enter" event of the the Combo Box: [Status]
I put
IIf ( DCount ( [DateDone] , tblLABORDETAIL)>0 , "Completed",
[Status])
no such luck
Also,
Row Source of Combo Box: [Status] is
OPEN;Out On Proof;At Printer;Completed;On Hold;Cancelled;Waiting on
Someone Else
if that helps?
Any and all help in greatly appreciated- Hide quoted text -
- Show quoted text -
