field control source depends on other field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 fields Status (List box) and Resolution Date (text box). I have
been able to write the if statement =IIf([Status]="resolved",Date()) in the
control source of resolution date fiel to capture the day the status changed
to resolved. How can I get that date to be stored in my table field resolved
date?
 
Use the AfterUpdate event of the list box to set the value of the text box.

Set the AfterUpdate property of the Status list box to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

Enter these 3 lines between the "Private Sub..." and "End Sub" lines:
Private Sub Status_AfterUpdate()
If Me.Status = "resolved" Then
Me.[Resolved Date] = Date
End If
End Sub

Note that it is possible that the list box has a hidden column, so its value
might be 2 or something rather than the text "resolved". Adjust the code
above to use the number (without quotes) in place of the word if that is so.
 
A formula in a cotrol source can only be used on unbound fields. The control
source should be the field in the table you want to update. Move your code
to the After Update event of the list box. Actually I doubt a list box is
the correct control to use here. I would suggest a combo instead. In any
case:
If Me.Status = "resolved" Then
Me.ResolutionDate = Date
End If
 
Klatuu said:
A formula in a cotrol source can only be used on unbound fields. The control
source should be the field in the table you want to update. Move your code
to the After Update event of the list box. Actually I doubt a list box is
the correct control to use here. I would suggest a combo instead. In any
case:
If Me.Status = "resolved" Then
Me.ResolutionDate = Date
End If
Click to expand...
Ok I tried this code:
Private Sub Status_AfterUpdate()
If Me.Status = "resolved" Then
Me.ResolvedDate = Date
End If
End Sub

I have also changed the control source of Resolved Date to Resolved date
rather then the formula. I still am not capturing the date in resoved date
field after I change the status to resolved.
 
Actually it is working now!!! I did not realize the code was case sensitive
for status equaling "resolved" needed to be "Resolved"

THANK YOU VERY MUCH!

Private Sub Status_AfterUpdate()
If Me.Status = "resolved" Then
Me.ResolvedDate = Date
End If
End Sub
Klatuu said:
A formula in a cotrol source can only be used on unbound fields. The control
source should be the field in the table you want to update. Move your code
to the After Update event of the list box. Actually I doubt a list box is
the correct control to use here. I would suggest a combo instead. In any
case:
If Me.Status = "resolved" Then
Me.ResolutionDate = Date
End If

Kevin76 said:
I have 2 fields Status (List box) and Resolution Date (text box). I have
been able to write the if statement =IIf([Status]="resolved",Date()) in the
control source of resolution date fiel to capture the day the status changed
to resolved. How can I get that date to be stored in my table field resolved
date?
Click to expand...
Click to expand...
 
Back
Top