[RESOLVED] Access - Changing control property on one record but not all records

Hello, I have a lot of experience with VBA in Excel, but next to no experience with an other Office Apps. I have been creating an Access project and I am still trying to figure everything out.

My problem is this: I am viewing records from a table in a continuous form view so that I don't have use the Record Navi controls to cycle through my records (I can just scroll down). I have a control on the form that is called [Status]. I want to be able to change the [Status].BackColor property of the [Status] Control, but i want it to change based on the value of the control FOR EACH INDIVIDUAL record.

I know this sounds confusing, so i'm going to try to reiterate what i just wrote. I have a control on a form that is bound to a table and displays the value of [Status] in the table. The control is a combobox that can be changed on the form. Depending on the value of the combobox, i want the backcolor to change, but i only want it to change for that record. What happens now is that if i change the value of the combobox, the BackColor will change on ALL of the records. Is there any way to change it on just the record for which the value changed?

Thanks in advance for all your help!!!

Offhand, I'd say there'd have to be some way to link the selected value in the combobox to background color desired. How about a sub to check the selected value. Like:

Code:

---

If cbo.selected > 5 and cbo.selected <15 then
  cbo.backcolor = vbBlue
End If

---

You could also use a query to do something similar.

Thanks for the reply, salvelinus. I already have a check for the value of my combobox... its something like this:

VB Code:

Private Sub Check_BackColor()
If Issues!Status.Value = "Open" Then
  Issues!Status.BackColor = vbWhite
ElseIf Issues!Status.Value = "Pending" Then
  Issues!Status.BackColor = vbYellow
ElseIf Issues!Status.Value = "Closed" Then
  Issues!Status.BackColor = vbGreen
End If
End Sub

This is basically what you have, which changes the BackColor property based on the value of the Status ComboBox. However, when I do this, it changes the color for ALL of the comboboxes seen in contiuous form view. Is there a way to change the backcolor property of the combobox for each individual record?

Does anyone know of a way in which I can show each record but change the properties of each control for that one record? Is there some way I can use a query to get one record at a time and display all of them in a subform? I'm still fairly new to Access so I am not as quick to come up with other solutions as a more experienced Access user would be.

Any help at all would be greatly appreciated, thanks!!

I couldn't get it to work for a continuous view (not saying it can't be done, just that I didn't get it), but did get it for Single record view. Code like this in the Form_Current event:

VB Code:

Private Sub Form_Current()
       If [txtFoo] = "B" Then
            [txtFoo].BackColor = vbBlue
      Else
            [txtFoo].BackColor = vbWhite
      End If
End Sub

You'd have to alter it some for a combobox, but that's the idea. If you have to have a continuous form, I don't know without more research.

Thanks again for the post, I really appreciate you trying to help me. Using single form is an option, but I really need continuous view. I'll keep trying and let you know if i figure something out.

So I tried a version of your code, Salvelinus and it worked except that as soon as i clicked on a new record, the control's backcolor went back to the color it was before. I'm pretty sure that is because it was in the Form_Current event. Is there a way to save the properties of the control after they are changed in the Form_Current event procedure?

Ok, may have found what you want. Check here for conditional formatting.

Perfect!! That was EXACTLY what I was looking for. Man, there is a wealth of info in that sample program. Thanks again for all your help, it has been much appreciated!

I have a textbox that I use as the background for each of my records. Does anyone know how to completely disable the textbox control so that it can't be clicked and therefore in focus? I have the properties of the textbox set to .enabled = false and .locked = true, but I can still click on the textbox which then covers up all the other controls in the record. Anyone?

Not sure what you mean by a textbox being the background, but try setting it to .Visible = False, or shrinking the size.

The link you added a few posts ago that explains how to set an the color of an individual record using conditional formatting. The link was an example that uses a text box that is the size of the details section of the form to change the "background" color of the record. After placing the textbox in the details section, you can then set conditional formatting properties to change the textbox.backcolor property, hence changing the background color of the record. I have done all this, however the textbox itself is still clickable. Since I want the color to remain, I want .visible = true and .height <> 0.

I have tried this:

VB Code:

Private Sub Text_Surround_Enter(Cancel As Boolean)
DoCmd.CancelEvent
End Sub

I figured it out!! Duhhh this is how you should do the cancel event (im retarded):

VB Code:

Private Sub Text_Surround_Enter(Cancel As Boolean)
    Cancel = True
    'DoCmd.CancelEvent
End Sub