By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,303 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

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

P: 6
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
Apr 6 '10 #1
Share this Question
Share on Google+
8 Replies

Expert 100+
P: 931
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?

Apr 7 '10 #2

P: 6
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
Apr 7 '10 #3

Expert Mod 15k+
P: 31,768
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.
Apr 13 '10 #4

Expert 5K+
P: 8,699
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.
Apr 13 '10 #5

Expert Mod 2.5K+
P: 2,545
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.

Apr 14 '10 #6

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database 
  2. Option Explicit 
  3. ' Define some global arrays to be used in drawing the dots 
  4. Dim countArray() As Integer       ' Number of dots in the rectangle 
  5. Dim xArray() As Single           ' Center of the rectangle 
  6. Dim yArray() As Single         ' Center of the rectangle 
  7. Dim nameArray() As String     ' Name of the rectangle 
  8. Dim arraySize As Integer         ' Number of rectangles 
  9. Const DOTSIZE As Single = 5#     ' Size of a dot in pixels 
  10. ' Primary procedure to perform the drawing. 
  11. ' This procedure is called once for the report (which has only one record) 
  12. ' (It may be necessary to move this to Detail_Print, instead) 
  13. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
  14. Call BuildArrays 
  15. Call ProcessRecords 
  16. End Sub 
  17. ' Procedure to collect data about the rectangles 
  18. Public Sub BuildArrays() 
  19. Dim rect As Rectangle 
  20. Dim ctrl As Control 
  21. '  Set the scale mode so all dimensions are in pixels 
  22. Me.ScaleMode = 3                 ' Pixels 
  23. ' Initialize a counter 
  24. ArraySize = 0 
  25. ' Look at every control, collecting information on the rectangles 
  26. For Each ctrl In Me.Controls 
  27. If ctrl.ControlType = acRectangle Then ' Make the rectangle-specific properties accessible 
  28. Set rect = ctrl                     ' Allocate space in the arrays 
  29. arraySize = arraySize + 1 
  30. ReDim Preserve countArray(arraySize) 
  31. ReDim Preserve xArray(arraySize) 
  32. ReDim Preserve yArray(arraySize) 
  33. ReDim Preserve nameArray(arraySize)     ' Initialize the count of dots displayed for this rectangle 
  34. CountArray(arraySize - 1) = 0             ' Determine the center of this rectangle 
  35. xArray(arraySize - 1) = (CSng(rect.Width) / 2#) + CSng(rect.Left) 
  36. yArray(arraySize - 1) = (CSng(rect.Height) / 2#) + CSng(rect.Top) 
  37. ' Capture the name of this rectangle 
  38. nameArray(arraySize - 1) = rect.Name 
  39. End If 
  40. Next ctrl 
  41. End Sub 
  42. ' Procedure to process records (note that the report has only ONE 
  43. ‘ "record". ALL ' handling of the table is performed in the recordset that is ‘ generated in this ' procedure, NOT in the RecordSource of the report). 
  45. Public Sub ProcessRecords() 
  46. Dim rs As DAO.Recordset 
  47. Dim dates() As Date 
  48. ' Initialize various drawing properties 
  49. Me.DrawMode = 13    ' CopyPen 
  50. Me.DrawStyle = 0        ' Default 
  51. Me.DrawWidth = DOTSIZE ' Get the starting and ending dates of the incidents to be 
  52.                     displayed 
  54. dates = Split(Me.OpenArgs, ";") ' Open a recordset consisting of all incidents for those dates 
  55. Set rs = CurrentDb.OpenRecordset( _ "SELECT * FROM tIncidents WHERE DateOfIncident BETWEEN #" _ & dates(0) & "# AND #" & dates(1) & "#;", _ dbOpenSnapshot) 
  57. ' Prepare to loop through the records 
  58. rs.MoveFirst 
  59. Do Until rs.EOF 
  60. DisplayDot (rs) 
  61. rs.MoveNext 
  62. Loop 
  63. End Sub 
  65. ' Procedure to display a dot 
  66. Public Sub DisplayDot(rs As DAO.Recordset) 
  67. Dim x As Single 
  68. Dim y As Single 
  69. Dim i As Integer 
  70. Dim rectangleName As String 
  71. Dim rho As Single 
  72. Dim theta As Single 
  73. Dim fillColor As Long 
  74. Select Case rs!IncidentType           ' Convert the incident type to a color 
  75. Case "Traffic Accident": 
  76. fillColor = RGB(0, 0, 255) 
  77. Case "Criminal Attack": 
  78. fillColor = RGB(0, 255, 0) 
  79. Case "IED Explosion": 
  80. fillColor = RGB(255, 0, 0) 
  81. Case Else: 
  82. fillColor = RGB(64, 64, 64) 
  83. End Select 
  84. Me.fillColor = fillColor 
  86. ' Construct the rectangle name (this procedure REQUIRES that the 
  87. ' rectangles be assigned names consisting of the Province name, followed 
  88. ' by a hyphen, followed by the District Name) 
  89. rectangleName = rs!Province & "-" & rs!District 
  91. ' Find the rectangle for this incident 
  93. For i = LBound(nameArray) To UBound(nameArray) 
  94. If nameArray(i) = rectangleName Then 
  96. ' Increment the count of dots displayed in this rectangle 
  98. countArray(i) = countArray(i) + 1 
  99. ' Calculate the next position on a logarithmic spiral starting 
  100. ' at the center of this rectangle 
  101. theta = CSng(countArray(i)) / 3# 
  102. rho = Exp(theta) 
  103. x = rho * Cos(theta) + xArray(i) 
  104. y = rho * Sin(theta) + yArray(i) 
  105. ' Display a dot at the calculated position 
  106. Me.PSet (x, y) 
  107. ' Exit the for loop and the procedure 
  108. Exit For 
  109. End If 
  110. Next i 
  111. End Sub
Apr 15 '10 #7

Expert 5K+
P: 8,699
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.
Apr 16 '10 #8

Expert 5K+
P: 8,699
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.
Attached Files
File Type: zip (18.4 KB, 95 views)
Apr 18 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.