FollowHyperlink with parameters question

[ptvGuy]

Okay, here's a fun Access question:

I'm trying to create a command button in my database that will take the donor address information from the current form and send it through the Internet to MapQuest to get a map and driving directions to the donor's business. However, I keep getting a "Compile Error: Object Required" message. I can't seem to figure out where I'm going wrong with this.

Any help would be appreciated. Thanks.

[VBA]Private Sub Mapper_Click()
On Error GoTo Err_Mapper_Click
Dim strAddress As String
Set strAddress = "http://www.mapquest.com/maps/map.adp?city=" & _
Str(Me![DonorCity]) & "%%26state=" & Str(Me![DonorState]) & _
"%%26address=" & Str(Me![DonorAddress1]) & "%%26zip=" & _
Str(Me![DonorZip]) & "%%26country=us%%26zoom=5"
Application.FollowHyperlink strAddress, , True
Exit_Mapper_Click:
Exit Sub

Err_Mapper_Click:
MsgBox Err & ": " & Err.Description
Resume Exit_Mapper_Click

End Sub[/VBA]

[WillR]

If strAddress id dimensioned as a String variable, I think the use of the Set command is what is causing the problem.... Set is used to determine "Objects"

How does this work ?

[vba]Private Sub Mapper_Click()
On Error Goto Err_Mapper_Click
Dim strAddress As String
strAddress = "http://www.mapquest.com/maps/map.adp?city=" & _
Str(Me![DonorCity]) & "%%26state=" & Str(Me![DonorState]) & _
"%%26address=" & Str(Me![DonorAddress1]) & "%%26zip=" & _
Str(Me![DonorZip]) & "%%26country=us%%26zoom=5"
Application.FollowHyperlink strAddress, , True
strAddress = vbnullstring
Exit_Mapper_Click:
Exit Sub

Err_Mapper_Click:
MsgBox Err & ": " & Err.Description
Resume Exit_Mapper_Click

End Sub [/vba]

Haven't delvedtoo much into the rest of the code as I not overly familiar with the Access model, but the string issue was the first thing to hit me...

[ptvGuy]

Thanks, I think you're right, but now I'm getting a "13: Type mismatch" error which doesn't make sense since all of the underlying fields are text fields. I even tried breaking it into seperate pieces which didn't help. I also just realized that both the DonorAddress and DonorCity fields either do or can contain spaces which means that I'm going to have to set something up in the code to find and replace all those spaces with plus signs to make the outgoing URL parameters valid. The really annoying part of all this is that it was just a little extra bit that I thought I'd throw into the database for the customer. It wasn't supposed to take this kind of time.

[VBA]
Private Sub Mapper_Click()
On Error GoTo Err_Mapper_Click

Dim strAddress As String
Dim donCity As String
Dim donState As String
Dim donAddress As String
Dim donZip As String

donCity = Str(Me![DonorCity])
donState = Str(Me![DonorState])
donAddress = Str(Me![DonorAddress1])
donZip = Str(Me![DonorZip])

strAddress = "http://www.mapquest.com/maps/map.adp?city=" & donCity & "%%26state=" & donState & "%%26address=" & donAddress & "%%26zip=" & donZip & "%%26country=us%%26zoom=5"

Application.FollowHyperlink strAddress, , True

strAddress = vbNullString

Exit_Mapper_Click:
Exit Sub

Err_Mapper_Click:
MsgBox Err & ": " & Err.Description
Resume Exit_Mapper_Click

End Sub
[/VBA]

[sgrant]

This is a sample of code that will remove spaces and replace them with "+".
[vba]
Dim sHoldIt As String

sHoldIt = "This is a test "

'Remove trailing spaces
sHoldIt = Trim(sHoldIt)

'Change imbedded spaces with plus signs
sHoldIt = Replace(sHoldIt, " ", "+", 1, , vbTextCompare)

'of course you can combine them like
'donAddress = Str(Replace(TRIM(Me![DonorAddress1]),, " ", "+", 1, , vbTextCompare))
[/vba]

[Anne Troy]

Hey, Scott! Cool. Would you mind adding that code to our knowledgebase?

[ptvGuy]

This is a sample of code that will remove spaces and replace them with "+".

Okay, that was incredibly handy. Thanks a lot. Now I have a working command button (called "mapper") in my database that lets users instantly get a map from MapQuest without having to reenter the address information once they're at that site. That little piece of code could be useful for a lot of different situations. I'm hoping it becomes part of the knowledge base too.

[flavo]

Not usre if you have a Access97 function for replace, there are plenty around, but here's one i prepaired earlier..

see below

Dave

[flavo]

[VBA] Public Function Replace97(ByVal sString, sFind, sReplace) As String

Dim iPos As Long
Replace97 = sString
If Len(sString) = 0 Then
'zero length string
Exit Function
Else
iPos = 1
Do
iPos = InStr(iPos, sString, sFind, vbBinaryCompare)
If iPos > 0 Then
Replace97 = Left(Replace97, iPos - 1) & sReplace & _
Right(Replace97, Len(Replace97) - iPos)
iPos = iPos + 1
End If
Loop While iPos > 0
End If
End Function

[/VBA]

[Pat Hartman]

I changed the function to be more generic so I didn't have to specify form field names but I couldn't get the posted string to work. So, I built a slightly different string.
[VBA]Public Function Mapper_Click(strCity As Variant, strState As Variant, strAddress As Variant, strZip As Variant) As Variant
On Error GoTo Err_Mapper_Click

Dim strFullAddress As Variant
Dim strMapquest As Variant
strFullAddress = strAddress & "&city=" & strCity & "&state=" & strState & "&zipcode=" & strZip
strMapquest = "http://www.mapquest.com/maps/map.adp...try=US&address=" & strFullAddress & "&historyid=&submit=Get Map"

'Remove trailing spaces
strMapquest = Trim(strMapquest)

'Change imbedded spaces with plus signs
strMapquest = Replace(strMapquest, " ", "+", 1, , vbTextCompare)

Application.FollowHyperlink strMapquest, , True

strMapquest = vbNullString

Exit_Mapper_Click:
Exit Function

Err_Mapper_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Mapper_Click

End Function
[/VBA]