Covert Km,miles in one column

[Mansoor Ahmad]

Dear All

I have got a bit complicated problem. It may not be complicated for you experts. In my database, we enter data from different customers. One column is for car mileage. As there are various customers, each customer sends data for their parts and they are using Km as vehicle mileage and some use Miles and others use Miles (x 1000).

As I have one column for mileage, I want all the mileage figures to be entered in one unit say Km. There are other users as well who would be using the same database. I want some user-friendly automatic way to convert all these figures into one unit and store that figure into the main table in one column, on which various forms and reports are based.

If any body can think of any solution, please let me know as early as possible. Thank you.

 

[Mile-O]

On your form create an option group with the three options and a textbox. Let them select which of the three options should be applied and convert he information given (if necessary) and store it in your table.

 

[Mansoor Ahmad]

Thanks

You are so quick. I will try it out and get back to you.

Thanks.

 

[Mile-O]

Re: Thanks

You are so quick.

Shh! I don't want all the ladies to know!

 

[Mansoor Ahmad]

Option group

Don't you worry, no one would know.

Back to my question. Thank you for your reply again. Problem is, I have never used option group before. Is it possible if you can explain it a little bit more, like if I put three options on my form as Km, Miles, Miles (x 1000) in a frame named Frame1 and one text box.

How this text box will be linked to options group?
Whose data source would be linked to the underlying table?
How would I put mathematical formulas in for each options. Like if the data comes in miles and I want it to be converted into Km and store in underlying table, how can I do that?

It all may sound like stupid, asking for such basic questions but I am stuck here and I need to solve it today at the most. Its a deadline today for me.

Looking forward to your reply. Thanks

 

[Mile-O]

The textbox would be bound to your table with the actual distance in km.

On the AfterUpdate() event of your option group you would use the SELECT CASE statement

i.e.

Select Case fraOptions
   Case Is = 1 ' km
      ' No need to convert
   Case Is = 2 ' miles
      txtYourTextBox = do the conversion calculation
   Case Is = 3 ' miles x100
      txtYourTextBox = do the conversion calculation
End Select

 

[Mansoor Ahmad]

A bit more help please

Thank your for your quick reply again. I feel I am nearly there.

Is this how the code should be in Afterupdate() event procedure of Frame1?

Private Sub Frame4_AfterUpdate()
Select Case fraOptions
Case Is = 1, km
Case Is = 2, miles
Text2 = *1.609
Case Is = 3, miles_(x_100)
Text2= * 1000
End Select

End Sub

But it doesn't accept '*' and says a complie error. What I want to do is to multiply by 1.609 to the value in Text2, text box to convert it to miles. Similarly in case 3, I want it to be multiplied by 1000 to convert it into miles.

With a little more of your help I think I can do it today.

Thank you

 

[Mile-O]

Private Sub Frame4_AfterUpdate() 
   Select Case Frame4 
      Case Is = 2
         Text2 = Text2 * 1.609 
      Case Is = 3 
         Text2 = Text2 * 1000 
   End Select 
End Sub

 

[Mansoor Ahmad]

I am shocked

I don't beleive how quickly I got reply. I had not actually come out of that website and there you were. I really appreciate it.

Now I will go back and try it out and let you know later.

 

[Mansoor Ahmad]

Not far at all now.

Thanks again

I have done it exactly as you said. It has accepted the * sign.

But still if I write 100 in Text box and click on option for miles, it still displays 100 in underlying table, same is with miles (x 1000) option.

Do I need to write anything in Control Source properly of Fram4? Or there is something else I am missing?

Thanks again.

 

[Mile-O]

Have a look at this:

 

[Mansoor Ahmad]

Frustration

Thank you again. But on my side it is becomming a bit frustrating now. I can't open this zip file.

I am loged on to my databse as an Admin user but when I open your zip file it says

The current user account doesn't have permission to convert or enable this database.

I can't understand why. Is there a way? Sorry to be a pain.

Thanks

 

[Mile-O]

Here's all the relevant code that I put in that database then.

Private Sub fraDistance_AfterUpdate()
    
   Select Case fraDistance
      Case Is = 2
         txtMileage = txtMileage * 1.609
      Case Is = 3
         txtMileage = txtMileage * 1000
   End Select

End Sub


Private Sub txtMileage_AfterUpdate()
    
    Call fraDistance_AfterUpdate
    
End Sub


Private Sub txtMileage_BeforeUpdate(Cancel As Integer)

    If Not IsNumeric(txtMileage) Then
        MsgBox "You have entered a non-numeric value in the textbox.", vbExclamation, "Example"
        Cancel = True
        txtMileage.Undo
    End If
    
End Sub

 

[Mansoor Ahmad]

Not far now really

Thank you very very much for your time and help.

It is working just like as I wanted to be. I can feel it now I am almost there with your help. Just a few more hints needed.

If I keep on selecting the three options for one mileage data in text box, it will keep on multiplying the mileage data over and over again. Obviously it would do so because this is what code asks it to do. But is there any code to restrict such repetitive multiplications of one mileage data for one record?

Another things is if option 2 (x 1.609) is already selected in a new record and I enter mileage data already in Km, say 100 into text box and click on option Km, it still multiplied by 1.69, where I want it to be in Km.

 

[Mile-O]

If you declare a variable at the form level and assign the typed value to this variable on the after update of the textbox then the select case statement can look like this:

Option Explicit
Option Compare Database

Dim dblMileage as Double

Private Sub fraDistance_AfterUpdate()
    
   Select Case fraDistance
      Case Is = 1
         txtMileage = dblMileage 
      Case Is = 2
         txtMileage = dblMileage * 1.609
      Case Is = 3
         txtMileage = dblMileage * 1000
   End Select

End Sub

 

[Mansoor Ahmad]

Hopefull last time

Thanks again.

I am feeling very stupid. Time is running out for me probably that is one of the reasons I am not using my brain a lot at this time and come back to you for further assistance.

Can you please explain how to declare a variable at the form level part of your answer? I really appreciate.

Thank you once again.

 

[Mile-O]

Rather than declare it in a sub or function on the form's module, declare it outwith.

 

[Mansoor Ahmad]

Let me admit here that I am absoluetly zero in VB. I myself desparately feel now that I need to do something about it.

That is why I still do not have any clue what so ever where would I find this outwith command.

Hope I will hear from you soon. Thank you

 

[Mile-O]

See at the top of your form's module. Underneath the lines:

Option Explicit
Option Compare Database

simply put:

Dim dblMileage As Double

There is no Outwith command, I just meant outside of a sub or function, i.e not enclosed within Private Sub yaddayadda and End Sub

 

[Mansoor Ahmad]

Sorry again

How do you go to form's module?