Form-Subform References

[indyaries]

Greetings,

I am using Access 97, SR2.

I have a form that tracks developmental assignments of employees (frm_DNO_Developmental Assignments) . On this form I have a subform (sbfrmDevelopmentalAssignments). The subform is set to display continuous forms.

An employee can have more than one developmental assignment, but not at the same time. In other words, employee Jane Doe was on a developmental assignment that ran from 01 Jan 02 through 01 Feb 02. On 01 Mar 02 she began another developmental assignment that does not expire until 31 Aug 02. On the subform I have two date fields to enter the start and end dates of the assignment ([Start_Date] and [End_Date]). On the main form I have a field called DevAssgn. This is a text box that will display a Y or N (text value; this is not a true-false field). The control source for this field is also called DevAssgn.

I would like to have this field display a Y if an employee is on a current developmental assignment, regardless if any other assignment they have had in the past has expired.

Any ideas on how I might best accomplish this? Thank you in advance!

PS: Forgot to post the code I've tried to use.
Private Sub Form_Current()
Set the form Developmental Assignments to change the Dev Assgn box from Y to N if the Assignment
End_Date has expired.

If Forms![frm_DNO_Developmental Assignments]![sbfrmDevelopmentalAssignments]![End_Date] >= Date Then
Forms![frm_DNO_Developmental Assignments]![DevAssgn] = "Y"
Else Forms![frm_DNO_Developmental Assignments]![DevAssgn] = "N"

End If
End Sub

 

[Fizzio]

You do not explicitly need a stored value for this as the answer can be derived from elsewhere. Make the control unbound and in the controlsource try
=IIf((DMax("[StartDateFieldName]","NameOfDevAssignTable", "[EmployeeID] =" & Forms!MainForm!EmployeeID) <= Date() AND DMax ("[EndDateFieldName]","NameOfDevAssignTable", "[EmployeeID] =" & Forms!MainForm!EmployeeID) >=Date()),"Y","N")

I think this is the correct syntax but I cannot check this at the minute.

Alternative method is to populate the textbox with VbCode via the on current event.

 

[indyaries]

Fizzio,

I've placed an unbound textbox on the Main Form (frm_DNO_Developmental Assignments), and pasted the expression you provided.

It returns a syntax error. Here is the error text:
"The expression you entered contains invalid syntax. You may have entered an operand without an operator."

Here is what I've pasted into the texbox:
=IIf((DMax("[Start_Date]","DevelopmentalAssignments", "[EmployeeID] =" & Forms!frm_DNO_Developmental Assignments!EmployeeID) <= Date() AND DMax ("[End_Date]","DevelopmentalAssignments", "[EmployeeID] =" & Forms!!frm_DNO_Developmental Assignments!EmployeeID) >=Date()),"Y","N")

Bob

 

[Rich]

Add an unbound textbox to the subform footer, set its control source to =Max([End Date]
On the main form text box =Iif(Forms!MainFormName!SubformName!NewTextboxname >Date(),"Y","N")
You'll have to requery the subform after entering new data

 

[indyaries]

Hmmm....Still not working

Rich,

Still returning the same error as before.

I created a new unbound textbox in the FOOTER of the subform, calling it txtEnd_Date.

In the main form I created an unbound textbox, calling it txtDevAssgn:.

I pasted this expression into the control source of txtDevAssgn:

=Iif(Forms!frm_DNO_Developmental Assignments!sbfrmDevelopmentalAssignments!txtEnd_Date>Date(),"Y","N")


This is driving me bug-doodoo !! <smile>.
*************************************************
DISREGARD !! I've fixed it. I enclosed the form and field names in brackets.

Thank you all for your assistance. !!