Changing button format depending on related records

[ibbledibble]

Hello

I have a DB with a Customer table that has a 1-to-many relationship with other tables. Each of these sub-tables are accessed through control buttons on the form of the customer table.

What I want to do is change the colour of the control button when there is data in the related record.

Is this possible?

Thanks!

 

[vbaInet]

Conditional Formatting:

Expression Is DCount(...) > 0

Conditional Formatting:
http://office.microsoft.com/en-us/a...using-conditional-formatting-HA010208133.aspx

http://www.databasedev.co.uk/conditional_report_formatting.html

DCount() function:
http://www.techonthenet.com/access/functions/domain/dcount.php

 

[ibbledibble]

Thanks but I explained it incorrectly - I meant a command button, not a control button.

Conditional Formatting:

Expression Is DCount(...) > 0

Conditional Formatting:
http://office.microsoft.com/en-us/a...using-conditional-formatting-HA010208133.aspx

http://www.databasedev.co.uk/conditional_report_formatting.html

DCount() function:
http://www.techonthenet.com/access/functions/domain/dcount.php

 

[vbaInet]

Oh I get you. Is this for a continuous form?

 

[ibbledibble]

Nope, it's a single form

 

[vbaInet]

Right, that should be ok then.

Put something like this in the On Current event of the Form:

If DCount(...) > 0 Then
     Me.NameOfButton.ForeColor = vbRed
Else
     Me.NameOfButton.ForeColor = vbBlack
End If

An idea too will be to add a count to the caption of the button in brackets so a typical caption would be:

Open Record (32)

 

[gemma-the-husky]

note that you can change the text colour of a button, but not the background.

you can change the text as well, if you wish.

 

[ibbledibble]

Thank you very much for the suggestions...

One more question - would this work for counting data in a related record? I don't want to check there is data in the whole of the related table, just in the specific record that relates to the customer page I am viewing.

The data is linked by the primary key of the customer table.

Thanks (hope I'm being clear).

 

[ibbledibble]

Solved it!

Private Sub Form_Current()
If DCount("*", "qryYourQuery") > 0 Then
Command42.ForeColor = RGB(255, 125, 0)
Else
Command42.ForeColor = -2147483630 'Standard fore color
End If
End sub

 

[vbaInet]

Glad to know you got that working!

 

[smig]

look at Stephan Leban's tools - http://www.lebans.com/ for changing the command button color.
never tested it myself