Continuous Form - Object Background color based on Criteria 1

[SnayJ]

Here is what I'm working with
Form = MAINfrm (Single Form)
Subform on MAINfrm = MAINsub (Single Form)
Subform on MAINsub = INVsub (Continuous Form)
Fields on INVsub = ITEMIDbox, ITEMNAMEbox, ITEMSTATUSbox
I also have a Rectangle on the form called BGbox
Options for ITEMSTATUSbox = Active, Inactive, Sold, Archived

I want to change the color of the BGbox.backcolor based on the value in ITEMSTATUSbox.value

Here is my code:

     Forms!MAINfrm!MAINsub!INVsub.SourceObject = "INVsub"
     Forms!MAINfrm!MAINsub!INVsub.Requery
     Dim rstSubForm As Recordset
     Set rstSubForm = Forms!MAINfrm!MAINsub!INVsub.Form.Recordset
     rstSubForm.MoveFirst
     Do While Not rstSubForm.EOF
          Select Case Forms!MAINfrm!MAINsub!INVsub!ITEMSTATUSbox.Value
               Case "Inactive"
                    Forms!MAINfrm!MAINsub!INVsub!BGbox.BackColor = "16777215"
               Case "Active"
                    Forms!MAINfrm!MAINsub!INVsub!BGbox.BackColor = "13434879"
               Case "Sold"
                    Forms!MAINfrm!MAINsub!INVsub!BGbox.BackColor = "13434828"
               Case "Archived"
                    Forms!MAINfrm!MAINsub!INVsub!BGbox.BackColor = "16772300"
          End Select
          Debug.Print Forms!MAINfrm!MAINsub!INVsub!BGbox.BackColor
          rstSubForm.MoveNext
     Loop
     rstSubForm.MoveFirst

Results of this code seem to work except for the changing the backcolor part. The subform loads correctly and the recordsets are cycled thru, but no color change.... and my debug.print reflects the correct changes I would expect to see. But visually no change. I checked the BGbox rectangle and it is visible with a current backcolor set to Background 1 and back style set to Normal.

[highlight #8AE234][EDITED][/highlight] Oh and the code is on the STOPbox_Afterupdate() Event from the form: Forms!MAINfrm!MAINsub. Basically the user chooses a value from this dropdown... that value is a criteria for the Rowsource on the Continuous Form: Forms!MAINfrm!MAINsub!INVsub [highlight #8AE234][EDITED][/highlight]


I Searched for an easier way and couldn't find anything but a zip file with an example of how to do it written by Steven Lebans, but unfortunately when I tried to open it, it said it was written in a prior version of Access and I couldn't open it. I'm using Office 365. Every other post I found basically said it couldn't be done except on Current but only for one detail at a time, but not looping thru. I tried on Current for the subform and Access froze.

 

[MajP]

As for passing an argument to a blank form. I know you can pass it, what I was saying is how is passing the argument to the blank form, going to affect the blank forms subforms (subform).... what I was saying, is I don't see how it works

As the code shows you pass an argument to the main form. The main form can determine its subforms, and its sub subforms. Forms load from the inside out. So the subs load before the main. Since the main form knows the argument you passed in (ie an ItemID) it can tell the subsubform to load that ID.

I tried it, didn't get it to work. I opened the form acNormal, and put the code as you wrote it... didn't work. I changed some of the names to match my stuff... MAINfrm, ITEMID, CURRENTITEM and it still didn't work. I placed the code on the Open Event... and nothing would happen. I put it on the Current Event and the forms loaded, but didn't cycle to the correct record.

Can you post the real code and explain where the code is located (Calling Form, Main Form, other)? Did you do any error checking that could help explain what did not work (debug.prints, msgboxes, breakpoints)?

 

[SnayJ]

I didn't know the subs loaded first... thought the main did. I have some parts to the subs that require items on the main, might be the problem. I'll check and get back here with what you asked for... might be tomorrow. Thanks for your help.