Restrict one record to TRUE in a boolean field and all other records to FALSE (1 Viewer)

Isskint

Slowly Developing
Local time
Today, 15:47
Joined
Apr 25, 2012
Messages
1,302
Hi all

I have a currency table that stores the currency name, trading code and exchange rate. I also have a TRUE/FALSE field for the default currency (the base currency being used by the company using the database). At any one time only 1 currency can be the default.
Currently I use Before Update events (on several forms) whenever the default box value changes, to confirm the user wants to change the default currency.

Much to my disapproval, one company has demanded restricted user access to the tables (so they can make 'quicker' 'easier' - more like 'lazy' and unrecorded - changes). As a result someone made a second currency a default which has led to a couple of weeks of incorrect calculations.

Is there a better way to ensure only 1 record has a TRUE value? I am thinking Table validation rules but cannot see how to use Domain Aggregate functions.
 

Minty

AWF VIP
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,355
I don't think table level validation will work, as I'm pretty sure you can't use domain functions (Or any other UDF) in field validation.

Why not have a single record table with a pointer to the currency list ID and use that as the default record. They can have a form for that that doesn't allow new records and won't allow more than the existing record to be edited ?

If not simply explain that what they want will break things.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:47
Joined
May 7, 2009
Messages
19,175
you can use DCount() function for your validation on BeforeUpdate of the control:
Code:
Private Sub txtCurrency_BeforeUpdate(Cancel As Integer)
    If DCount("*", "baseCurrencyTable", "CurrencyField='" & txtCurrency & "' And BooleanField=True") = 0 Then
        ' not the default currency
        ' put messagebox or action here
        Cancel = True
    Else
    ' default Currency
    End If
End Sub
 

Isskint

Slowly Developing
Local time
Today, 15:47
Joined
Apr 25, 2012
Messages
1,302
Thanx Minty and arne.

Minty, this may work, even with them having access to tables, they can only set the pointer currency, so only one default at any time.

If not simply explain that what they want will break things.
Oh i have already had that conversation :banghead: along with other conversations about the problems that can be caused by them accessing the tables.

arne, that is what i currently do on forms, but this question is aimed at validating at the table level due the the client having access to the tables.
 

Solo712

Registered User.
Local time
Today, 11:47
Joined
Oct 19, 2012
Messages
828
Thanx Minty and arne.

Minty, this may work, even with them having access to tables, they can only set the pointer currency, so only one default at any time.


Oh i have already had that conversation :banghead: along with other conversations about the problems that can be caused by them accessing the tables.

arne, that is what i currently do on forms, but this question is aimed at validating at the table level due the the client having access to the tables.

Hi ,
I don't think anything would be bulletproof here but I would make that "pointer" table Minty suggested a "Usys" table and hide it in the navigation panel.

Best,
Jiri
 

Users who are viewing this thread

Top Bottom