its complicated.....

[Sam Summers]

And the position combos should be bound to the 8 extinguishers. Once all that works, we can figure out how to colorize the pictures.

I'm not sure why you are using Access to do this. You can't draw the rooms on the fly or even place the extinguishers so you need to "hard-code" each form with at least the diagram and location markers. What the database part of this can do is to allow you to swap one type of extinguisher for another but that's about it.

I don't like to talk people out of stretching their wings and trying something new and completely different but this isn't the kind of project that can be truly data driven. If it were even possible to move extinguishers on the fly, that would probably make it worth while but if you move an extinguisher, you need to modify the diagram on the form manually.

I think it will work but i'm just trying to get my head round it.
I can colorize the buttons fine and i can move them anywhere on the picture which is fine but they will eventually stay in their particular locations.
I wouldnt know what else to use other than Access? At the end of the day it is mainly a Database of Extinguishers and i am attempting to develop this graphical idea as a nice to have concept rather than essential.
Swapping the extinguishers is exactly right in what you said.
My ultimate idea would be to maybe display on a 'mouseover', the details of the extinguisher (Inspection Date, ID Number, Capacity etc.)

I like a challenge and if i crack it, it may help other people.

Thank you again

 

[Pat Hartman]

I thought you were doing this to print pictures to post in the various buildings

 

[Sam Summers]

I thought you were doing this to print pictures to post in the various buildings

That would be a lot easier

 

[Sam Summers]

I think it will work but i'm just trying to get my head round it.
I can colorize the buttons fine and i can move them anywhere on the picture which is fine but they will eventually stay in their particular locations.
I wouldnt know what else to use other than Access? At the end of the day it is mainly a Database of Extinguishers and i am attempting to develop this graphical idea as a nice to have concept rather than essential.
Swapping the extinguishers is exactly right in what you said.
My ultimate idea would be to maybe display on a 'mouseover', the details of the extinguisher (Inspection Date, ID Number, Capacity etc.)

I like a challenge and if i crack it, it may help other people.

Thank you again

Hi again,
Well i have been trying all kinds of things but still getting no-where?

The subform you see here, based on this SQL crosstab produces rows for each type of extinguisher which i then cannot get all of them to be inserted into the the position combos. I can only obviously get one rows extinguishers in them at a time which is no use?
I feel i will get there but being a basic user it may take some time......

TRANSFORM Count(Item.ItemID) AS CountOfItemID
SELECT Item.DescriptionRef, Item.LocationRef
FROM Item
WHERE (((Item.LocationRef)=7))
GROUP BY Item.DescriptionRef, Item.LocationRef
PIVOT Item.Position In (1,2,3,4,5,6);

 

[Lampje]

Is dit wat je wilt,

 

[Sam Summers]

Is dit wat je wilt,

Ja. maar ik heb ook net een ander idee. Dank u

 

[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

 

[Pat Hartman]

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

 

[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

 

[Pat Hartman]

You're welcome. Glad you got it working.