its complicated.....

[Sam Summers]

Hi everyone,

This is beyond my current capabilities so i will try to explain:

In the form attached you will see six buttons in pairs.

Each button is numbered e.g. Btn1 - Btn6

I have been able to insert the position number which correlates to the button number into the table and corresponding to each location as in the subform in view.

This form (of which there will be one for other locations with more or less positions) is the view form and what i am trying to do is to recognise the positions that will be selected as in the subform (RH column) in relation to each record and if there is a number (position No) in the field i can then insert that number into the list of Positions on the left of the subform and in turn make the corresponding button change colour as a key to what type of fire extinguisher is in that position.

Hope this makes sense and i hope you can help me.

Many thanks in advance

 

[Uncle Gizmo]

I have a sample database which I found many moons ago probably 20 years! I thought it might be applicable to your problem. I can provide you with a copy. It's a "Backyard Ownership Map"... Here's a video demonstration of how it works.

EDIT:- Sample was Created by "Michael Kaplan"

Was this Him?

RIP Michael J Kaplan (of Sorting It All Out blog)

If MS (Multiple Sclerosis) did not kill him, then another MS (Microsoft) certainly had helped him meet fate. In his heyday, Michael Kaplan was one of the most popular guys on MSDN (a Microsoft webs…

vsubhash.wordpress.com

More Info:-
File: MAP_DOC.TXT in the _KAPLN09.EXE archive

RE: Create Map Interfaces Without Getting Lost
by Michael Kaplan
in July 1996 Smart Access

Compatibility: Access 2 and Access 95

This archive contains:K MAP_Doc.TXT - (this file)
MAP20.MDB - the Access 2 sample database discussed in article
MAP70.MDB - the Access 95 sample database discussed in article

See article for more details on using these files

Mapping Sample Database - Nifty Access​

Download HERE:-

Mapping Sample Database - Nifty Access

Map of Microsoft Access Data This demo database demonstrates how you can collect the mouse pointer coordinates and link them to to a table, providing information about the actual point selected. It's a very nice sample. Sample dB and PDF file provided by "Michael Kaplan" I'm posting it here as...

niftyaccess.gumroad.com

 

[CJ_London]

buttons don't have conditional formatting so you will need to use some code.

not quite clear to me how everything relates but if your table bottom right has a field to store the button name then use code in your form open event along these lines

dim rs as dao.recordset
set rs=currentdb.openrecordset("SELECT * FROM nameofbottomrighttable WHERE buildingID=1",dbopensnapshot)
with rs
   while not .eof
       select case !description
            case "CO2"
                me(!ButtonName).backcolor=vbred
            case "foam"
                me(!ButtonName).backcolor=vbblue
            case....
        end select
       .movenext
  wend
end with
rs.close
set rs=nothing

Not description is a really poor name for a field name, it is a reserved wor.

I would probably have another table to store the descriptions and the colour required

 

[arnelgp]

sorry but you need an Image there not dynamic button/label placement.
your position is relative to the screen/laptop you are working right now.
when you move your db to smaller/bigger screen, and the form resizes, what
do you think will happen to your buttons/label?

since each building has a "fixed" where to put the FireExt, you should make
the map static and just update it when relocated.

 

[Sam Summers]

Hi guys and thank you. Its probably less complicated than i explained so i''l try and let you know where i am so far.

The buttons work a treat and i can already change there backcolor with code which works great and when i click on them my code also inserts the position number of the button into the table. All working great.

So basically i am inserting an image and then each button i can move wherever i want to - no problem.

Its just when the user views the building i want the positions occupied (as numbered) to indicate by their respective colours and empty spaces to remain as standard

 

[Sam Summers]

As further clarification - on the left of the subform you will see the list of Positions from 1 to 6. All i want to do is to insert whatever position number along with its matching position number in these textboxes.
The subforms data is from a query

 

[moke123]

Since it appears you are using Visio, Have you looked into Vba for Visio?

https://docs.microsoft.com/en-us/office/vba/api/overview/visio

https://docs.microsoft.com/en-us/office/vba/visio/concepts/about-displaying-data-graphically-visio

 

[Sam Summers]

Since it appears you are using Visio, Have you looked into Vba for Visio?

https://docs.microsoft.com/en-us/office/vba/api/overview/visio

https://docs.microsoft.com/en-us/office/vba/visio/concepts/about-displaying-data-graphically-visio

I'm not actually using visio, its just our own drawings inserted as an image on the form.

What i am trying to do would be something like:

Insert into (from the data in the subform or from the table or query?) position 1 where the number = 1 and insert into 2 where the number = 2 and so on?

 

[Sam Summers]

sorry but you need an Image there not dynamic button/label placement.
your position is relative to the screen/laptop you are working right now.
when you move your db to smaller/bigger screen, and the form resizes, what
do you think will happen to your buttons/label?

since each building has a "fixed" where to put the FireExt, you should make
the map static and just update it when relocated.

After reading your post again you are right. Hmmm have to think about that? I do have images of the coloured circles i need that i could use?

 

[Sam Summers]

So i am trying to check the subform data in the field "Position" with this code but i really am not sure how to do this? I am just trying out ideas.

Me!MessViewSubform.Form!Position.SetFocus
If Me!MessViewSubform.Form!Position.Text = "1" Then
Me.Position1.Text = "1"
If Me!MessViewSubform.Form!Position.Text = "2" Then
Me.Position2.Text = "2"
If Me!MessViewSubform.Form!Position.Text = "3" Then
Me.Position1.Text = "3"
If Me!MessViewSubform.Form!Position.Text = "4" Then
Me.Position2.Text = "4"
If Me!MessViewSubform.Form!Position.Text = "5" Then
Me.Position1.Text = "5"
If Me!MessViewSubform.Form!Position.Text = "6" Then
Me.Position2.Text = "6"
Else
End If
End If
End If
End If
End If
End If

 

[Pat Hartman]

The .text property is ONLY used when the control has the focus. In ALL other situations, use the .value property or eliminate the property reference entirely since .value is the default so Me.somecontrol is equivalent to Me.somecontrol.value and is shorter so more desirable.

Since the value being set is the same as the from value, why not just use a single statement?
Me.Position1 = Me.MessViewSubform.Form!Position

Also, Why are you copying data from the subform to the mainform? The subform has many records and the mainform has ONE. When you execute that line of code, you are copying the value from the subform record that has the focus. Unless you specifically click into the subform and set focus to a different record, you will ALWAYS get the value from the first record displayed on the form.

And finally, it is always better to push than to pull. That way there is no question which value is getting copied. Therefore, when you are in the subform and know that is the value you want to copy, push it to the parent form.
Me.Parent!Position = Me.Position

 

[Sam Summers]

The .text property is ONLY used when the control has the focus. In ALL other situations, use the .value property or eliminate the property reference entirely since .value is the default so Me.somecontrol is equivalent to Me.somecontrol.value and is shorter so more desirable.

Since the value being set is the same as the from value, why not just use a single statement?
Me.Position1 = Me.MessViewSubform.Form!Position

Also, Why are you copying data from the subform to the mainform? The subform has many records and the mainform has ONE. When you execute that line of code, you are copying the value from the subform record that has the focus. Unless you specifically click into the subform and set focus to a different record, you will ALWAYS get the value from the first record displayed on the form.

And finally, it is always better to push than to pull. That way there is no question which value is getting copied. Therefore, when you are in the subform and know that is the value you want to copy, push it to the parent form.
Me.Parent!Position = Me.Position

Hi Pat, Thank you for that.
The subform is on there as an unbound form based on a query.
I'm just trying to think of a way to achieve this? Hmmmmm
This form is based on one building of which there are in excess of twenty other buildings.
Some of the locations of the items may be blank at the time of this building being viewed but thinking now maybe i can make the main form have these positions? But all items are in the table 'Items' and each one has a field 'Position' which is populated with the number of the button clicked on when the item is set at the location in the building.
This building here has 6 locations but others may have more or less?

So basically i don't know how to make the positions on the main form = each records 'position' field value for the location and its 'type' of extinguisher which is critical too?

 

[Pat Hartman]

Sounds like the data needs to be in the parent table if it is the same for all child records.

If you need to do a conversion, to move the data from the child records and then get rid of it from the child table, we can help you to do that but it really makes no sense to pick data from some random child record to populate a parent record which is essentially what is happening now.

If the data is unique for a position, then the data belongs in the child table. If you can show us a real world example of the data in both tables, we can help you to get it organized correctly.

 

[Sam Summers]

Sounds like the data needs to be in the parent table if it is the same for all child records.

If you need to do a conversion, to move the data from the child records and then get rid of it from the child table, we can help you to do that but it really makes no sense to pick data from some random child record to populate a parent record which is essentially what is happening now.

If the data is unique for a position, then the data belongs in the child table. If you can show us a real world example of the data in both tables, we can help you to get it organized correctly.

Thank you so much Pat! Sorry for the delay but here it is.
It is still very much in development stage.
The only location currently working as i try and solve this is 'The Mess' in all selections.
You will see that on entering an item, once you click save you are then able to click on a button which will change colour depending on what type of Extinguisher is chosen (not fire blanket thought).
So this works but what i am trying to do is to save that so that when someone views the location they will see all the colours of what extinguishers are in each position or not coloured (just default colour) if there is not currently and extinguisher in place.
Hope this makes sense?

 

[Pat Hartman]

Since the forms that show the locations are not updateable, I would bind them to a crosstab query so that all the locations will appear as columns in the same row. That way, the RecordSource for the form returns a single record. You can use the same query for multiple forms. They just need to have criteria and the same maximum number of extinguishers. Make the column headers fixed in the crosstab so if there are 8 slots for extinguishers, you always get 8 columns. That way your position fields will be bound rather than unbound. The rowSource for the position combos will be to the type table. The visible field will be type and the hidden field will be the color.

You might be able to make this work without code but I didn't try so I can't say for sure but if you need code, you would use the form's Current event to take the hidden color from the bound position combo and use it to colorize the button for the extinguisher. Obviously, I don't have any code sample to post. To make the code simpler, use numeric suffixes to tie the buttons to the combos. Me.btn2 = Me.cboPosition2. You can hard-code or loop. Loop is cleaner in case you get 20-30 on one picture but I think you really need to keep the pictures smaller so 10 might be a rational max. Break up parts of a room if that makes sense.

You need a type table and that is where you can assign a color to the type. You can use theme colors (my choice) or get the actual color values and use those.

Naming consistency is your friend. You want to use a naming scheme that lets you group rooms by building and probably a prefix that separates all the picture forms from the other forms.

IMPORTANT -
1. remove the table level lookups. They are a crutch and will just get in the way once you start building queries and writing code. Lookups should be combos on Forms NOT on Tables. All the table level lookup does is to obfuscate the actual contents of a field and the MS implementation is inconsistent at best so in some cases, you will see the text you expect to see but in others, you will see the numeric ID.
2. NEVER use special characters or embedded spaces in your object names. Those characters are invalid as far as VBA is concerned and that makes your coding and SQL require enclosing the offending names in square brackets and in the case of forms/reports, Access will actually change the name of controls bound to these fields.
3. You also need a unique index on Location and position in the Item table to ensure that you don't create duplicate data that will never be mapped correctly.
4. The data entry form doesn't seem to work. I can only enter a couple of the fields.
5. Red is a lovely color but not really appropriate when you are designing forms. You need to choose colors that are less brilliant or they can be over bearing and jarring to users.

 

[Sam Summers]

Since the forms that show the locations are not updateable, I would bind them to a crosstab query so that all the locations will appear as columns in the same row. That way, the RecordSource for the form returns a single record. You can use the same query for multiple forms. They just need to have criteria and the same maximum number of extinguishers. Make the column headers fixed in the crosstab so if there are 8 slots for extinguishers, you always get 8 columns. That way your position fields will be bound rather than unbound. The rowSource for the position combos will be to the type table. The visible field will be type and the hidden field will be the color.

You might be able to make this work without code but I didn't try so I can't say for sure but if you need code, you would use the form's Current event to take the hidden color from the bound position combo and use it to colorize the button for the extinguisher. Obviously, I don't have any code sample to post. To make the code simpler, use numeric suffixes to tie the buttons to the combos. Me.btn2 = Me.cboPosition2. You can hard-code or loop. Loop is cleaner in case you get 20-30 on one picture but I think you really need to keep the pictures smaller so 10 might be a rational max. Break up parts of a room if that makes sense.

You need a type table and that is where you can assign a color to the type. You can use theme colors (my choice) or get the actual color values and use those.

Naming consistency is your friend. You want to use a naming scheme that lets you group rooms by building and probably a prefix that separates all the picture forms from the other forms.

IMPORTANT -
1. remove the table level lookups. They are a crutch and will just get in the way once you start building queries and writing code. Lookups should be combos on Forms NOT on Tables. All the table level lookup does is to obfuscate the actual contents of a field and the MS implementation is inconsistent at best so in some cases, you will see the text you expect to see but in others, you will see the numeric ID.
2. NEVER use special characters or embedded spaces in your object names. Those characters are invalid as far as VBA is concerned and that makes your coding and SQL require enclosing the offending names in square brackets and in the case of forms/reports, Access will actually change the name of controls bound to these fields.
3. You also need a unique index on Location and position in the Item table to ensure that you don't create duplicate data that will never be mapped correctly.
4. The data entry form doesn't seem to work. I can only enter a couple of the fields.
5. Red is a lovely color but not really appropriate when you are designing forms. You need to choose colors that are less brilliant or they can be over bearing and jarring to users.

Absolutely brilliant Pat! Thank you SO much for your time.

The entry form i disabled the other fields temporarily to focus on this problem only just now.

Before you replied and on giving it more thought i actually created a crosstab query as you describe and so i will now see how i get on with all your points and advice.
It may take a a few days but i will get back to here with my results.

Once again i would like to thank you and all the people who contribute to this forum and the many people who have helped me over the years as without this forum i would not have been able to achieve anything and i continue to learn from all of you.

 

[Sam Summers]

Since the forms that show the locations are not updateable, I would bind them to a crosstab query so that all the locations will appear as columns in the same row. That way, the RecordSource for the form returns a single record. You can use the same query for multiple forms. They just need to have criteria and the same maximum number of extinguishers. Make the column headers fixed in the crosstab so if there are 8 slots for extinguishers, you always get 8 columns. That way your position fields will be bound rather than unbound. The rowSource for the position combos will be to the type table. The visible field will be type and the hidden field will be the color.

You might be able to make this work without code but I didn't try so I can't say for sure but if you need code, you would use the form's Current event to take the hidden color from the bound position combo and use it to colorize the button for the extinguisher. Obviously, I don't have any code sample to post. To make the code simpler, use numeric suffixes to tie the buttons to the combos. Me.btn2 = Me.cboPosition2. You can hard-code or loop. Loop is cleaner in case you get 20-30 on one picture but I think you really need to keep the pictures smaller so 10 might be a rational max. Break up parts of a room if that makes sense.

You need a type table and that is where you can assign a color to the type. You can use theme colors (my choice) or get the actual color values and use those.

Naming consistency is your friend. You want to use a naming scheme that lets you group rooms by building and probably a prefix that separates all the picture forms from the other forms.

IMPORTANT -
1. remove the table level lookups. They are a crutch and will just get in the way once you start building queries and writing code. Lookups should be combos on Forms NOT on Tables. All the table level lookup does is to obfuscate the actual contents of a field and the MS implementation is inconsistent at best so in some cases, you will see the text you expect to see but in others, you will see the numeric ID.
2. NEVER use special characters or embedded spaces in your object names. Those characters are invalid as far as VBA is concerned and that makes your coding and SQL require enclosing the offending names in square brackets and in the case of forms/reports, Access will actually change the name of controls bound to these fields.
3. You also need a unique index on Location and position in the Item table to ensure that you don't create duplicate data that will never be mapped correctly.
4. The data entry form doesn't seem to work. I can only enter a couple of the fields.
5. Red is a lovely color but not really appropriate when you are designing forms. You need to choose colors that are less brilliant or they can be over bearing and jarring to users.

Hi Pat, I think i have the crosstab working but i am not quite sure about this 'The rowSource for the position combos will be to the type table.'?

What position combos? Sorry if i am being stupid but i have been trying all kinds of things and re-reading this but still just cannot see it?

I have this

 

[Pat Hartman]

The Extinguisher table should be the RowSource for the position. combos. So, in position1, you might have extinguisherID = 4 but the combo will show CO2. For position2, you might have extinguisherID =1 and the combo will show Water.

 

[Sam Summers]

The Extinguisher table should be the RowSource for the position. combos. So, in position1, you might have extinguisherID = 4 but the combo will show CO2. For position2, you might have extinguisherID =1 and the combo will show Water.

Ok, i will keep trying to get it - Thank you

 

[Pat Hartman]

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.