Full Version: Display tracking# on website
UtterAccess Forums > Microsoft Access > Access Forms
linker33
Not sure if this is the right forum. What I have is a form that looks up shipping information, such as addresses, tracking #'s ect... Most of my shipments are done using FedEx, and what I'd like to do is when I type/select the tracking #, have FedEx.com open and the tracking # that I selected be displayed in the tracking # box, so I don't have to retype it. Is this even possible? I'm sure there's probley a way to do it, but most likely it's way out of my league. Anyway, just thought it would be kinda cool if I could.
bty, when you open FedEx.com, the cursor automaticaly is displayed in the tracking # text box, so I think all I would need to do is have some sort of copy when I select the tracking # and then delay the paste until after the website opens.
Edited by: linker33 on Wed Nov 3 10:55:21 EST 2004.
Fletch
Yes, that can be done, it's just a question of how much coding you're willing to do and how complex you want to get. What you might do is have a button that simply follows a hyperlink. If you have a button called cmdTrackIt that when clicked will open the tracking web page and the tracking number is stored in the text box txtTrackingNumber, then you might have this code for the Click event of the Track It command button:
!--c1-->
CODE
Private Sub cmdTrackIt_Click()
Dim strURL as String
strURL = "http://fedex.com/Tracking?ascend_header=1&clienttype=dotcom&cntry_code=us" & _
         &language=english&tracknumbers="
strURL = strURL & txtTrackingNumber.Value
cmdTrackIt.Hyperlink = strURL
cmdTrackIt.Hyperlink.Follow
End Sub
Obviously, I just took a real quick look at FedEx's web site, so more than likely you'll need to refine the URL to appropriately track, but hopefully it points you in the right direction.
Of course, you can get more complex and imbed an Internet Explorer control in your Access form, have it look up the page, and then you can parse the results and display them in any fashion you want on your form, though if you're not at least somewhat experienced at coding VBA, that task might seem a little daunting, but it is theoretically possible. If you look at this post I demonstrated how to do something similar. In it, driving directions are fetched from Yahoo maps and returned to the form for display. It's by no means perfect, but maybe it'll spur some ideas for you. If you look at it, the download you want is in the last post in that thread.
HTH frown.gif
ScottGem
I actually did this in one of my apps: Here's the code I used:
!--c1-->
CODE
Private Sub Track_Click()
On Error GoTo Err_Track_Click
Dim ctl As CommandButton
Dim strURL1 As String
Dim strURL2 As String
Dim strAddress As String
Select Case Me.Carrier
    Case "Airborne Express"
'        strAddress = "http://track.airborne.com/atrknav.asp"
'        Application.FollowHyperlink strAddress, , True, , Me.Tracking_, msoMethodGet
         MsgBox "Tracking is not available for Airborne at this time!", vbInformation, "Warning"
         GoTo Exit_Track_Click
        
    Case "DHL"
        strURL1 = "http://www.dhl-usa.com/cgi-bin/tracking.pl?AWB="
        strURL2 = "&LAN=ENG&TID=US_ENG&FIRST_DB=US"
        strAddress = strURL1 & Me.Tracking_ & strURL2
        Application.FollowHyperlink strAddress, , True
        
    Case "Fedex"
        strURL1 = "http://www.fedex.com/cgi-bin/tracking?action=track&language=english&cntry_code=us&initial=x&tracknumbers="
        strAddress = strURL1 & Me.Tracking_
        Application.FollowHyperlink strAddress, , True
    Case "UPS"
        strURL1 = "http://wwwapps.ups.com/etracking/tracking.cgi?tracknums_displayed=5&error_carried=yes&TypeOfInquiryNumber=T&HTMLVersion=4.0&sort_by=status&InquiryNumber1=1Z"
        strURL2 = "&InquiryNumber2=&InquiryNumber3=&InquiryNumber4=&InquiryNumber5=&track=Track"
        strAddress = strURL1 & Me.Tracking_ & strURL2
        Application.FollowHyperlink strAddress, , True
        
    Case Else
         MsgBox "Tracking is not available for " & Me.Carrier & " at this time!", vbInformation, "Warning"
         GoTo Exit_Track_Click
  
End Select
'    GetUserAddress = True
Exit_Track_Click:
    Exit Sub
Err_Track_Click:
    MsgBox Err.Description
    Resume Exit_Track_Click
    
End Sub

This code was attached to a button. Carrier is a control on the form where the user selected a carrier and Tracking the control where the tracking number was entered.
HTH
timbailey
The FedEx tracking site uses XPath queries. To query it, you just need to append your tracking number onto the URL below:
http://www.fedex.com/Tracking?cntry_code=u...p;tracknumbers=
If you shell out to your favorite internet browser in the OnDoubleClick event of your TrackingNumber control with the tracking number tacked onto the string above, you will go directly to the info for that tracking number.
You can track multiple numbers by separating them with %0D%
linker33
Thanks for the responses. Babrandt, that was very interesting, what you did w/ the Yahoo driving directions. I'll have to absorb that in detail. Thank you for the code Scottgem. I basicly understand the code and see how it points to the URL, but I don't see how it pastes the tracking # into the text box on the website. It's got to be right here, but I'm not seeing it.
CODE
strAddress = strURL1 & Me.Tracking_        
Application.FollowHyperlink strAddress, , True

Babrandt, looks like you did it similarly w/
CODE
strURL = strURL & txtTrackingNumber.Value
cmdTrackIt.Hyperlink = strURL
cmdTrackIt.Hyperlink.Follow

Would you be able to explain a little. I'm still learning VBA, so I like to understand as much as possible. Thanks again.
Fletch
Sure. . .it's not pasting the tracking number into the box on the web site. Basically, when you type a tracking number into the web site and click OK or whatever the button says, it then generates a URL that includes the tracking number in it and goes to that URL. What we're doing is bypassing that first step. Since we know the tracking number and by looking at the URLs generated by FedEx on test tracking numbers, we know what to expect the ultimate URL to be. So, our code generates the final URL so there's no typing or pasting to be done. Does that make sense? frown.gif
linker33
Timbailey, I like that, but how would I go about doing it? I discovered that I forgot how to bring up a URL from an Access form. And I left my Access for Dummies book at home. :( Once I do that how do I append the tracking # from a text box on a form to the end of the URL. Sorry if my questions are a bit basic.
Fletch
I think if you look at the code ScottGem and I provided, you'll see how to attach the tracking number to the URL. It's just basic string concatenation.
So, if you write
strURL = "someurl.com/" & Me.Tracking & "/something_else"
and Tracking is a field in your database which for the current record is 123, the string strlURL will be equivalent to
someurl.com/123/something else
The & operator is used for string concatenation.
So, the idea is that you generate the ENTIRE URL before opening a browser window with that URL. Then, you use Application.FollowHyperlink or something along those lines to actually open the browser to that URL.
HTH/makes sense! frown.gif
linker33
thanks, that helps alot.
ScottGem
Glad to assist
Fletch
Also happy to help! Let us know if you need any further help! frown.gif
linker33
Well, I got as far as embeding an IE control on my form and having in come up w/ the search results, but what is meant by parse, and how would I go about doing this?
Fletch
I can't think of a good, succint way of defining parse at the moment, so let me just give you an example. When you load a web page, it's source probably looks something like
<HTML><TITLE>This is a title</TITLE><BODY>Your package was sent on 11/6/2004. It's been to this place and that place. We think it'll arrive at the destination on 11/10/2004.</BODY></HTML>
bviously, the real HTML is a lot more complex. Anyway, if your goal is to determine when the package is set to arrive, then if you parsed the data for the arrival date, you'd extract 11/10/2004 out of all the other text. It probably does take some coding experience. If you had an XML file, it can be a lot easier, or at least you're a lot less likely to run into weird circumstances. For example, in my above example, say you set up some code to look for the text "We think it'll arrive at the destination on " and then grab whatever follows, and you get it working. Then, all of a sudden you get a page that returns "Sorry, you entered an invalid tracking number." What's your code going to do now?
It's far from perfect, but if you take a look at this thread there is a rough example of how you might do it. In that thread, I showed someone first how to retrieve driving directions from Yahoo maps. I parsed the data to ignore all the random stuff in there such as advertisements, etc., to just get the meat of it all--the driving distance, the driving time, the actual directions, etc. Later in the thread, I helped someone else modify it a little to try checking a bunch of addresses and comparing the distances to determine the 3 nearest locations. Maybe it'll help a little. frown.gif (as a side note, there was a little bug in my first download that I made available, so make sure you scroll down to after vibajajo points out a bug, and then I respond with another download).
linker33
Ok, what I'm trying to do here is when a button is clicked on a Form called frmSearchResults, another form is opened called frmTrack. On frmTrack there is a web browser component that will display the FedEx tracking results for the tracking number located in the textbox called Tracking on frmSearchResults. I'm getting errors when I run this code here. If I could get frmTrack to display a textbox w/ the tracking number from the selected record on frmSearchResults, I could set strTrackDisplay to open to that, but I'm not sure how to point to a textbox on another form.
CODE
Private Sub Form_Load()
Dim strTrackDisplay As String
Dim strUrl As String
strUrl = "http://www.fedex.com/cgi-bin/tracking?  action=track&language=english&cntry_code=us&initial=x&tracknumbers="
strTrackDisplay = strUrl & Form.frmSearchResults.Tracking
Form.frmSearchResults.Tracking.Navigate frmSearchResults.Tracking
End Sub
Fletch
If you want to reference another form, use the Forms collection as in one of these three methods:
orms.frmSearchResults.Tracking
Forms!frmSearchResults.Tracking
Forms("frmSearchResults").Tracking
Also, just taking a quick look at the code, are you sure the URL is correct? Specfically, there are a couple of spaces between "tracking?" and "action=track." I don't believe spaces are allowed in a URL (if you need a space, there is an escape character for it).
HTH frown.gif
linker33
Ok, I sorta understand how this is pulling the directions. It's pulling them out of the html source code, right? I was hoping mabey you could clarify the process here. I see the For, If, Else statement that defines where to start pulling from, and where to stop.
- If this is pulling from the html source. How is it directed to look at the source of the page?
- When it defines where to start/stop pulling the info from, how does it know to exclude other page content inbetween such as tags.?
I'm trying to adapt this code to parse tracking information off of FedEx or UPS. In the FedEx html the tracking info is spread over quite a bit of code, w/ lots of tags inbetween, not all together like Yahoo's map direction. Anyway those are my 2 major questions right now. I'm sure I'll have more before I figure this out. frown.gif Thanks for any help.
CODE
Public Function GetDirections(strHTML As String) As String
Dim lines() As String, ctr As Long, blnDirections As Boolean
SplitStringIntoLines strHTML, lines
blnDirections = False
For ctr = LBound(lines) To UBound(lines)
    If lines(ctr) Like "1.Start at*" Then
        blnDirections = True
    ElseIf lines(ctr) Like "When using any driving directions or map*" Then
        blnDirections = False
    End If
    If blnDirections Then
        GetDirections = GetDirections & lines(ctr) & vbNewLine
    End If
Next ctr
GetDirections = Left(GetDirections, Len(GetDirections) - Len(vbNewLine))
End Function
linker33
Sorry I didn't clarify where this code came from. If this is babrandt reading this, you probley already know. But anyway it was from the map/direction finder application in this thread.
linker33
Outta time to edit my post. Oh well, thought I'd just put the attachment here instead of a link to the thread.
Fletch
Okay, so you need to look at the source HTML code for the site you want to parse to figure out the best plan of attack. If you can get it in XML, it'll probably be much easier, but that may not be possible. In fact, it looks like you might have to have an account with FedEx to do that (see this site which might be helpful if you have an account with them and want to use their API to interact directly with their site).
For the code I showed you, basically what I first did was parsed all the HTML into lines and stored each line in an array (called lines in the code). Additionally, the web object being used can give you all the HTML or just the actual text, less the HTML tags. In this instance, I'm using just the text. I happen to know, at least based on testing, that all directions start when I see a line that starts with "1.Start at" and ends with a line that starts with "When using any driving directions or map". So, I just go through each line and collect all data between those start and end points.
For you, such a strategy may not work. You may also need to actually parse through the HTML tags. Bottom line, it will take a fair amount of customization on your part if you really want to make it work.
In response to your quest If you look at the code that parses the results, you'll see a function that is called that splits all the text from the source into lines and stores the results in the string array called lines. Thus, this array has the text (but not the HTML tags) of the web page that was loaded and is used when calling the function. Specifically, webDirections.Document.Body.OuterHTML results in the actual page source with tags, and webDirections.Document.Body.Innertext results in just the text of the page source, less the tags. The key is that tags are not included in the information that is sent to this function. Based on testing, it seems that all information in between the designated start and end points is information that I want.
Now, actually, looking at what you have, you may not have downloaded the latest code. It turns out that although that code almost works, it didn't say whether to turn left or right on to each road. Yahoo instead of putting in text to say turn left or right included a small graphic image to indicate a Left or Right turn. So, in the long run I had to end up parsing the whole HTML including the tags, to be able to determine left or right. The function eventually ended up being this where strLines was now an array of lines like before, except that it included all HTML tags, not just the text.
CODE
Public Function GetDirections(strLines() As String) As String
Dim ctr As Long, blnDirections As Boolean, strLine As String, ptr As Long
Dim blnCopyTextFlag As Boolean, blnDelayOneCharacter As Boolean
blnDirections = False
For ctr = LBound(strLines) To UBound(strLines)
    If blnDirections Then
        strLine = ""
        For ptr = 1 To Len(strLines(ctr))
            If Mid(strLines(ctr), ptr, 1) = "<" Then
                blnCopyTextFlag = False
            ElseIf Mid(strLines(ctr), ptr, 1) = ">" Then
                blnCopyTextFlag = True
                blnDelayOneCharacter = True
            End If
            If blnCopyTextFlag = True Then
                If blnDelayOneCharacter Then
                    blnDelayOneCharacter = False
                Else
                    strLine = strLine & Mid(strLines(ctr), ptr, 1)
                End If
            End If
        Next ptr
        If (strLine Like "Turn*" Or strLine Like "Bear*") And _
           InStr(1, strLines(ctr), "alt=Left") > 0 Then
            strLine = Left(strLine, 4) & " Left" & Mid(strLine, 6)
        ElseIf (strLine Like "Turn*" Or strLine Like "Bear*") And _
               InStr(1, strLines(ctr), "alt=Right") > 0 Then
            strLine = Left(strLine, 4) & " Right" & Mid(strLine, 6)
        End If
        GetDirections = GetDirections & Replace(strLine, "&lt;", "") & vbNewLine
    End If
    If strLines(ctr) Like "<TD>?.*" Or strLines(ctr) Like "<TD>??.*" Or strLines(ctr) Like "<TD>???.*" Then
        blnDirections = True
    Else
        blnDirections = False
    End If
Next ctr
If Len(GetDirections) > 0 Then
    GetDirections = Left(GetDirections, Len(GetDirections) - Len(vbNewLine))
End If
End Function
So, you may want to ensure you download the latest version from that thread. At least, that'll show you how I ended up handling all the HTML tags.
I'm not sure if you've ever dealt with automata theory or even heard of it, but it all boils down to a finite state machine. A very simplistic way to think about it is this. You have a start state, an end state, an ignore state, and a read state. As you go through the HTML data, you are in a start state. Eventually, you find some data you want to read, so move to the read state. In the read state, you are copying information from the HTML. Then, you know that you get to a point where there is a bunch of junk, so you move to the ignore state, where you ignore all the HTML. Upon certain conditions, you may switch back and forth between these states multiple times. Eventually, you reach some end state at which you know there is no further data you want and so you stop parsing. By having boolean variables set up, you determine which state you're in, which is basically what that function does.
HTH frown.gif
linker33
Thanks so much for your indepth response. I did get the code to work w/ FedEx tracking results by just modifying the '1.Start at', and 'When using any driving...', to whatever I wanted it to start and stop displaying in the Msgbox. This code seems to work fine, as there are no images used to display tracking info. Thanks again for your help.
Fletch
Sure, glad you got it working. Just as a warning, if FedEx ever modifies the way they display their results, it could affect the ability of the code to correctly parse the information, so you just have to be vigilant looking for changes. You might also want to check special cases such as invalid tracking numbers, packages that were delivered a long time ago, or are listed as undeliverable, etc., to ensure it can handle all aspects.
nyway, glad I could help. frown.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.