Can this be done?

[DaaAgent]

Use text from a Screen dump to Autofill specific fields

At the moment we book our cruise clients using a agents website we have then to input key information into the office database for accounts and date tracking etc

Current Input method
1. Take a copy of the screen text and paste it into a memo field in the clients record
2. Manual input the fields listed below

[BookedDateTime]
[BookingRef]
[CruiseLine]
[Shipname]
[SailingDate]
[CruiseDuartion]
[GradeBooked]
[GradePriced]
[CabinNo]
[Location]
[Dining]

All the above information is in the screendump below. I have placed “Our” field names beside the present system fields

(Dummy Data but formatted as per paste )
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Printed from Amadeus Cruise on 31 Jan 2007 13:30[BookedDateTime] Local time
Booking Summary

Confirmation Number : 9265255[BookingRef]
Status : CNF
Extend Due Date : 6FEB2007
Record Locator : 46V3TK


Sailing Information

Cruise Line : ROYAL CARIBBEAN INTERNATIONAL[CruiseLine]
Ship : VOYAGER OF THE SEAS[Shipname]
Date : 15 Sep 2007[SailingDate]
From / To : BARCELONA, ES / BARCELONA, ES
Cruise Length : 7 nights[SailingDate]

Cabin

Category : N[GradeBooked] Priced as : Q[GradePriced]Cabin : 7368 [CabinNo] Location : [Location]
Bedding : 2 LOWER TWIN CONV TO DBLE
Dining : See Guests [Dining]

Guest Details



1. Mr Adam Test


Details

Transport City : GLASGOW (GLA), GB
Mode of Transport : AIR
Age : 68 years
Fare : BRKD05
Insurance : NONE
Past Passenger : 3334617817

Dinings

Preferred Seating : FIRST - AVL
Packages

Cruise Package : VY 7 NIGHT MEDITERRANEAN CRUISE
Special Services

Service Char
Preferred Language : English


2. Mrs Eve Test

Details

Transport City : GLASGOW (GLA), GB
Mode of Transport : AIR
Age : 67 years
Fare : BRKD05
Insurance : NONE
Past Passenger : 3339242765

Dinings

Preferred Seating : FIRST - AVL
Packages

Cruise Package : VY 7 NIGHT MEDITERRANEAN CRUISE
Special Services

Service Char
Preferred Language : English




© 1999-2006 Amadeus s.a.s
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Any ideas

Cheers

 

[Bat17]

This was fun

Dim aInput() As String
Dim strLine As String
Dim strVar() As String
Dim j As Integer
aInput = Split(Me.Mem1, vbCrLf)
For j = 0 To UBound(aInput) - 1
    If Len(Nz(aInput(j))) > 1 Then
        strLine = aInput(j)
        If InStr(strLine, "Printed from") Then
            Me.BookedDateTime = CDate(Mid(strLine, InStr(strLine, " on ") + 4, 17))
        ElseIf InStr(strLine, "Confirmation Number :") Then
            strVar = Split(strLine, ":")
            Me.BookingRef = strVar(1)
        ElseIf InStr(strLine, "Cruise Line :") Then
            strVar = Split(strLine, ":")
            Me.CruiseLine = strVar(1)
        ElseIf InStr(strLine, "Ship :") Then
            strVar = Split(strLine, ":")
            Me.Shipname = strVar(1)
        ElseIf InStr(strLine, "Date :") Then
            strVar = Split(strLine, ":")
            If Trim(strVar(0)) = "date" Then
                Me.SailingDate = CDate(Trim(strVar(1)))
            End If
        ElseIf InStr(strLine, "Cruise Length :") Then
            strVar = Split(strLine, ":")
            Me.CruiseDuartion = strVar(1)
        ElseIf InStr(strLine, "Category :") Then
            strVar = Split(strLine, ":")
            Me.GradeBooked = Trim(Left(Trim(strVar(1)), InStr(Trim(strVar(1)), " ")))
            Me.GradePriced = Trim(Left(Trim(strVar(2)), InStr(Trim(strVar(2)), " ")))
            Me.CabinNo = Trim(Left(Trim(strVar(3)), InStr(Trim(strVar(3)), " ")))
            Me.Location = Trim(Left(Trim(strVar(4)), InStr(Trim(strVar(4)), " ")))
        ElseIf InStr(strLine, "Dining :") Then
            strVar = Split(strLine, ":")
            Me.Dining = strVar(1)
        End If
    End If
Next j

No error checking or anything!!
add the code to a button and fire it off after adding the data to the memo box

Peter

 

[DaaAgent]

Wooooooooooooooooooowww

bat17 I think this is the second time you have bailed me out

“This was fun” Sarcasm maybe ?

Thanks a million for writing the code…could you break it down and explain the steps so I can learn

Had 1 or 2 wee hiccups...Just to be picky
1. The code fails at Grade Priced (see Attached Error screens)
2. What do I change to remove the space that goes before the fields after the code has run
3. [Cruiseduration] could it just be the figure without the nts The maximum duration should be no greater than 99

Again thanks a million

 

[Bat17]

