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]

You cannot change the color of an unbound control through code except possibly in the on paint event. However, that is not very reliable. What you are trying will change all rendering of the control. You should be able to use conditional formatting. Just make the rectangle a textbox.

 

[MajP]

Conditional Formatting using a single format condition.

 

[SnayJ]

Appreciate it, this did work but it presents another problem. When I'm clicking around on that continuous form now, when I click on the textbox that changes color, the text I'm viewing in the fields above it disappear. I've tried changing the enabled/locked values for the colored text box, doesn't fix it.

Plus there's another problem I have in using a subform to begin with vs a listbox. (I know you can't colorize a listbox - very easily, that's why I went with a subform)... but I can't seem to scroll the subform very well. I have to use the actual scroll bar arrows or click in the scroll bar empty parts to move the list up/down. I can't simply hover and scroll the records and I can't use my keyboard arrow keys either.

Thoughts?

 

[MajP]

In your additional controls "activeX controls" there is something called a listview. It is a listbox on steriods. The problem is there is little documented and you have to code a lot of functionality.

Private Sub Form_Load()
  Dim lstItem As ListItem
  Dim lvw As ListView
  Dim rs As DAO.Recordset
  
  Set rs = CurrentDb.OpenRecordset("Suppliers", dbReadOnly)
  Set lvw = Me.lvwOne.Object
  With lvw
     'Set ListView style
     .View = lvwReport
     .GridLines = True
     .FullRowSelect = True
     .Appearance = cc3D
     .Checkboxes = True
     .TextBackground = lvwOpaque
     
     'Clear Header and ListItems
     .ListItems.Clear
     .ColumnHeaders.Clear
   End With
   'Set up column headers
   With lvw.ColumnHeaders
      .Add , , "Company Name" & vbCrLf & "Test", 2000, lvwColumnLeft
      .Add , , "Contact Name", 200, lvwColumnLeft
      .Add , , "Contact Title", 2000, lvwColumnLeft
   End With
   Do While Not rs.EOF
     Set lstItem = lvw.ListItems.Add()
     lstItem.Text = rs!CompanyName
     lstItem.SubItems(1) = rs!contactName
     lstItem.SubItems(2) = rs!contactTitle
     If rs.AbsolutePosition Mod 2 = 0 Then
       lstItem.Bold = True
     Else
       lstItem.ForeColor = RGB(10 * rs.AbsolutePosition, 0, 100 * rs.AbsolutePosition)
     End If
     rs.MoveNext
    Loop
End Sub

Private Sub lvwOne_ItemClick(ByVal Item As Object)
 Dim lvw As ListView
 Dim lstItm As ListItem
 Set lvw = Me.lvwOne.Object
 For Each lstItm In lvw.ListItems
   lstItm.Selected = False
 Next lstItm
End Sub

I do not think Access forms have a hover and scroll capability, but I will see if I have code. Check your cycle property of the form "All Records". Arrows should work. Does you mouse wheel work?

 

[MajP]

I think you will be successful for what you want to do using the onpaint event. This is a rectangle behind the controls. It seems to avoid the issue you were describing

Private Sub Detail_Paint()
  If Me.UnitPrice < 10 Then
    Me.box1.BackColor = vbGreen
  ElseIf Me.UnitPrice >= 10 And Me.UnitPrice < 25 Then
    Me.box1.BackColor = vbYellow
  Else
    Me.box1.BackColor = vbRed
  End If
End Sub

 

[SnayJ]

MajP,

What I meant is the Mouse Wheel doesn't work (Hover and Scroll). I did fix the whole disappearing text issue. I simply setfocus to the visible textbox when I click on the colored textbox. It highlights the text, but that's fine.

I checked the subform cycle and it's set to All Records.

As for the Advanced listbox control, would I still be able to color the recordset (conditional formatting)?

 

[MajP]

Not sure if this is your issue, but look here.

http://allenbrowne.com/ser-70.html

The mouse wheel now only controls the scroll bar and does not sroll the records. However, if the mouse wheel does not move the scroll bar you have a different issue.

The listview does not have conditional formatting. It has all kinds of features that can be applied at the row level, but it is not the easiest thing to code and work with. Conditional formatting is the easiest, but the on paint of the detail section should also work in this case.

 

[SnayJ]

MajP,

Appreciate the link to that... not exactly sure if it will help... but I'm going to try it.

