Singular selection on a boolean column

C

Charles Tam

I have a table with a boolean field. How do I ensure the user can only set
the column to True for one record for the entire table? If the user has
decided to set another record to True, the set record must be set back to
False. Any ideas?
 
J

Jeff Boyce

Charles

Are you saying you have a table of records, only one of which can be "True"
for this field?

Could you provide an example of the kind of data/domain you are working
with? It will help with getting more specific responses.

Regards

Jeff Boyce
<Access MVP>
 
C

Charles Tam

My table has a list of customers and the user would only nominate one of many
as a winner to a marketing draw price. Therefore, how could I ensure only one
customer could be set?
 
J

Jeff Boyce

Charles

I suppose one way might be to write a procedure that:

1. sets all records' value to False (an update query?)
2. sets the selected record to True (also an update query)

Good luck

Jeff Boyce
<Access MVP>
 
6

'69 Camaro

Hi, Charles.
how could I ensure only one
customer could be set?
Click to expand...

Use an update query to reset any other choices to false whenever the current
choice is chosen. In the following example, a bound form is used to make the
selection of the winner. The Boolean field for the winner selection is named
"Choice," chkChoice is a check box displaying this value, ID is the numerical
primary key of the table, and txtID is the name of the text box displaying
the primary key.

' * * * * Start Code * * * *

Private Sub chkChoice_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "UPDATE " & Me.RecordSource & _
" SET Choice = " & False & _
" WHERE (ID <> " & Me!txtID.Value & ")"

CurrentDb().Execute sqlStmt, dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in chkChoice_BeforeUpdate( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
6

'69 Camaro

Hi, Charles.

It appears that you tried to mark my earlier reply as an answer to your
question. If so, it's not marked with the green check mark. (The Web-based
newsreader is still pretty buggy and answer marks occasionally don't get
saved the first time.) Would you please do me a favor? Would you please
sign into the Community again, navigate to your question and select my reply
and answer the question "Did this post answer your question?" at the bottom
of the message? If successful, you should see a green check mark on my
reply a few minutes later after refreshing the Web page.

If it says "Was this post helpful to you?" then the Web site has not
recognized you as the person who asked the question, and you needn't try
marking it again because it won't be marked as an answer this time, either.
In that case, you might have to either post a message or visit your user
profile before the Web-based newsreader recognizes you as the person who
asked the question.

Thanks for your help!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
 
Top