set property value on a subform control from control value on parent

[capndave]

I am trying to accomplish the following:

frmPtReg is parent form
combo49 on frmPtReg (when = "current" SmokeCessa control on subform is visible otherwise not visible)

frmEnc is subform
SmokeCessa is y/N control on subform

This is my code

Private Sub frmENC_Enter()

If Forms!frmPtReg!Combo49 = "Current" Then
Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = Yes

Else
Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = No

End If
End Sub

First time I enter subform frmEnc SmokeCessa disappears and never becomes visible again, even if Combo49 = "Current"

Is this the correct event to use?
Syntax error?

Thanks in advance
CapnDave

 

[kjv1611]

I'm thinking something else may be amiss in the code, but just to see, have you tried using Form.Refresh or Form.Requery after setting the visible property?

Private Sub frmENC_Enter()

    If Forms!frmPtReg!Combo49 = "Current" Then
       Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = Yes
       Forms!frmPtReg.Requery
       Forms!frmPtReg!frmENC.Requery
    Else
        Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = No
        
    End If
End Sub

Anyway, I would play around with the refresh/requery methods of the form and/or subform.

 

[capndave]

Thanks kjv1611, but all combinations of requery/ refresh do not fix my original problem. Any other assistance would be appreciated

 

[MajP]

make the sub separate so you can call it from multiple events
I would call this from the main forms current event and the combo’s after update

Private sub form_Current()
  Call changeSmokeCessa
End sub

Private sub combo49_afterUpdate()
  Call changeSmokeCessa
End sub

Private Sub changeSmokeCessa()
 'Msgbox  Forms!frmPtReg!Combo49
 Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = (Forms!frmPtReg!Combo49 = "Current")
End Sub

For debug purposes you may want to put a message box in the sub to ensure it is being called when it should. Once it works then you can comment it out

 

[capndave]

Thanks majP, I get the Msgbox on both occassions, but once the smokecessa checkbox disappears it is never seen again regardless of Combo49 value.

Any suggestions would be appreciated
CapnDave

 

[MajP]

My guess is that
Forms!frmPtReg!Combo49
never returns a value of "current"

That is why I suggested the message box. Does it return "Current"?

 

[capndave]

Duh me!! I used "current" when the stored value is "C" etc. "Current" is from the unbound column.

Thanks so much I learned a lot of good stuff. Have agreat day

 

[capndave]

Oops! There is still a bug- when the Combo49 box is empty I get a runtime 13 error Type Mismatch.

Any thoughts?

Thanks

 

[MajP]

Sorry I was a little lazy

Check to see if it is null using the Null to Zero function NZ.

Private Sub changeSmokeCessa()
Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = (NZ(Forms!frmPtReg!Combo49,"") = "Current")
End Sub

It errored out before because if the control is null
it would try to check (Null = "current") and thus a type mismatch.

 

[capndave]

MajP

All works 100%. I had given a thought about Nz, but had only used it sql and did not know it could/ or how in VBA.

Thanks again for your time!
CapnDave

 

[MajP]

or you can check if it is null using the isnull function

Private Sub changeSmokeCessa()
if isnull(frmPtReg!Combo49) then
Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible = false
else
Forms!frmPtReg!frmENC.Form.SmokeCessa.Visible =(Forms!frmPtReg!Combo49,"") = "Current")
end if
End Sub