I will try to have a closer look in the morning, but for now 1) looks like it should be a number field and there was no data so it returned an empty string. we can either add an 'if' to skip the field if blank or coerce it to a number.
2) wrap lines in Trim() to clear spaces at start and end of line.
3) can probably just wrap the line in Val() to pull out the number.

Was not 'Sarcasm' this sort of coding is fun compared to constructing queries or trying to fit dozens of text fields into a report!

Peter

looks like I was wrong on 1), will check it out in morning

 

[Bat17]

here is some tweaked code, I have added some comments but if you need more specific help understanding how it works then just shout.

I think the problem with Grade Priced is going to be a variation in the format of the line
Category : N[GradeBooked] Priced as : Q[GradePriced]Cabin : 7368 [CabinNo] Location : [Location]

can you post the one that failed please.

Dim aInput() As String
Dim strLine As String
Dim strVar() As String
Dim j As Integer
'This line takes your memo field and splits it into individual lines in an array
aInput = Split(Me.Mem1, vbCrLf)
' Step through the Array 1 line at a time
For j = 0 To UBound(aInput) - 1
'Make  sure there is something in the line before processing it
    If Len(Nz(aInput(j))) > 1 Then
        strLine = aInput(j)
        ' now search for key words to locate data.
        If InStr(strLine, "Printed from") Then
            Me.BookedDateTime = CDate(Mid(strLine, InStr(strLine, " on ") + 4, 17))
        ElseIf InStr(strLine, "Confirmation Number :") Then
            strVar = Split(strLine, ":")
            Me.BookingRef = Trim(strVar(1))
        ElseIf InStr(strLine, "Cruise Line :") Then
            strVar = Split(strLine, ":")
            Me.CruiseLine = Trim(strVar(1))
        ElseIf InStr(strLine, "Ship :") Then
            strVar = Split(strLine, ":")
            Me.Shipname = Trim(strVar(1))
        ElseIf InStr(strLine, "Date :") Then
            strVar = Split(strLine, ":")
            'double check line as two line match "Date :"
            If Trim(strVar(0)) = "date" Then
                Me.SailingDate = CDate(Trim(strVar(1)))
            End If
        ElseIf InStr(strLine, "Cruise Length :") Then
            strVar = Split(strLine, ":")
            Me.CruiseDuartion = Val(strVar(1))
        ElseIf InStr(strLine, "Category :") Then
        ' more than 1 set of data in this line so need to split it into its own array
            strVar = Split(strLine, ":")
            Me.GradeBooked = Trim(Left(Trim(strVar(1)), InStr(Trim(strVar(1)), " ")))
            Me.GradePriced = Trim(Left(Trim(strVar(2)), InStr(Trim(strVar(2)), " ")))
            Me.CabinNo = Trim(Left(Trim(strVar(3)), InStr(Trim(strVar(3)), " ")))
            Me.Location = Trim(Left(Trim(strVar(4)), InStr(Trim(strVar(4)), " ")))
        ElseIf InStr(strLine, "Dining :") Then
            strVar = Split(strLine, ":")
            Me.Dining = Trim(strVar(1))
        End If
    End If
Next j

Peter

 

[DaaAgent]

Hi Peter

Thanks for your quick reply’s unfortunately I have not been as fast …got waylaid... landed a new business account also little matter of a 1years old birthday

Sorry I picked up the wrong vibe Re my Sarcasm comment…. I am very very very glad that I have given you something to get your Teeth into.. and Peter trust me I have another couple of beauties up my sleeve

Still haven’t had a chance to go through the help pages and find the functions.. Hopefully lunchtime tomorrow

At lunch today i pulled some tables forms & reports together to let you see the bigger picture also I have add in pdf copy’s of the data that comes from the booking site “the names and faces have been changed to …”

Reports uploaded
1. Confirmation
2. Costing
3. Itinerary

The cruise booking system is designed not to let you copy from the inputscreen directly but there is a print function on every screen that loads up a review type report displaying all the formatted fields with a selector for Print or Copy.. copy is then selected followed by pasting it into our DB

I have added 3 records from multiple cruise lines pasted directly from the booking systems reports . The first 1 is supported by the pdf reports to let you see a possible problem when it comes to splitting the line that has the CabinNo field init… Most cruise lines cabin No go up to 4 digits but with all the bigger ships coming out some are now 5 see last record for example

Hope you are still finding this post fun

Cheers

 

[Bat17]

OK I will let you off for being tardy! 1st birthdays ARE important!

I have tweaked it to handle the versions you have given and tried to make it a little more robust.

Private Sub AutoFillCRUISEBT_Click()
Dim aInput() As String
Dim strLine As String
Dim strVar() As String
Dim j As Integer
Dim strTemp As String

'clear fields, just in case :)
Me.BookingRef = Null
Me.BookedDateTime = Null
Me.CruiseLine = Null
Me.Shipname = Null
Me.SailingDate = Null
Me.CruiseDuartion = Null
Me.Dining = Null
Me.GradeBooked = Null
Me.GradePriced = Null
Me.CabinNo = Null
Me.Location = Null
'This line takes your memo field and splits it into individual lines in an array
aInput = Split(Me.Mem1, vbCrLf)
' Step through the Array 1 line at a time
For j = 0 To UBound(aInput) - 1
'Make  sure there is something in the line before processing it
    If Len(Nz(aInput(j))) > 1 Then
        strLine = aInput(j)
        ' now search for key words to locate data.
        If InStr(strLine, "Printed from") Then
            Me.BookedDateTime = CDate(Mid(strLine, InStr(strLine, " on ") + 4, 17))
        ElseIf InStr(strLine, "Confirmation Number :") Then
            strVar = Split(strLine, ":")
            strTemp = Trim(strVar(1))
            If Not strTemp = "" Then Me.BookingRef = strTemp
        ElseIf InStr(strLine, "Cruise Line :") Then
            strVar = Split(strLine, ":")
            strTemp = Trim(strVar(1))
            If Not strTemp = "" Then Me.CruiseLine = Trim(strVar(1))
        ElseIf InStr(strLine, "Ship :") Then
            strVar = Split(strLine, ":")
            strTemp = Trim(strVar(1))
            If Not strTemp = "" Then Me.Shipname = Trim(strVar(1))
        ElseIf InStr(strLine, "Date :") Then
            strVar = Split(strLine, ":")
            'double check line as two line match "Date :"
            If Trim(strVar(0)) = "date" Then
                Me.SailingDate = CDate(Trim(strVar(1)))
            End If
        ElseIf InStr(strLine, "Cruise Length :") Then
            strVar = Split(strLine, ":")
            strTemp = Trim(strVar(1))
            If Not strTemp = "" Then Me.CruiseDuartion = Val(strVar(1))
        ElseIf InStr(strLine, "Dining :") Then
            strVar = Split(strLine, ":")
            strTemp = Trim(strVar(1))
            If Not strTemp = "" Then Me.Dining = Trim(strVar(1))
        End If
        ' seperate if's for lines that may have more than one data element
        If InStr(strLine, "Category :") Then
            ' padd strTemp to avoid problem with empty string
            strTemp = Trim(Mid(strLine, InStr(strLine, "Category :") + 10)) & " "
            strTemp = Trim(Left(strTemp, InStr(strTemp, " ") - 1))
            If Not strTemp = "" Then Me.GradeBooked = strTemp
        End If
        If InStr(strLine, "Priced as :") Then
            strTemp = Trim(Mid(strLine, InStr(strLine, "Priced as :") + 11)) & " "
            strTemp = Trim(Left(strTemp, InStr(strTemp, " ") - 1))
            If Not strTemp = "" Then Me.GradePriced = strTemp
        End If
        If InStr(strLine, "Cabin :") Then
            strTemp = Replace(strLine, "Location :", " ")
            strTemp = Trim(Mid(strTemp, InStr(strTemp, "Cabin :") + 7)) & " "
            strTemp = Trim(Left(strTemp, InStr(strTemp, " ") - 1))
            If Not strTemp = "" Then Me.CabinNo = strTemp
        End If
        If InStr(strLine, "Location :") Then
            strTemp = Trim(Mid(strLine, InStr(strLine, "Location :") + 10)) & " "
            strTemp = Trim(Left(strTemp, InStr(strTemp, " ") - 1))
            If Not strTemp = "" Then Me.Location = Trim(strTemp)
        End If
    End If
Next j
Me.Refresh ' just to make sure the data on screen updates
End Sub

Peter

 

[DaaAgent]

Morning Peter

Sorry about the time taken to reply, I have just been swamped at work last minute 203 delegates to a tradefare in Frankfurt next month hasn’t helped and somebody’s got Teethes coming through again

Thanks for the revised code works a dream “bullet proof” hasn’t failed yet 23 pastes later and no errors messages

Went through the Access & VB help pages last night working backwards with your code. Your notes in the code helped me a lot to understand what was going on....thanks to your help I have now a version of the code for when we copy in Insurance details

I couldn’t work/find out the meaning for the lines referring to the character j
Dim j As Integer & strLine = aInput(j) etc ??

Could you advise?

Cheers again

 

[Bat17]

J is just a counter

aInput() is an array


For j = 0 To UBound(aInput) - 1
......
next j

This bit means that j will step through the values 0 to however many itmes there are in the array. (arrays are 0 based which is why it is 0 to the upperlimit -1)

so if j = 10 then aInput(j) is equivalent to aInput(10) ie look at the 11th line in the text file.

HTH

Peter

 

[DaaAgent]

Me again !!!

Reading your explanation I now understand my insurance code was a fluke as thankfully its just one field per line so if there were 2 fields on 1 line I would never of got it to work

Thanks again for all your Help
Stewart

p.s
Remember I spoke about having some beauties up my sleeve for you to get your teeth into… they will be coming soon but if you’re a wee bit hungry could you look at an old post sitting that nobody replied to

http://www.access-programmers.co.uk/forums/showthread.php?t=112920

Have attached a screen shot to let you see the form asis

 

[detrie]

You said that the data was from a "screendump". How is the screendump done?

Detrie