VBA search whole query for results?

[Cozzy]

Afternoon people,
I have a "computer Locator" database I want to create where it will show customers where available compters are located, now I have tried conditional formatting and don't really want to type up hundreds/thousands of vba programming to say IF testbox = computer name (example),
I have found something that would be so much easier but need it to look through the whole query and highlight different colours. I have text boxes for the machines and have tagged them with there machine names
for example:

Private sub form_Current()
Dim ctrl As Control

For Each ctrl In Me.Section(acHeader).Controls
If ctrl.Tag = [alog_host] Then
ctrl.BackColor = vbRed
Else
ctrl.BackColor = vbGreen
End If
Next
End Sub

now at the moment the form has the query running as a continuous form and if I click on individual machine names the colour changes but I want all the controls to change depending on the query.
hope this makes sense.

 

[vbaInet]

For continuous forms you need Conditional Formatting.

 

[Cozzy]

thing is I don't need it to be a continuous form all I need on the form is the floor plan with 30-40 textboxes as machines looking up a query, I should point out that the query only shows users logged onto a particular floor.

 

[vbaInet]

If it's going to be a single form then your code will suffice. What's happening?

 

[Cozzy]

no as it is only looking at 1 record so 1 machine changes colour, I want it to look up the whole query so multiple records change colour? thanks

 

[vbaInet]

How do you expect the colour of multiple records on a Single form to be affected? Why would the colour change for a record that isn't visible and isn't the current record. Like I mentioned, Conditional Formatting is for Continuous Form and Datasheet and your code is for Single Form (or CF will work too).

 

[Cozzy]

ok I'll explain more, I have a query with current log ons, lets say 35 people are logged on and 70 machines available in that area, the query lets me know there user name and machine they are on, if I was to hit the "find me a computer" button all the machines that are in the query turn red and the rest go green, or I can have a live map that refreshes every 2 mins and shows you where the nearest available computers are.

 

[vbaInet]

You've just explained a scenario but didn't clarify how this relates to what you're trying to do.

 

[Cozzy]

sorry thought my first message explained, I need unbound text boxes to change colour depending if someone is logged onto then, I.e red if they are logged on and green if it's available to to use. if I do conditional formatting you have to have focus on the record before it changes colour, but I want all all unbound text boxes to change colour if the criteria matches. I think it's too complicated

 

[vbaInet]

Ok the problem you're having is you are using the Field Name in the Conditional Formatting expression. Prefix the control name with "txt", i.e. the name you are checking the condition against, and use the control name in the Conditional Formatting expression.