'refreshing' problem (?) in subform?

[BennyLinton]

I have a combobox dropdown "certStatusID" in a subform that is correctly displaying one of two labels correctly upon a change in value "lblRelinquished" or "lblRevoked". Problem is the label persists even after I select a diifferent record number in my main form... and ideas? Thanks!

Private Sub certStatusID_BeforeUpdate(cancel As Integer)
lblRelinquished.Visible = False
lblRevoked.Visible = False

Select Case Me.certStatusID
Case 1
testForDate = Me.applicationDate
Case 2
testForDate = Me.provisionalDate
Case 3
testForDate = Me.certificationDate
Case Else

If Me.certStatusID = 4 Then
lblRelinquished.Visible = True
lblRevoked.Visible = False
Else
lblRelinquished.Visible = False
lblRevoked.Visible = True
End If
Exit Sub
End Select

If IsNull(testForDate) Then
iDate = InputBox("Please enter a " & Me.certStatusID.Column(1) & " Date", , " ")
If iDate = " " Or iDate = "" Then
cancel = True
Me.certStatusID.Undo
Exit Sub
Else
If IsDate(iDate) Then
Select Case Me.certStatusID
Case 1
Me.applicationDate = iDate
Case 2
Me.provisionalDate = iDate
Case 3
Me.certificationDate = iDate
Case 4
Me.lblRelinquished.Visible = True
Me.lblRevoked.Visible = False
Case 5
Me.lblRelinquished.Visible = False
Me.lblRevoked.Visible = True
End Select
Else
MsgBox "You must Supply a valid date."
cancel = True
Me.certStatusID.Undo
End If
End If
Else
End If

End Sub

 

[Minty]

Allow me to make this more readable - please use the code tags ( # on the advanced editor)

Private Sub certStatusID_BeforeUpdate(cancel As Integer)

    lblRelinquished.Visible = False
    lblRevoked.Visible = False

    Select Case Me.certStatusID
        Case 1
            testForDate = Me.applicationDate
        Case 2
            testForDate = Me.provisionalDate
        Case 3
            testForDate = Me.certificationDate
        Case Else

            If Me.certStatusID = 4 Then
                lblRelinquished.Visible = True
                lblRevoked.Visible = False
            Else
                lblRelinquished.Visible = False
                lblRevoked.Visible = True
            End If
            Exit Sub
    End Select

    If IsNull(testForDate) Then
        iDate = InputBox("Please enter a " & Me.certStatusID.Column(1) & " Date", , " ")
        If iDate = " " Or iDate = "" Then
            cancel = True
            Me.certStatusID.Undo
            Exit Sub
        Else
            If IsDate(iDate) Then
                Select Case Me.certStatusID
                    Case 1
                        Me.applicationDate = iDate
                    Case 2
                        Me.provisionalDate = iDate
                    Case 3
                        Me.certificationDate = iDate
                    Case 4
                        Me.lblRelinquished.Visible = True
                        Me.lblRevoked.Visible = False
                    Case 5
                        Me.lblRelinquished.Visible = False
                        Me.lblRevoked.Visible = True
                End Select
            Else
                MsgBox "You must Supply a valid date."
                cancel = True
                Me.certStatusID.Undo
            End If
        End If
    Else
    End If

End Sub

You can hopefully now see that in cases 1,2 or 3 your buttons won't get set by anything. I'm also pretty certain your logic flow is a bit out of kilter, and if there is no date you'll never get to the bit to ask for a date anyway? but I may have that wrong.

 

[BennyLinton]

Thanks for the tip on the code tags... actually the code is performing well, this is some code in a database I inherited so I would have written it differently,
My main issue is the labels appear but persist after another record is chosen, e.g. I set one person as 'revoked' and label appears - I go onto another person and the value in the the combobox having looked up another status, still shows the 'revoked' label.

 

[BennyLinton]

Actually the subform is not displaying the 'revoked' label when I then main form to a 'revoked' person. I tried this but to no avail:

Private Sub certificationSubForm_Enter()
If Me.Form.certStatusID = 5 Then Me.lblRevoked.Visible = True
End Sub

 

[Minty]

You need to use the on current event - that fires when you move between records.

 

[BennyLinton]

OnCurrent in the main form?

 

[Minty]

OnCurrent in the main form?

Or the sub form either or both depending on your requirement!

 

[BennyLinton]

I placed this in the frmMain which houses the subform bu I get the error 'member not found':

Private Sub Form_Current()
If Me.certificationSubForm.Form!certStatusID = 5 Then Me.certificationSubForm.Form!lblRevoked.Visible = True
End Sub

 

[BennyLinton]

Here's what the sub form looks like: