CODEWITHSUNDEEP
vbams-accessms-access-2003
GeneralMike
GeneralMike

Reputation: 3001

Adding a unit toggle to MS Access Form

TL;DR

I'm trying to perform some calculations on the values entered to a data entry form before saving them into a table.

Background

I have a form in MS Access that serves as a front end for entering data into our database. It is mostly distance data between a datum and various objects. The database and the applications that use that data have all been set up expecting all the measurements in feet, but a lot of our sources for the data are in meters. In the past, the user had to convert the data on their own before inputting it into the form. This extra step slows down productivity, and increases the chance of user error.

What I want to do is add a radial button to my form that lets the user swap between meters and feet. When it is swapped to meters, all the values for that record are converted to their meter equivalent before being displayed on the screen. If the user changes a record or creates a new one, the form will need to convert the values back into feet before updating the table. It's very important that the values in the table always remain in feet.

I was able to find this article that describes how to swap between record sources for a form. I think I can just set up 2 queries - one that pulls the data directly from the table, and one that performs the conversion - and swap between the queries as my record source when the user toggles the radial button.

The Problem

The issue with this set up is that since I have to use aliases in my queries to perform the conversion, the user cannot enter data while the radial is toggled to meters. If I try to update one of those fields, I just get a bing noise and the input is ignored. Is there any way to allow inputs when the radial is toggled to meters? As I said above, it is important that the data in the table is always in feet, so if the user has the 'meters' option selected and inputs a value like 500, the value entered into the table should be 1640, which is the result from the conversion to feet.

Sample

Here's simplified example of what my queries look like

FEET query

SELECT A,B,C AS LEN,D,E
FROM TABLE1

METERS query

SELECT A,B,C/0.3048 AS LEN,D,E
FROM TABLE1

Upvotes: 0

Views: 136

Answers (2)

Gustav
Gustav

Reputation: 55941

Use this query only:

SELECT A,B,C AS LEN,D,E
FROM TABLE1

On your form, hide the textbox bound to LEN.

Create a new unbound textbox, say, txtLEN.

Now use code like this where SelectMetric is the checkbox to mark when using metric values:

Private Sub Form_Current()

    Dim Factor As Currency

    If Me!SelectMetric.Value = True Then
        Factor = 0.3048
    Else
        Factor = 1
    End If        
    Me!txtLen.Value = Factor * Me!LEN.Value

End Sub

Private Sub txtLen_AfterUpdate()

    Dim Factor As Currency

    If Me!SelectMetric.Value = True Then
        Factor = 0.3048
    Else
        Factor = 1
    End If        
    Me!Len.Value = Me!txtLEN.Value / Factor

End Sub

Upvotes: 2

Brad
Brad

Reputation: 12253

Just change what is displayed in the textbox. Set up the text box to have a control source of

=Length*txtConversionFactor

Where txtConversionFactor is a hidden textbox which either has 1 in it if your units are to be displayed in feet or 0.3048 if they are to be displayed in meters. It could even be a combobox bound to a table like this

MultiplyFeetByToGetOutputUnits    OutputUnits
1                                 ft
0.3048                            m
12                                in
0.000189394                       mile

Then you can convert to whatever you want without changing your form.

Upvotes: 0

Related Questions