I do have another question that might be easy for you... I want to filter the subform by clicking a label on the parent form. How can I affect the subforms rowsource. I tried:
The label is on the forms!MAINfrm!MAINsub (form) and the rowsource I want to change is for forms!MAINfrm!MAINsub!INVsub

 

[MajP]

dim strFilter as string
dim sfrm as access.form
set sfrm = Me.MAINsub.Form.INVsub.form
strFilter = "StatusField = 'Inactive'"
sfrm.filter = strFilter
sfrm.filteron = true

When you reference a control on a subform your syntaxs is
Me.nameofsuformcontorl.form.nameOfControlOnSubform
Me.MAINsub.Form.INVsub.form

The reason you need "form" after the name of the subformcontrol is so you return the form that is in the subformcontrol
Me.MAINsub.INVsub would not be correct because MainSub is the name of the subformcontrol and it does not have a property InvSub. The form inside the the subcontrol has the next subformcontrol and it has a form that you are trying to change.

 

[SnayJ]

MajP,

Great explanation, thanks a bunch. I really wish I could have more formal training in all this. I took a VB class in 1998 and then from there have learned by Tek-Tips and trial/error. I make pretty decent simple databases. My more complicated ones give me a headache... but I enjoy the challenge. I really wish I could do this in VB and make an Application instead of a MS Access DB, but I don't have the time to learn the differences between VB and VBA especially since I don't even know all the syntax in VBA. Thanks again for all the help.


Jeff

 

[MajP]

You can do some pretty advanced stuff in VB.NET using ADO.NET connecting to Access as the backend. But that is a steep learning curve. However, ADO.Net is pretty amazing in the things you can do.

If you want to get good at Access, spend 4 dollars on the Access Desktop Developers Handbook. There may be newer version but you read this and you will be a power programmer.

https://www.betterworldbooks.com/ac...utm_medium=onr_paidshopping&utm_source=google

Spend another 4 dollars on this

https://www.thriftbooks.com/w/micro...LCsaAmjdEALw_wcB#isbn=0735626928&idiq=5712696

These may be old but VBA has changed so little and Access has changed some but the core stuff remains the same. I rather spend 4 dollars with that in mind then 80 on the most current versions.

 

[SnayJ]

MajP,

Appreciate that much!

 

[SnayJ]

MajP,

I know how I would handle this if I was using a listbox, but if you remember I went with the continuous subform so I could conditional format the records based on criteria. Well, now I want to be able to (from a different from), bring up this form and cycle thru the records till I'm at the correct one... (selecting it). Kinda hard to explain... look at the picture and code and please see if you can make heads or tails. Thanks in advance, I can put up a video of me using the program if that would help.

    Dim x As SubForm
    Set x = Forms!MAINfrm!MAINsub!INVsub
    Dim y, z As Long
    y = 1
    z = DCount("[ITEMID]", "INVENTORYtbl", "[STOPID] = " & CURRENTSTOP)         'z = the number of records in the subform INVsub
    With x
        DoCmd.GoToRecord Record:=acFirst
        Do Until y = z
            If !ITEMIDbox.Value = CURRENTITEM Then
                GoTo getout
            Else
                DoCmd.GoToRecord Record:=acNext
            End If
        y = y + 1
        Loop
    End With
getout:
    Call LOOKUP_ITEM
    Call LOOKUP_EBAY

 

[MajP]

I do not really understand your code because it appears there are some public variables that I do not know where they are defined. Also I do not know where this code is being called. Depending on how you open this form you may want to pass the CurrentItem in the forms OpenArgs.

Well, now I want to be able to (from a different from), bring up this form and cycle thru the records till I'm at the correct one

I will assume you are opening MAINfrm from some other form.

In the MainFrm on load event I would have this code

dim invFrm as access.form
set invFrm = Me.MAINsub.form.INVsub.form 'do not forget the word form after the control name
If not trim(me.openArgs & " ") = "" then 'ensure an open args is passed in
  invFrm.recordset.findfirst "ItemID = " & clng(openArgs)
  'invFrm.recordset.findfirst "ItemID = '" openArgs & "'" if itemID is text
end if

That assumes openArgs is numeric. I am pretty sure open args are passed in as strings that is why I added the clng.

 

[SnayJ]

