VBA Error referring control field in Subform using Mainform

[ramez75]

Hi,

I believe I did this before awhile ago but for some reason I keep getting an error.

I have a Mainform (frmMain) that has a Subform (frmSub). On frmSub I have 2 comboxes (strCom1 & strCom2) one is set to invisible (strCom2.Visible = No). So using the "On Open Event" of frmMain I want make strCom2 visible if strCom1 = "Read Only". Ofcourse I will also need to place the vba on the On After Update event. Below is what I have so far but doesnt work. Please can someone tell me what am I doing wrong

I get Run-time Error 2427 "You enetered an experssion that has no value"

Private Sub Form_Open(Cancel As Integer)
 
If Me!frmSub.Form!strCom1.Value = "Read Only" Then
     Me!frmSub.Form!strCom2.Visible = True
Else
     Me!frmSub.Form!strCom2.Visible = False
End If
 
End Sub

Thanks

RB

 

[ramez75]

So I used the above code in the "On After Update event" of the Mainform and also used another vba on the "On After Update event" of strCom1.

That worked for me.

The issue I still have to get around presuming it is doable and of someone can guide me how is my frmSub is a continuous form. Hence, strCom1 could be "Read Only" for row 1 but nor row 2, 3 etc. Hence Row 1 strCom2 need to be visible whereas strCom2 for rows 2, 3, etc should be invisible.

Is this even doable

Thanks

 

[ramez75]

Thanks Pat

I figured out the error as you mentioned in your last para. Since I was using the On Open Event it was going to the first record which was empty and thus didnt work. When I moved it to On AfterUpdate Evenet the code worked well but since I cant control every row separately on a continuous form my effort was fruitless.

So I was looking into conditionla formatting. Is there away using conditional formatting to say if strCom1 = "Read Only" then fill strCom2 of that row within the continuous form with "N/A"

I tried IIF([strCom1]="Read Only", "N/A","") - I put this in the conditional formatting of strCom1 using Condtion 1 = Expression Is. But nothing happened

Is that doable?

There is only one set of properties for continuous forms so whatever the properties are set to applies to all visible instances. If this doesn't bother you, put your code in the Current event of the subform. That way the control will show or hide as you click into each subform record.

You could try playing with conditional formatting. Show/Hide isn't an option but you can play with color and that might do the trick. Conditional formatting will apply different values to each row so some might be red and some might be green.

Keep in mind that when you reference a conrol on a subform from an event on the parent form or a different form, you can ONLY reference the CURRENT record. So in your case, since you are doing it in the open event, you are referencing the first row in the subform.

 

[ramez75]

Pat,

Thanks, for some reason I miss read the first part of your response below. I see what you are saying now. I will try it

There is only one set of properties for continuous forms so whatever the properties are set to applies to all visible instances. If this doesn't bother you, put your code in the Current event of the subform. That way the control will show or hide as you click into each subform record.

You could try playing with conditional formatting. Show/Hide isn't an option but you can play with color and that might do the trick. Conditional formatting will apply different values to each row so some might be red and some might be green.

Keep in mind that when you reference a conrol on a subform from an event on the parent form or a different form, you can ONLY reference the CURRENT record. So in your case, since you are doing it in the open event, you are referencing the first row in the subform.