Only one record with yes/no

[GDW]

I have a yes/no check box field in my "tblSession" table named
"CurrentSession". I have many Sessions but only one is current. I only
want one record to show this field as 'true'. Is there more efficient way
than counting records each time the my form opens. Maybe a checkbox isn't
the best way to do this or can I somehow index on this field?

Below is my tblSession properties;

SessionID Long Integer 4

AllowZeroLength: False

Attributes: Fixed Size,
Auto-Increment

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

Description: Class Session
ID - Primary Key

GUID: {guid
{57F94C73-3848-4AD1-9B5F-A4B37AF8FE82}}

OrdinalPosition: 0

Required: False

SourceField: SessionID

SourceTable: tblSession

SessionDescription Text 50

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: 1853

DataUpdatable: False

Description: Class Session
Name

DisplayControl: Text Box

GUID: {guid
{1CB2B847-4B5C-4D3C-BDDD-658E04C286C1}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: False

SourceField:
SessionDescription

SourceTable: tblSession

UnicodeCompression: True

SessionStartDate Date/Time 8

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

Description: Date is the
First Monday that classes start for the session

Format: Short Date

GUID: {guid
{AEA84478-B36F-46CC-97EA-5CCA33C7EE66}}

IMEMode: 0

IMESentenceMode: 3

InputMask: 99/99/00;;_

OrdinalPosition: 2

Required: False

SourceField:
SessionStartDate

SourceTable: tblSession

CurrentSession Yes/No 1

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

Description: Is this the
current session?

DisplayControl: 106

Format: Yes/No

GUID: {guid
{9F3239B8-27D4-4FCA-89A5-C50350A0DD75}}

OrdinalPosition: 3

Required: False

SourceField: CurrentSession

SourceTable: tblSession

Table Indexes

Name Number of Fields

PrimaryKey 1

Clustered: False

DistinctCount: 3

Foreign: False

IgnoreNulls: False

Name: PrimaryKey

Primary: True

Required: True

Unique: True

Fields:

SessionID Ascending

SessionID 1

Clustered: False

DistinctCount: 3

Foreign: False

IgnoreNulls: False

Name: SessionID

Primary: False

Required: False

Unique: False

Fields:

SessionID Ascending



C:\Documents and Settings\All Users\Documents\MS Access DBs\NLCA
Registration Thursday, March 31, 2005

DBs\NLCA Registrations II\NLCA Registrations.mdb

Table: tblSession

 

[Arvin Meyer]

Assuming that the only way to get to your data is a form. If that isn't
true, you need to re-examine the way you are doing your process. No good
database administrator ever lets users see anything but forms and reports,
not ever.

In the AfterUpdate event of the check box, check if the value is true, then
store the value in a temporary variable, update all the rest of the rows to
false, then re-establish your true value for that 1 record. Some thing like
(aircode):

Sub chkCurrentSession_AfterUpdate()
Dim blnHold As Boolean

blnHold = Me.chkCurrentSession
If blnHold = True Then
CurrentDB.Execute "UPDATE MyTable SET MyTable.CurrentSession = False"
Me.chkCurrentSession = blnHold
Else
Exit Sub
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access