Changing box color in vba

[Cozzy]

Afternoon, I am trying to do a mini project where I have a map of a floor and a query that has current users that are logged on to computers, what I want to achieve is that if I put box1-10 (computers) they change colors when they are in use or currently free. any help would be much appreciated

 

[vbaInet]

Conditional Formatting is the answer:

http://www.techonthenet.com/access/forms/cond_format.php

And I'm guessing you are talking about textboxes?

 

[Cozzy]

well I guess it really doesn't matter, I have in the Form Heading the Floor plans with the box shape not text box hence I can't use conditional formatting and query then runs in the Detail part.

 

[vbaInet]

Well you already know that conditional formatting works for textboxes so why not make the textboxes look like boxes and then use conditional formatting?

 

[Cozzy]

lol yeah I know but I guessed I lied when I said Mini Project as it will eventually expand (if I get it to work) to have over 3000 computers with different floors!! in my head it's achievable but I was wondering also If I put ="computer name" and tag all computers on that form can I run a conditional expression to say iff ([computer.tag] = curr_host, vbred, vbgreen) this is an example?

 

[vbaInet]

Is this for a report or a form? If it's for a form, is the form going to be Datasheet or Continuous?

 

[Cozzy]

thanks for the quick responses mate, it is a continuous form which will re-query say every 5 mins (I know how to do that) or on a "Find me a Computer" button, I have done a conditional formatting form previously for a service contract database I design where I had it showing different colours depending on the amount of time a call was open?

 

[vbaInet]

If it's a continuous form then you can only use Conditional Formatting. Doing it any other way won't work.

 

[mdlueck]

I am doing my own "conditional formatting" via VBA code. I have Validation classes to make sure that all form field values are proper prior to sending them to the SQL Server BE DB. The code first validates each value. Then it checks the flag for each field and colors them appropriately, as follows:

  'Update UI with good/bad field indications
  'flduserid
  If flgBaduserid = True Then
    MePointer.flduserid.BackColor = vbRed
    flgBadValue = True
  Else
    MePointer.flduserid.BackColor = vbWhite
  End If

Me.Pointer is the Form having passed Me to the Validation class and the Validation class having received it via ByRef into variable MePointer.

You could code such directly into your form, and simple use Me as the code would be on the actual form. I went a bit fancier to externalize the Validation from the Form and thus the same Validation class works for both the Add/Edit record forms.

A True value in variable flgBadValue prevents the code from issuing the DB Insert/Update call and leaves the Form for the user to fix the values in the red background fields.

 

[vbaInet]

I am doing my own "conditional formatting" via VBA code. I have Validation classes to make sure that all form field values are proper prior to sending them to the SQL Server BE DB. The code first validates each value. Then it checks the flag for each field and colors them appropriately, as follows:

  'Update UI with good/bad field indications
  'flduserid
  If flgBaduserid = True Then
    MePointer.flduserid.BackColor = vbRed
    flgBadValue = True
  Else
    MePointer.flduserid.BackColor = vbWhite
  End If

We're talking about a Continuous Form here. That kind of validation would work in a report but won't work for a continuous form. For Single Forms this is valid.

 

[mdlueck]

We're talking about a Continuous Form here.

You are correct in that case. It is impossible to differentiate via code between instances of named controls in the Continuous Form environment.