Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

capndave

Technical User
Mar 4, 2003
36
US
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
 
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?
Code:
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.
 
Thanks kjv1611, but all combinations of requery/ refresh do not fix my original problem. Any other assistance would be appreciated
 
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
Code:
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
 
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
 
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"?
 
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
 
Oops! There is still a bug- when the Combo49 box is empty I get a runtime 13 error Type Mismatch.

Any thoughts?

Thanks
 
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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top