I can't use the On_Open Event on the main or subform because when that form usually would open I wouldn't want it to do anything. Unless I was to declare a Global Variable that I could set on the other form and check for that Global Variable on the On_Open Event. And run it that way... I just thought it would be easier to do some Next Record type thing to advance thru the continuous form till I arrived at the right record based on criteria. I'm going to try docmd.runmacro next... then I might try the Global Variable with your On_Open Event.... thanks for the thought.

If I can't get it to work, I'll make a vid and post it on Youtube... might just be easier for you to see it - to advise how to get it to work. Thanks again.

 

[MajP]

Stop! No global variables, no next record thing. Not video. This is a trivial task.
Read the code. Trust me, I know what I am doing.
If you do not pass in any open args from a calling form nothing happens.

If not trim(me.openArgs & " ") = "" then 'ensure an open args is passed in

So if nothing is passed in nothing happens. If you just open it nothing is passed in but if you call it from another form like

docmd.openform "frmMain",,,,,Me.ItemID

I believe that is the correct amount of commas for the openArgs argument. You will pass in an ItemID and it will move the subform to that itemID.

 

[SnayJ]

Ok... bare with me (I'm not trying to be argumentative), I learned coding thru trial/error and forums....no classes so I'm very poorly self-taught.

But I think the way you are telling me to do it is assuming I'm using Master/Child linking for the Main/Subforms... and I'm not. I've never done it that way because when I was learning to make databases, I kept getting record locking errors and when researching work arounds I was told to create unbound forms and load the data systematically thru dlookup (I was told to do that on this forum - years ago, and it has always worked). Which is how I do all my forms now. A LOT more coding (mostly cut/paste - changing fieldnames), but no errors... and it works for me. Also the MAINfrm has NO data on it whatsoever... so I can't pass arguments to a form with no dataobjects on it. The data is on a continuous subform which is on a single form subform which is on the mainform. And again, I'm not linking mains/subs with master/child references. What I'm trying to do is:

I have a form that I use to search records out based on ITEMNAME, ITEMID or STORAGELOCATION. The user chooses their search option and enters their search words/number/location into a textbox which creates the recordsource for a listbox. When the user clicks the listbox record they want, it opens the MAINfrm (which initially has a blank subform (MAINsub), sets the MAINfrm!MAINsub.Sourceobject to a form called "MIfrm" (My Inventory Form). Then on the MIfrm is a subform called INVsub, which is a continuous form. The records in that form are the itemrecords that correspond to the listbox from the searchform. So opening up the MAINfrm with open arguments won't do anything. What I was trying to do was open the MAINfrm, set the subform (MAINsub) which has the continuous subform on it (INVsub), then select the continuous subform (INVsub) and cycle thru the recordsets till the field [ITEMID] = CURRENTIEM (which is a Global Variable set when the user clicks the listbox from the searchform). Everything works as far as setting up the forms... but I can't get it to cycle thru (selecting recordsets) on the continuous form till it reaches the desired criteria ([ITEMID] = CURRENTITEM). Which is why I was trying to figure out how to cycle thru the continuous form manually, doing NextRecord type stuff.

I know this is not how YOU would do it... but to do it the way you would do it... I would need you to do it, because I don't know how to do get around record-locking errors. (except to use temptables...and I hate doing that - this way is actually easier for me). Is it possible to manually cycle thru a continuous subform on Form A, from Form B (a totally unrelated form)

I wouldn't be doing this had I not changed the INVsub from a listbox to a subform. I did so (at the beginning of this thread) so I could do conditional formatting (not possible in a listbox). If I were to revert back to a listbox... I can accomplish everything I want to accomplish except to have those records in the listbox to use conditional formatting. I hope I didn't confuse you - that's why I suggested a video because it's a lot of forms/subforms etc... and I obviously haven't built this the way you or any other programmer would.

 

[MajP]

I am not a fan of globals and this could definitely be accomplished as previously suggested using openargs. Some of your statements are incorrect:

so I can't pass arguments to a form with no dataobjects on it

There is no reason you cannot pass an argument to a completely blank form.


Not sure where you want to call the code from. You could call it from an external form as long as you do not open it acdialog.

dim invFrm as access.form
set invFrm = forms("MainFrm").MAINsub.form.INVsub.form 'do not forget the word form after the control name
invFrm.recordset.findfirst "ItemID = " & CurrentID

The above code will select the itemID in subform invSub that meets the global variable. If not found it does not move.

 

[SnayJ]

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.

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.