Image type form that displays cubicle of search result?

[Zipster1967]

I am trying to create a map type form that will use the rectangle control to represent all the different cubes in an office building and I would like the cube that the person is searching for based on the employee look-up table to change either the rectangle color of the corresponding cubicle or to display an arrow. Are either of these things possible? If not how would I go about accomplishing something similar? Any ideas? Thanks in advance for any help.

[June7]

Yes, I think this could be done. You have a combobox or listbox where user enters search criteria. Then VBA code in the AfterUpdate event sets the background color property of the correct rectangle. The code could be a Select Case structure. Or if you use textboxes as the rectangles can take advantage of conditional formatting, no VBA needed for that. Background color would be conditional on value in combobox.

[Zipster1967]

That sounds pretty simple. Now is where it gets complicated. In this situation I might have two Sarah Johnson's working for the company one in the HR Department on the fifth floor and one in R&D on the 7th floor. I have my form designed to let the person searching to add the department to narrow the resulting results if they know it, but if they don't enter any other information but the name the form will list multiple records of both Sarah Johnsons. Can I have the form display the form with different highlights when they select different records in the subform display? (That is what I am using to display the multiple results) BTW. Thanks for the info. Any hints on how to structure the code. (I am not a great VBA coder by any stretch of the imagination.)

[June7]

Probably could be done. So in a button click event, follow this logic:

1. set color property of all rectangles to default. This is needed if you allow multiple searches and want the display reset for each search. In other words, search for Smith, form stays open, now reset and Search for Jones. If you don't reset then both rectangles will be colored.

2. Loop code to process all selected records and highlight relevant rectangle.

Rough code example. Presumes a recordset filtered to only records meeting search criteria.

 
Private Sub btnHighlightRectangles_Click()
 
Dim cn As ADODB.Connection
Dim rs As DAO.Recordset
Dim n As Integer
Set cn = CurrentProject.Connection
Set rs = Me.ctrEmplList.Form.RecordsetClone
 
'code to set rectangles to default backcolor; name the rectangles similar like rec1, rec2, etc
For n = 1 to total number of controls here
    Me.Controls("Rec" & i).BackColor = vbWhite
Next
 
'alternate code using Controls collection
'not tested and not sure of syntax
'required if rectangle name includes room number
Dim c As Control
For Each c In Me.Controls
If c.Name Like "rec*" Then c.BackColor = vbWhite
Next
 
'code to read each record of filtered recordset and set color of rectangle
rs.MoveFirst
While Not rs.EOF
    Select Case rs!RoomNo
        Case 111A
            Me.rec1.BackColor = vbGreen
        'continue for other rooms
    End Select
    rs.MoveNext
 
    'alternate to the Select Case if rectangle names follow room number structure
    Me.Controls("rec" & rs!RoomNo).BackColor = vbGreen
 
Wend
 
End Sub