How to place an unbound images on the form based on query results?

[serhii7777]

Hello, I am building a database that keeps track of some events. I need to plot the events on a map. In other words, a query returns a number - digit, the number of incidents of a particular kind. I need to place a red dot or another image on a form where I already embedded a map. The number of dots should correspond to the result of a query. I do not have actual Geo locations or grid references - I can only build a schematic and approximate map. I simply put an unbound image inside a form - a picture of a map and I would like to place dots on that object corresponding to the result of a select query. Any assistance or ideas would be greatly appreciated. I am not a specialist in VBA, so if you provide code (I hope you do), please, explain also how to put it in the database and how to use it. Thank you very much for the support! Serhii7777

[patjones]

Hi -

Are you saying that you want to place dots on different locations on the map depending on the query results? Or do you just want to place a certain number of dots somewhere on the form?

Pat

[serhii7777]

hi Pat! I need both... I need to place dots in a specific place on the map and the number of dots should correspond to the query result. Perhaps, I should explain in more detail the issue I have. I built a database that stores information about incidents. The information is sometimes ambiguous and shows only - district name. I took a real map of a region and cut it in squears. Each squere became an independed picture (jpeg file), which I embedded in a form. I put them side by side like a puzzle, so on the normal form view it looks like a map. Now, when I run a querry say for a district A, it returns number of traffic incidents, number of criminal attacks, etc. for a district. What I need is to put a corresponding number of dots (different colors for each kind) within the boundaries of each individual image/unbound object on the form. Thank you for helping me. Serhii

[NeoPa]

As you don't mention what type of data the query will be returning I will assume that you have a way of determining whereabouts on your form you want the dots to appear depending on the results of your query. That leaves the mechanism you would utilise to show each dot in the position determined.

For this I would suggest pre-creating a number of such dots on your form which start out as not Visible. In the Format event procedure of your form I would get the co-ordinates (and maybe even colour) of the next item to show from your data (you will probably need a loop here) and update it with the X- & Y- co-ordinates and the colour, then set it Visible.

[ADezii]

In my opinion, what you are requesting is well beyond the realm of VBA. I am referring to precisely positioning a series of dots, different colors, on either 1 of 4 Embedded Images on a Form. The closest parallel to this that I can think of is the Circle() Method in VBA whereby you can precisely position 'Filled Circles' of varying Circumferences, Fill Colors, and Circumference Colors. Unfortunately, this can only be accomplished on Reports, not Forms, and I'm pretty sure that they cannot be overlaid on Embedded Images. I think that you may need to turn to Visual Basic, or another Programminig Language, on this one. Good Luck.

[Stewart Ross]

Can I also add to what my colleagues have advised you by saying that your requirement is best satisfied using a Geographical Information System (GIS) such as MapInfo (which is also programmable with a VB interface). GIS packages are based on built-in scalable maps, from street level maps showing individual streets and houses to 1:500,000 large-scale views of the country concerned. Many can link via ODBC to database data.

The two GIS systems used at my workplace (MapInfo and a specialised risk modelling package based on the Wingz GIS system) both use SQL-like query languages that allow for conditional display of incidents (fires and other emergencies in our case) by geographic location. Both also can do stuff automatically such as produce scaled circles at particular locations, with radius and fill colour dependent on dynamic conditions that test the underlying data.

One of their remarkable features is that they use multiple layers - a bit Photoshop-like - to display features on top of the maps. This gives great variety in the way that displays of topic of interest density can be built up.

Trying to program such a system yourself... well you may get results after you invest a fair amount of time, but GIS packages are designed for this application and would save you a lot of effort in the long run.

-Stewart

[serhii7777]

Dear all! thank you very much for the valuable suggestions. I see now that the problem is much larger that I initially thought. I agree with the advice to use MapInfo and other GIS systems. However, my practical issue is that I don’t have exact geo grids or coordinates to use as parameters for plotting. I have only generic notion of a location for each event/incident. After some research I realized there are two basic options to solve the problem: 1) use the X,Y method for spatial analysis – i.e. create a virtual X,Y grid over an imbedded image and then using a reference table where each location falls under a range of X and Y coordinates, built a scatter graph… It has many limitations and hard to implement if one is (like me) an intermediated Access user; 2) Use PSet method for placing dots inside manually drawn rectangles over the background map. This was suggested by another Access Guru and seems to be a more viable method. Here is how it supposed to work in theory: A form is created that contains two controls for entering time range – i.e., Start date and End Date, so all events between the two dates are selected later using OpenArgs parameter. Then a button calls a report where the PSet is used to place dots inside pre-positioned rectangles above a respective location on a map. When report is generated, a number of event procedures are executed. In particular, there is an event procedure that is executed at the point where the detail record is formatted and another that is executed at the point where the detail record is printed. One of these event procedures is used to execute the code that scans the set of records in the table that match the range of dates entered on the form. There are some conventions how to use the method and how to name the objects. The code bellow was suggested by the Access Guru and provides a theoretical outline. I tested it and I couldn’t make it work… But I am sure it is me rather than code… If you could make it work, please, let me know so I can see where I made mistakes. The code should be placed in the Report module and rectangles should be drawn over the embedded map in the Report detail section.

