UtterAccess Discussion Forums > If statement for dependency between two option groups

Full Version: If statement for dependency between two option groups

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

knagen

Aug 22 2006, 03:50 AM

Hi UA

I have a problem making the code for an "If" statement and was hoping that you could help me.

I have a table with the following relevant fields:

tblPunchList
PunchListID pk autonumber
dtCloseContractor date/time
dtCloseClient date/time
StatusContractor yes/no
StatusClient yes/no

On my main form I have two separate option groups; "fogStatusContractor" and "fogStatusClient" referring to the above fields. Both option groups are either "Open" or "Closed" (Closed=Yes, Open=no). I like to prevent users from Closing the "fogStatusClient" option group before the "fogStatusContractor" is "closed", i.e. if "fogStatusContractor" is Open (StatusContractor field in table equal "No"), then "fogStatusClient" must be "Open" (StatusClient field in table equal "No").

I write the date into my table, when items are closed.

CODE

Private Sub fogStatusClient_AfterUpdate()
    Me.txtdtPLCloseClient = Date
End Sub

Private Sub fogStatusClient_BeforeUpdate(Cancel As Integer)
    If (Me.StatusContractor = False) Then
            'MsgBox "Settting Date for Contractor Closed: " & Now()
            Me.StatusClient = False
            MsgBox "You can not close the item before the item has been closed by the Contractor"
    Else
            MsgBox "Settting Date for Client Close: " & Date
            Me.txtdtPLCloseClient = Date
            Me.cbofkCloseClientID = lngMyEmpID
            Me.StatusClient = True
    End If
End Sub

Ultimately, I do not want to have the message boxes to appear, but these are just used for information during development. Currently, the above code does not prevent a change in "StatusClient" even though "If" statement is not valid. Can some one help?

Lastly, what is a good syntax for an option group control? I have currently used fog for Frame Option Group.

HiTechCoach

Aug 22 2006, 07:36 PM

The BeforeUpdate has a Cancel parameter.

To stop the change you need to use:

Cancel = True

Not sure if this is correct, but is should be something like:

CODE

Private Sub fogStatusClient_BeforeUpdate(Cancel As Integer)

    If (Me.StatusContractor = False) Then

            'MsgBox "Settting Date for Contractor Closed: " & Now()

            Me.StatusClient = False

            MsgBox "You can not close the item before the item has been closed by the Contractor"

       ' Cancel the Update

           Cancel = True

    Else

            MsgBox "Settting Date for Client Close: " & Date

            Me.txtdtPLCloseClient = Date

            Me.cbofkCloseClientID = lngMyEmpID

            Me.StatusClient = True

    End If

End Sub

knagen

Aug 22 2006, 09:02 PM

The Cancel = True command seems to prevent users from changing the status in the table as desired, however the option group in the form accepts the change, i.e. the radio button shows "closed" rather than going back to "open".
The code also "locks" users at the current record, i.e. I can not go to the next or previous record using the navigation buttons. This is even if I select the "open" radio button...

What do I need to look into here to make this work?

knagen

Sep 22 2006, 03:17 AM

Hi UA

I finally figured out a solution that works for me. Here is the code that I ended up with... no If statement, but purely looking at the value of the option group. I also figured out a way to clear all controls, if the user for some reason re-opens an already closed item. Hope this helps someone else.

CODE

Private Sub fogStatusClient_AfterUpdate()
    If (Me.StatusClient = False) Then
        Me.txtdtPLCloseClient = Null 'setting closed date to Null as defect item has Open status
        Me.cbofkCloseClientID = Null 'setting closed by to Null as defect item has Open status
        Me.txtdtPLCloseClient.Locked = True 'lock control until Status Client is closed
        Me.cbofkCloseClientID.Locked = True 'lock control until Status Client is closed
    Else
        Me.txtdtPLCloseClient = Date 'setting date when defect item is closed
        Me.cbofkCloseClientID = lngMyEmpID 'used to automatically record user who closed defect item
    End If
End Sub

Private Sub fogStatusContractor_AfterUpdate()
    If (Me.StatusContractor = False) Then
        Me.txtdtPLCloseContractor = Null 'setting closed date to Null as defect item has Open status
        Me.cbofkCloseContractorID = Null 'setting closed by to Null as defect item has Open status
        fogStatusClient.Enabled = False
        fogStatusClient.Value = False
        Me.txtdtPLCloseClient = Null 'setting closed date to Null as defect item has Open status
        Me.cbofkCloseClientID = Null 'setting closed by to Null as defect item has Open status
    Else
        Me.txtdtPLCloseContractor = Date 'setting date when defect item is closed
        fogStatusClient.Enabled = True
    End If
    'Me.cbofkCloseContractorID = lngMyEmpID 'used to automatically record user who closed defect item
End Sub

Private Sub Form_Current()
    If (Me.StatusContractor = False) Then
        fogStatusClient.Enabled = False
    Else
        fogStatusClient.Enabled = True
    End If

    If (Me.StatusClient = False) Then
        Me.txtdtPLCloseClient.Locked = True 'lock control until Status Client is closed
        Me.cbofkCloseClientID.Locked = True 'lock control until Status Client is closed
    Else
        Me.txtdtPLCloseClient.Locked = False 'unlock control as Status Client is Closed
        Me.cbofkCloseClientID.Locked = False 'unlock control as Status Client is Closed
    End If
End Sub

Let me know, if there is a smarter way of doing this.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.