Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Control Source for Option Group in Form (2000)

    In a data input form I created an Option Group with 2 values: Open (numeric value 1) and Closed (numeric value 2) - default value is 1. I then stored the numeric value (1 or 2) in the Record_Status field contained in the table DATAtbl.

    To automate the process for operators when they close a record and input a date in the Date _Received field (contained in the Form linking back to the field in the DATAtbl) (so the Open or Closed value in the Option Group will toggle between Open or Closed depending on whether or not a date is present in Date_Received) I changed the Option Group's control source from Record_Status to =IIF(IsNull([Date_Received]),"1","2"); however, because the control source is no longer Record_Status, the numeric value of 1 or 2 is no longer being stored in the Record_Status field in the DATAtbl.

    Is there any way I can use the calculated control source and store the value in the Record_Status field in the DATAtbl.

    Thanks for any and all help provided.

    Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated Control Source for Option Group in Form (2000)

    In the first place, there is no real need to store the status in a table, because it is derived information. You can calculate the status in a query, and use this query as basis for other queries, forms and reports. Besides saving a little bit of space, this will also ensure that the status is always up-to-date, because it is calculated when you open the query (directly or indirectly because it underlies a form or report).

    If you still want to store the status, set the Control Source back to the Record_Status field, and update the status in the After Update event of the text box bound to Date_Received:

    Private Sub Date_Received_AfterUpdate()
    If IsNull(Me.[Date_Received]) Then
    Me.[Record_Status] = 1
    Else
    Me.[Record_Status] = 2
    End If
    End Sub

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Control Source for Option Group in Form (2000)

    Thanks Hans.

    Using the query makes much more sense and since the form is already bound to a query, it was easy to transfer the calculated control to the query and change the control source in the form to the calculated control in the query.

    Thanks again,

    Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •