Shade a Form Row when a field is checked

[noboffinme]

Hi

I've looked at many examples of how to do this but can't find one to do exactly what I want.

I have an Access 2003 form (frm1) in datasheet format with several fields, one being 'Member' which is a checkbox.

I want the entire row to be shaded a colour when the Member checkbox is ticked.

Here's the code;

Private Sub Member_Click()

'The Member field is a checkbox
Dim Member As Boolean
Dim lngYellow As Long

'Declare colour
lngYellow = RGB(255, 255, 0)

'The member field starts out as false until checked

'If the checkbox is ticked, colour the entire row yellow
If Member = True Then

CurrentRecord.BackColor = lngYellow

End If

End Sub

Any suggests?

 

[pbaldy]

Have you looked at Format/Conditional Formatting? It's the simplest way to do what you describe.

 

[noboffinme]

Hi Pbaldy

Yes, but that option is greyed out in normal & design view, that's why I thought vba would be the answer.

Can you assist with my code at all?

 

[pbaldy]

VBA doesn't work well for this type of thing on a form (it works well on a report). Make sure you highlight a textbox before selecting the menu option.

 

[noboffinme]

Thanks

I did get the formatting to work but not using a Checkbox field.

I need it to work on the basis of if the Checkbox is ticked, the row is shaded in a background colour.

 

[pbaldy]

You'd use Expression Is rather than Value, like:

[Member] = True

 

[noboffinme]

Thanks

Now I'm getting an 'Invalid Qualifier' error for 'CurrentRow'.

Changed it to [Member] as well but got the same error.

The code recognises that Member is now true but I need to know what will cause the row to change its background colour when this is true.

Can anyone help with the syntax to select the current row & apply a backcolour??

 

[pbaldy]

If you're still trying to use code, it's not going to work. The syntax would be

Me.TextboxName.BackColor

You'll see then why this won't work.

 

[noboffinme]

Thanks

I got it to work to shade just a text field with the below code but not from the Checkbox & also not on a Datasheet form.

So I could add the conditions that if 'PROD' is populated, colour all the fields a certain colour.

It will work on the field background for a Single or Continuous form;

--------------------------------
Private Sub Prod_AfterUpdate()

If [PROD] <> "" Then

PROD.BackColor = 123222

End If

End Sub

--------------------------------

Can you or anyone advise why this won't work on a Datasheet form or advise how to if it's possible?

 

[pbaldy]

You don't seem to believe me that it won't work. A form doesn't have a detail format event like a report does. Why don't you just use Conditional Formatting?

 

[noboffinme]

Hi pbaldy

I do believe you but when I tried to use Conditional Formatting, the option was greyed out unless I used a text field but I need to use a Checkbox.

I tried getting cond formatting to be available from the form, the field, the row - all were greyed out as an option so I wasn't able to use them...

 

[noboffinme]

Hi Again pbaldy

Is it possible for you create a database with one table, one form created from the table & apply shading using Conditional Formatting?

I'm clearly doing something different to you & would like to use it if you can show me how. Thanks

 

[pbaldy]

You want to format a textbox based on a checkbox. That means you select the textbox, use Expression Is and an expression like I posted above.

 

[Dairy Farmer]

Try using

If (Nz(Me.PROD, 0) <> 0) then

instead of

If [PROD] <> "" Then

Also, why are you using a datasheet? A Tabular Form is much more customisable.

 

[noboffinme]

Hi pbaldy

An example would be best to show me what you mean as I still don't fully understand.

Thanks Dairy Farmer, for the syntax of the empty field.

Appreciate both responses. Thanks

 

[pbaldy]

It would make the most sense for you to post your db. Otherwise I may make a sample that doesn't fit your scenario and you still won't understand.

 

[noboffinme]

Hi pbaldy

I've attached an example database I am using to test this code.

The 'slgfrm_example' is a Single form that shades the 'Prod' field green if it is populated - using vba code.

The 'Datasheet' form is the one to apply Conditional Formatting to, if you can show me what you mean I'd appreciate it, Cheers

 

[pbaldy]

Here you go

 

[noboffinme]

Thanks pbaldy

This is great, but what steps did you take to get it to work?

Going to design view & focusing on the Member checkbox, the whole form , the label, you name it, won't allow me to select Conditional Formatting??

How did you get it to be available to use here? Cheers

 

[pbaldy]

As I suggested, I applied Conditional Formatting to the prod textbox. Select that textbox then CF.