Solved

Using Access form to create a Mappoint 2013 data map

Posted on 2013-10-22
8
1,322 Views
Last Modified: 2013-10-27
Hello,

I have a form with five fields that is bound to a table "tblMappoint". I have button on the form that when pressed should open Mappoint and plot the data points using address, city, state, and zip. I am getting a VBA compile error: Method of data member not found on the line "rs.Edit". Any help in resolving this would be appreciated.

===================================================
Here is the fields in the table
===================================================
ID = PK
Address = short text
City = short text
State = short text
Zip = short text

===================================================
Here is the code behind the button
===================================================

Dim APP As MapPoint.Application

Private Sub Geocode_Click()

  Dim MAP As MapPoint.MAP
  Dim FAR As MapPoint.FindResults
  Dim LOC As MapPoint.Location
 
  Set APP = CreateObject("MapPoint.Application")
  APP.Visible = True
  Set MAP = APP.ActiveMap
 
  Dim rs As Recordset
  Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblMappoint")

  Do Until rs.EOF = True
    Set FAR = MAP.FindAddressResults(rs("Address"), rs("City"), , rs("State"), rs("Zip"))
    Set LOC = FAR(1)
    rs.Edit
    rs!MP_Latitude = LOC.Latitude
    rs!MP_Longitude = LOC.Logitude
    rs!MP_MatchedTo = GetGeoFieldType(LOC.Type)
    rs!MP_Quality = GetGeoQuality(FAR.ResultsQuality)
    rs!MP_Address = LOC.StreetAddress.Value
    rs.Update
    rs.MoveNext
  Loop

End Sub
0
Comment
Question by:htamraz1
  • 4
  • 3
8 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 39591064
>>> Dim rs As Recordset

Try changing this to


Dim rs As DAO.Recordset
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39591068
Also make sure that you have your references set properly.

From the VBA editor:

Tools --> References

Check the box for the Microsoft DAO Object Library

Click OK to save and exit the references dialog.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 39591160
Also make sure the code compiles...

And just for fun move the recordset declaration to the top of the sub with the other declarations...

Sometimes the code line identified is not the actual line that has the problem.

So can you loop the table without editing anything?

Is  "LOC.Latitude" a valid value?,
I see the Latitude for NYC as both:
40° 42' 51" N
And as:
40.6700° N
So you need to be sure that what you are trying to insert is valid for this field.
(not sure if the degrees symbol is relevant or not...?)

If you hardcore a value, can it be updated?
    rs!MP_Latitude = 40° 42' 51" N
or,:
    rs!MP_Latitude = 40.6700° N

JeffCoachman
0
 

Author Comment

by:htamraz1
ID: 39591325
Thanks to both of you. Adding DAO did resolve the previous error; however, I have a compile error on two missing functions -- GetGeoFieldType and GetGeoQualityare.

FYI, I am adapting the code found here
http://www.mapforums.com/access-vba-programming-part-i-geocoding-mappoint-28228.html

Where would I find the these two helper functions referenced in the code:
GetGeoFieldType and GetGeoQualityare

I need them for the code to compile correctly.

Thank you again.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 39591438
Those appear to be user defined functions that you will need to either obtain or find some suitable work-around for if you need the Quality and MatchedTo fields.  (or perhaps omitting them is an option depending on your application)

I briefly looked around on the web for them, to no avail.  My best guess is that they are copyrighted functions.  From what I can tell, they were developed by Eric Frost - the author of that post - and are possibly downloadable with a membership to that site, or they may be a part of a commercial product developed by him.  

You might try contacting him directly.

Not sure what else to say here...
0
 

Author Comment

by:htamraz1
ID: 39591458
I think you're right. I posted a message on his forum to see what he has to say. I am comfortable closing the loop on this post, but let me wait for a day or so before I do that just in case he replies and I can add that to the post.
0
 

Accepted Solution

by:
htamraz1 earned 0 total points
ID: 39591942
The code for the functions
GetGeoFieldType
GetGeoQualityresult

was posted by Eric Frost at
http://www.mapforums.com/access-vba-programming-part-i-geocoding-mappoint-28228.html
0
 

Author Closing Comment

by:htamraz1
ID: 39603613
I had to research the solution for the final part of the problem
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now