Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Km to Miles Visa Versa (A2k)

    I thought I had been clever whilst creating a recovery form today but I was wrong Again.

    I had to come to a solution using different recovery trucks and their speedo's.
    Some trucks have a Kilometre speedo, some have a Miles speedo.
    We record a recovery trucks Out & In mileage (in that order), from here we can calculate how many miles on a particular journey.

    Here's the twist.
    If a vehicle has a Miles speedo, we enter this into a bound text, who's after update code reads:-

    Private Sub SpeedoOut_AfterUpdate()
    If Me.Speedo = "Kilometres" Then
    Me.SpeedoOut = Me.SpeedoOut * 0.6214
    Else
    End If
    End Sub


    The "Kilometres" text comes from a combo for selecting the vehicle for that particular journey showing [RecVehicle] [RecRegistration] [SpeedoType]
    After selection this populates a bound field [Speedo] showing the user

    =cmbRecTruckID.Column(2)

    Alls fine until we need to change the vehicle on an existing record.
    The existing code as above works fine but have added code to the after update of the combo:-

    Private Sub cmbRecTruckID_AfterUpdate()
    If Me.Speedo = "Kilometres" Then
    Me.SpeedoOut = Me.SpeedoOut * 0.6214
    Me.SpeedoIn = Me.SpeedoIn * 0.6214
    Else
    End If
    If Me.Speedo = "Miles" Then
    Me.SpeedoOut = Me.SpeedoOut * 1.609
    Me.SpeedoIn = Me.SpeedoIn * 1.609
    Else
    End If
    End Sub


    Using this combo code Scenario, heres what happens:-

    Miles = 100 (convert to Km) = 62
    Km = 62 (convert to miles) = 100
    Miles = 100 (convert to Km) = 62

    So far ok.
    if I change the vehicle and it has the same speedo as the previous vehicle, we get a calculation again where we shouldn't ie:-

    Miles = 100 (convert to miles) Miles = 161

    See what I mean ??

    P.S
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold"> I'm also practising the forum help, as if you hadn't guessed</span hi>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Km to Miles Visa Versa (A2k)

    Naive questions:
    <UL><LI>Why do you want to change the truck in an existing record? Shouldn't it be fixed after the initial choice?
    <LI>If you do need to change the truck in an existing record, shouldn't SpeedoIn and SpeedoOut be reset to Null (or 0, if Null poses problems)?[/list]A possible workaround would be to make the Speedo text box unbound (empty control source). Set its value in the After Update event of cmbRecTruckID and in the On Current event of the form.

    Private Sub cmbRecTruckID_AfterUpdate()
    Dim oldSpeedo As String
    ' Retrieve old value
    oldSpeedo = Me.Speedo
    ' Set new value
    Me.Speedo = Me.cmbRecTruckID.Column(2)
    ' Only go ahead if Speedo changed
    If Not (Me.Speedo = oldSpeedo)
    ' Multiply based on kilometres/miles setting
    If Me.Speedo = "Kilometres" Then
    Me.SpeedoOut = Me.SpeedoOut * 0.6214
    Me.SpeedoIn = Me.SpeedoIn * 0.6214
    Else
    Me.SpeedoOut = Me.SpeedoOut * 1.609
    Me.SpeedoIn = Me.SpeedoIn * 1.609
    End If
    End If
    End Sub

    In the On Current event of the form you only need to set the value of Speedo:

    Private Sub Form_Current()
    Me.Speedo = Me.cmbRecTruckID.Column(2)
    End Sub

    Another naive question:
    Isn't it time you Brits adopt the metric system completely?
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Km to Miles Visa Versa (A2k)

    Hans
    To answer your questions , but first thanks for the code, I'll try it in a while.

    Q1
    Why change the truck ?
    Trucks break down, have a habit of getting stuck in traffic etc etc.
    The whole Idea is to run a report each morning showing the schedule each particular truck should follow.
    Schedules can get changed throughout the day, so swapping trucks with different speedo types can be a pain.
    When running month end reports for miles covered etc, can get confusing if we don't convert the Km's to Miles.
    I suppose this could be done at report stage ??

    Q2
    Reset to Null or Zero.
    You're right, I overlooked this.

    Q3
    Why don't you Brits change to Metric completely ?

    Come now Hans, thats just stupid !!
    I ask you, how could I walk into a pub:??

    "Can I have 0.5683Ltr of Lager and a packet of crisps" ???

    You see, it just isn't right !!

    I can't walk into the corner shop:

    "Can I have 0.45Kg of sugar please" ??
    "No sir, we only have a pound, try the other shop down the road, It's only 0.8045 Km"

    See, it just isn't Cricket, the country would be in turmoil !!
    Some times I really wonder about these European countries.

    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15> <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Km to Miles Visa Versa (A2k)

    Hans
    When the form opens on a new record "Speedo" field is empty, so the combo after update runs a Invalid use of Null 94 Error on the lines:

    oldSpeedo = Me.Speedo

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Km to Miles Visa Versa (A2k)

    The easiest workaround is to declare Dim oldSpeedo As Variant instead of Dim oldSpeedo As String. A Variant can be null.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Km to Miles Visa Versa (A2k)

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •