VBA to conditionally format subform

[Scottish_Anatomist]

Hey guys and gals, I've got a problem in designing a form, but its more the VBA that's giving me the headache.



So, to simplify my database, lets say we have 3 tables with the following fields:

tbl_Ours: OursID(PK); OursDescription
tbl_Theirs: TheirsID(PK); TheirsDescription
tbl_No_Mans_Land: OursID(CK); TheirsID(CK); NoMatch(Boolean)

I've got a form used to match 'our' produicts with 'their' ones.

frm_Matching - which contains:

subfrm_Matching - a subform listing all the products in tbl_Ours as a continuous form (recordset type as snapshot to prevent data being changed in this form)
MatchingList - a list box listing all the products in tbl_Theirs
bttnMatch - a command button running code to enter the two selected products into the appropriate tbl_No_Mans_Land fields.

The following code is what appends them:

Private Sub bttnMatch_Click()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim OurProduct As Control
  Dim TheirProduct As Control
  
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("No_Mans_Land", dbOpenDynaset, dbAppendOnly)

  'add selected products to table
  Set OurProduct = Me.subfrm_Matching
  Set TheirProduct = Me.MatchingList
  
  rs.AddNew
  rs!OurID = OurProduct.Form!ID.Value
  rs!TheirID = TheirProduct.Column(0)
  rs.Update
 
End Sub

The problem I'm trying to solve is that I want to format the records appearing in the subform. If the record has been matched with another (i.e.that records PK value appears in the matching table (tbl_No_Mans_Land)) or has the check box selected for NoMatch, the background should be green. If it hasn't been matched yet the record background will be red - just to make it easier to see whats be done and what remains.

unfortunately I'm completely stuck at this point. I was thinking of finding a way to iterate through the recordset and conditionally format the record, however I'm still fairly new to VBA and would be very grateful of any help/hints you can offer.

[Lukael]

I would suggest you to check this. Probably not same case as yours, but you'll figure out what you need. There are some other simmilar sites you can visit, just google it. Here It is:

http://www.utteraccess.com/wiki/inde...ht_Current_Row

[Micron]

I believe your only option is to place a textbox BEHIND the row of controls, make it wide/high enough to surround those controls, set the border to none and conditionally format the background colour based on the value of one of the record controls. AFAIK, a form does not have the capability to conditionally colour a row as there really is no such thing. The area on which you place the controls is not a row per se, it is part of the form body or background. The alternate row colour feature is not the same as what you ask for, so that would be of no help. To be honest, my Access version is somewhat behind, so I'm not sure which objects that feature has been added to anyway.

My suggestion probably will not work if your form is datasheet view. You did not say if it was a continuous form or not.

[Missinglinq]

...AFAIK, a form does not have the capability to conditionally colour a row as there really is no such thing...

That's true, but you can make it look that way, for a Datasheet View Form (or a Continuous View Form tweaked to look like a Datasheet View Form), by changing the BackColor of all Textboxes on the Current Record. Here's a quick 'how to,' using a method that I think is similar to the one in the link Lukael posted:

You have to use Conditional Formatting for this, and you have to let Access know which Row is the Current one. Assuming that you have a unique Field/Primary Key in your Record (we'll call it RecID in this example) you can do that by

1. Adding an Unbound Control to you Form...name it txtHilite
2. Set its Visible Property to No
3. In Design View, while holding down the <Shift> Key, click on each Control, in turn, to be formatted
4. Click on Conditional/Conditional Formatting from the Ribbon (or Menu, if your Access is pre-2007) and under Condition1, select Expression Is from the dropdown
5. In the next Textbox, enter [txthilite]=[RecID]
6. Select the BackColor you want, using the 'Paint Bucket' icon
7. Click on OK

Now, in the code module, enter this:

Private Sub Form_Current()
 Me.txtHiLite = Me.RecID
End Sub

Linq ;0)>

[Micron]

Thanks for posting a neat trick. Maybe I'll use it someday if the situation calls for it. Correct me if I'm wrong, but the 'condition' you have coded for is for any record that is the current record. This trick won't help the poster because they want to highlight a record, or maybe several, in a subform if there is a match between two fields OR if one other field is True/Yes. If so, do you see an easy way to cover those two requirements by putting a hidden text box on a subform? Maybe it's easy enough to capture the value of a yes/no field, but I suspect the matching record is in some other table (tbl_No_Mans_Land). Not sure because I don't completely grasp the relationship between that table and the form/subform.

[Lukael]

That is exactly same thing I allready posted. But seems like there is no feedback from Scottish_Anatomist to confirm If It solved his problem. Or to close his thread.