Option Compare Database 
Option Explicit 
' Define some global arrays to be used in drawing the dots 
Dim countArray() As Integer  	 ' Number of dots in the rectangle 
Dim xArray() As Single  		 ' Center of the rectangle 
Dim yArray() As Single		 ' Center of the rectangle 
Dim nameArray() As String	 ' Name of the rectangle 
Dim arraySize As Integer		 ' Number of rectangles 
Const DOTSIZE As Single = 5#	 ' Size of a dot in pixels 
' Primary procedure to perform the drawing. 
' This procedure is called once for the report (which has only one record) 
' (It may be necessary to move this to Detail_Print, instead) 
' 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
Call BuildArrays 
Call ProcessRecords 
End Sub 
' Procedure to collect data about the rectangles 
' 
Public Sub BuildArrays() 
Dim rect As Rectangle 
Dim ctrl As Control 
'  Set the scale mode so all dimensions are in pixels 
Me.ScaleMode = 3 				' Pixels 
' Initialize a counter 
ArraySize = 0 
' Look at every control, collecting information on the rectangles 
For Each ctrl In Me.Controls 
If ctrl.ControlType = acRectangle Then ' Make the rectangle-specific properties accessible 
Set rect = ctrl 					' Allocate space in the arrays 
arraySize = arraySize + 1 
ReDim Preserve countArray(arraySize) 
ReDim Preserve xArray(arraySize) 
ReDim Preserve yArray(arraySize) 
ReDim Preserve nameArray(arraySize) 	' Initialize the count of dots displayed for this rectangle 
CountArray(arraySize - 1) = 0 			' Determine the center of this rectangle 
xArray(arraySize - 1) = (CSng(rect.Width) / 2#) + CSng(rect.Left) 
yArray(arraySize - 1) = (CSng(rect.Height) / 2#) + CSng(rect.Top) 
' Capture the name of this rectangle 
nameArray(arraySize - 1) = rect.Name 
End If 
Next ctrl 
End Sub 
' Procedure to process records (note that the report has only ONE 
‘ "record". ALL ' handling of the table is performed in the recordset that is ‘ generated in this ' procedure, NOT in the RecordSource of the report). 

Public Sub ProcessRecords() 
Dim rs As DAO.Recordset 
Dim dates() As Date 
' Initialize various drawing properties 
Me.DrawMode = 13	' CopyPen 
Me.DrawStyle = 0		' Default 
Me.DrawWidth = DOTSIZE ' Get the starting and ending dates of the incidents to be 
					displayed 

dates = Split(Me.OpenArgs, ";") ' Open a recordset consisting of all incidents for those dates 
Set rs = CurrentDb.OpenRecordset( _ "SELECT * FROM tIncidents WHERE DateOfIncident BETWEEN #" _ & dates(0) & "# AND #" & dates(1) & "#;", _ dbOpenSnapshot) 

' Prepare to loop through the records 
rs.MoveFirst 
Do Until rs.EOF 
DisplayDot (rs) 
rs.MoveNext 
Loop 
End Sub 
 
' Procedure to display a dot 
Public Sub DisplayDot(rs As DAO.Recordset) 
Dim x As Single 
Dim y As Single 
Dim i As Integer 
Dim rectangleName As String 
Dim rho As Single 
Dim theta As Single 
Dim fillColor As Long 
Select Case rs!IncidentType   		' Convert the incident type to a color 
Case "Traffic Accident": 
fillColor = RGB(0, 0, 255) 
Case "Criminal Attack": 
fillColor = RGB(0, 255, 0) 
Case "IED Explosion": 
fillColor = RGB(255, 0, 0) 
Case Else: 
fillColor = RGB(64, 64, 64) 
End Select 
Me.fillColor = fillColor 
 
' Construct the rectangle name (this procedure REQUIRES that the 
' rectangles be assigned names consisting of the Province name, followed 
' by a hyphen, followed by the District Name) 
' 
rectangleName = rs!Province & "-" & rs!District 

' Find the rectangle for this incident 

For i = LBound(nameArray) To UBound(nameArray) 
If nameArray(i) = rectangleName Then 

' Increment the count of dots displayed in this rectangle 

countArray(i) = countArray(i) + 1 
' Calculate the next position on a logarithmic spiral starting 
' at the center of this rectangle 
theta = CSng(countArray(i)) / 3# 
rho = Exp(theta) 
x = rho * Cos(theta) + xArray(i) 
y = rho * Sin(theta) + yArray(i) 
' Display a dot at the calculated position 
Me.PSet (x, y) 
' Exit the for loop and the procedure 
Exit For 
End If 
Next i 
End Sub

[ADezii]

I'm on Vacation right now, but I'll be happy to play with the code when I return and see what I can do, if anything. What I would like from you is some sample data that will be the basis for the Recordset being processed. This would be a good starting point for me when I return. You san simply Upload a Database consisting of a single Table containing all these Fields referenced.

[ADezii]

I've written some relatively simple code, as least compared to that listed in Post #4, that may/may not be useful to you. Simply download the Attachment for a Visual.