its complicated.....

[Sam Summers]

Well everyone - Thank you all so much.

I have managed to get it working with the buttons and all seems fine at this stage (still more to build and test).

What i did was i changed the table relationships and also removed the Lookup tables as suggested.

I tried all types of crosstab queries but after re-doing the relationships based on the new table structures i was able to create a subform for each position using queries like this example for each subform:

SELECT ItemType.ItemID, Item.LocationRef, ExtType.ExtType, Item.Position
FROM ExtType INNER JOIN (Item INNER JOIN ItemType ON Item.ItemID = ItemType.ItemID) ON ExtType.ExtinguisherID = ItemType.TypeID
WHERE (((Item.LocationRef)=7) AND ((Item.Position)=1));

Then i was assisted in creating a case statement code section (possibly to be refined yet) to colorize the buttons according to the international colour code for the type of Extinguisher at each position in the building which i will now do for every individual building that we have.

Below is an example of a section of my case statement code but as i said i may tweak this method to improve it based on the multiple amazing suggestions i have received in my other post "What am i doing wrong"

Select Case Me!Position1QrySubform.Form!Type

   Case Is = "Foam"
      Me.Btn1.BackColor = RGB(255, 228, 181)
   Case Is = "Water"
      Me.Btn1.BackColor = RGB(255, 0, 0)
   Case Is = "Powder"
      Me.Btn1.BackColor = RGB(30, 144, 255)
   Case Is = "CO2"
      Me.Btn1.BackColor = RGB(0, 0, 0)
   Case Is = "Wet Chemical"
      Me.Btn1.BackColor = RGB(0, 201, 87)

End Select

 

[Sam Summers]

I thought we discussed this. The reason for making the crosstab was so that you could bind the position combos to the crosstab and use the table to pick up the description of the extinguisher using the RowSource of the combo because you wanted a simple way to color the pictures of the extinguishers.

If you need the positions to be updateable, then get rid of the crosstab and put the positions in the subform instead of as unbound controls on the main form. Then to colorize the pictures, you need to loop through the subform.

OR

You can leave the main form bound to the crosstab but hide the position fields. Use the positions in the crosstab to set the colors but use the subform if you need to update the extinguisher. You need to requery the mainform in the AfterUpdate event of the subform to make the change visible in the main form though

Hi Pat, yes it was your guidance that enabled me to get where i am with it. I just reported back here so that everyone can know the outcome in case it may help someone else.

Thank you once again