# Thread: Km to Miles Visa Versa (A2k)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•