Conditional formatting in another form (1 Viewer)

[Faoineag]

[FONT=&quot]Hello, I need your help to solve a problem concerning conditional formatting: I have a form "MascheraUno" with a subform "MiaSottoMaschera" (containing a field called "PrezzoA"), a button that opens a "MascheraDue" form ( which contains a field called "PriceB") and an unassociated text box "txtPippo", which I would like to change color depending on whether "PriceB" is greater or less than "PriceB". On "txtPippo" I applied conditional formatting with these two expressions:[/FONT]
[FONT=&quot]1) [MiaSottoMaschera]! [PrezzoA]> Forms! [MascheraDue]! [PrezzoB][/FONT]
[FONT=&quot]The txtPippo box turns red[/FONT]
[FONT=&quot]2) [MiaSottoMaschera]! [PrezzoA]> Forms! [MascheraDue]! [PrezzoB][/FONT]
[FONT=&quot]3) The txtPippo box turns green[/FONT]
[FONT=&quot]I then added on the After update event of the main form (where the subform is located and the button that opens the “MascheraDue”) this code:[/FONT]
[FONT=&quot][Forms]! [MascheraUno] .refresh[/FONT]

[FONT=&quot]Unfortunately, however, it only works once in a while. It's normal? Can I solve with Vba code?[/FONT]
[FONT=&quot]Thanks to those who want to help me[/FONT]

 

[arnelgp]

add an Unbound textbox (txtPrice) to MascheraDue form.
make it visible property to No.
add code to MascheraDue Load Event:

Private Sub Form_Load()
Me.txtPrice = Forms!MascheraUno!MiaSottoMaschera.Form![PrezzoA]
End Sub

remove the conditional formats on txtPippo.
create new conditional format to txtPippo comparing txtPrice and PriceB.
close and save the form.

next, on subform MiaSottoMaschera, add code to its Current Event:

Private Sub Form_Current()
On Error Resume Next
If IsFormLoaded("MascheraDue") Then
	Forms!MascheraDue!txtPrice = Nz(Me.PrezzoA, 0)
End If
End Sub

lastly, copy and paste to following code to a standard module.

Public Function isFormLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject
    On Error Resume Next
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    isFormLoaded = False
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then isFormLoaded = True
    End If
    Set oAccessObject = Nothing
End Function

 

[Faoineag]

add an Unbound textbox (txtPrice) to MascheraDue form.
make it visible property to No.
add code to MascheraDue Load Event:

Private Sub Form_Load()
Me.txtPrice = Forms!MascheraUno!MiaSottoMaschera.Form![PrezzoA]
End Sub

remove the conditional formats on txtPippo.
create new conditional format to txtPippo comparing txtPrice and PriceB.
close and save the form.

next, on subform MiaSottoMaschera, add code to its Current Event:

Private Sub Form_Current()
On Error Resume Next
If IsFormLoaded("MascheraDue") Then
    Forms!MascheraDue!txtPrice = Nz(Me.PrezzoA, 0)
End If
End Sub

lastly, copy and paste to following code to a standard module.

Public Function isFormLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject
    On Error Resume Next
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    isFormLoaded = False
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then isFormLoaded = True
    End If
    Set oAccessObject = Nothing
End Function

first of all thanks for the quick reply. I followed the instructions, but inserting the code on the Current event of the subform (If IsFormLoaded ("MascheraDue") I get the compile error: sub or function not defined

 

[arnelgp]

You need ti copy and paste the IsFormLoaded function in Standard module in vba.

 

[Faoineag]

Ah, I had stopped at the error, now the message no longer appears. However, the txtPippo box does not change color

 

[arnelgp]

Edit your mascheradue firm and review your condition format. Are the fields enclosed in quote (")? If so replace it square bracket [ ]. Also add Recalc to the form:

Private Sub Form_Current()
On Error Resume Next
If IsFormLoaded("MascheraDue") Then
	Forms!MascheraDue8!txtPrice = Nz(Me.PrezzoA, 0)
        Forms!MascheraDue.Recalc
End If
End Sub

 

[Faoineag]

ok, now it works, but the formatting is also applied to empty records of the "MascheraDue ". And then it works only if I also open the "MascheraDue" in addition to the "MascheraUno"

 

[arnelgp]

It will apply to empty record also because we are comparibg PriceB on your form. The form is being called from a button on main form right? If the main form us not open where will you compare it?

 

[Faoineag]

True! Thanks for everything

 

[Faoineag]

To be precise, when I open the "MascheraUno" formatting does not work, then I go to the second record and go back to the first one and the cell is colored

 

[arnelgp]

Upload a sample copy and ill take a look

 

[Faoineag]

I have minimized the database, how can I attach it?

 

[arnelgp]

Zip it. Then use the Post Reply button on this forum. Manage attachment.

 

[Faoineag]

thank you very much

 

[arnelgp]

Here test it

 

[Faoineag]

Thank you again for your availability; unfortunately, however, I still have problems with the first record: in addition to being white in the text box (turns green when I go to the next record and go back to the first), the subform ListiniPrezziEAttivi is empty at the first record. From the second record on then everything works fine

 

[Faoineag]

But I'm very happy that the box, from the second record on, is colored without having to open the MascheraDue!

 

[Faoineag]

Thank you again for your availability; unfortunately, however, I still have problems with the first record: in addition to being white in the text box (turns green when I go to the next record and go back to the first), the subform ListiniPrezziEAttivi is empty at the first record. From the second record on then everything works fine

Can you find a solution to this problem? otherwise the first record becomes unusable. Thanks again

 

[arnelgp]

here my fried give a try (don't give up on us ...)

 

[Faoineag]

Fantastic! It's perfect. Thank you and sorry if I was insistent