Building a single variable value of different records

[stephenaa5]

Hi, I'm oversimplifying my example, but here goes. I am trying to map multiple choices from a split form in Bing Maps. I have all of the selections figured out, but have to pass all of the addresses as ONE criteria. I have built a table that has all of the address information. That table is MapAddTbl. It has a field in it called ADDRESS. All good so far.

What I need to do is build a variable, call it MAPVAR that has all of the addresses in the table. Then add a prefix of "http://bing.com/maps/default.aspx?sp=" to it. So essentially if the table contains


1 Main, San Francisco, CA
33 Oak St, Los Angeles, CA

I need the resulting value of the variable to be:


http://bing.com/maps/default.aspx?sp=1 Main, San Francisco, CA~33 Oak St, Los Angeles, CA

From there, I will use the following statement to open a browser, go to bing, and display the sites.
Application.followhyperlink MAPVAR

Everything works fine, but I don't know how to build the one variable.

Any help would be greatly appreciated

[June7]

So you want to concatenate all the addresses into one looooooong text string, with the addresses separated by ~ character? How long a string do you expect this to be?

Open a recordset and loop through records.

[stephenaa5]

Hi, June7 (funny, my BD is june8)... That's what I'm trying to do, but it isn't working quite right. Here's my code. Everything works, but I don't get the next ADDRESS. Clearly, that's a big fail. It formats up correctly, and loops through the right number of records, just the value in ADDRESS doesn't get added to the string.

Thanks for the reply....

Private Sub Command519_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT address FROM MapAddTbl")
Dim address As Field
Dim BingStatement As String
BingStatement = "http://bing.com/maps/default.aspx?sp="

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True

BingStatement = BingStatement & "~adr." & address

rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If
MsgBox BingStatement
rs.Close
Set rs = Nothing
End Sub

[orange]

I looked at your post 1 and did some Googling. I haven't worked with Bing but have done some things with Google Maps.
You need to encode the blanks in the address string. Also, I don't think the sp= work??/ not sure?????

I took your first address and changed the blanks to %20, and it worked for me.
For encoding just have a small routine to replace " " with %20.
Try this in your browser.
http://www.bing.com/maps/?v=2&where1...,%20MI%2049002

Good luck.

[stephenaa5]

Hi, the bing piece I have figured out. Actually it works fine with spaces. The part I cannot get (which should be easy, except I'm a noobie) is the adding the next address to the variable "bingstatement" (using the loop correctly). There are 3 records in the table. If I remove the "& address" from the statement

BingStatement = BingStatement & "~adr." & address

The result is
http://bing.com/maps/default.aspx?sp=~adr.~adr~.adr

which is correct. But I need to add the ADDRESS in there....

[June7]

Don't see need for the address variable declaration.

BingStatement = BingStatement & "~adr." & rs!address

[orange]

Can you show the final URL you used that worked with spaces in the link?

Did you try the link I suggested?

[stephenaa5]

Hi. I'll give
BingStatement = BingStatement & "~adr." & rs!address

a shot in the morning, left for the day already.

Yes, of course I'll post the statement that works.

Thanks for the help.