turn unbound box green if query is not empty

[tagteam]

I have a report I need to run that is for US miles. If there were miles driven in Canada then I need to run a different report. I am trying to find a way to have a notification of miles in Canada.


So I have a dropdown box to select the customer. If that customer has Canadian mileage i would like to have a unbound box or label turn green to notify the user to run the Canadian mileage report.

I have created a query that looks at the customer records and will show all the records in Canada. I was thinking I could do an if statement that would basically say if the query is not empty then show the box as green

here is my rough effort

if (dcount, "q_checkformileage" > 0 Then
Me.messagebox.backcolor = green
else
Me.Messagebox.backcolor = red
end if

I know that doesn't really work at all but it is just my rough idea.

[pbaldy]

Try

DCount("*", "q_checkformileage")

[tagteam]

So on the after update event of my dropdown box I have

If DCount("*", "q_CheckForCanadianMileage") > 0 Then
Me.Canadian.BackColor = vbRed
Else
Me.Canadian.BackColor = vbWhite
End If

I don't get an error, however the label background also does not change color

[pbaldy]

And the query returns records only when there are Canadian records? Can you attach the db here?

[orange]

What exactly is the name of the textbox that you want to change its background color to signify Canadian mileage involved?

[tagteam]

I was able to get it to work.

Two issues I had
1. I had to set the background color of the label box to something. It was set to transparent
2. I had to move the code from the after update event to the on change event.

Thanks for the help

[pbaldy]

Glad you got it sorted out.