Locking A Boolean Field In A Table To Only Allow One True Answer

[nouveauricheinvestments]

Hi,

I have a form with a subform (datasheet view) that displays records in
a table. There is a checkbox there where the user can select the item
and then hit a button that says 'View'. I want to lock the table so
that only one record can be checked true at a time. How would I do
that? I was thinking about writing a loop that cycles through the
records in a table and evaluates every other record to false except
the last one selected. I'm not sure how to access the record in code
though...

 

[Klatuu]

An update query that uses the same table, fields, and criteria as your form
and subform record selection criteria would do the trick. You would need to
execute the query, then requery the subform. You would also need to
bookmark the record you just checked so it would stay as the current record
and be rechecked. This example is just to offer an idea, it is not meant to
actually work:

Dim strSQL As String
Dim lngKey As Long

'Save the current record key so we can get back to it
lngKey = Me.txtSomeKeyField

'Set All check fields to false

strSQL = "UPDATE MySubTable SET MyChkFld = FALSE WHERE SomeKeyField = "
& Me.txtSomeKeyField;"
CurrentDb.Execute strSQL, dbFailOnError

'Requery the records and check the selected record

Me.Requery
With Me.RecordsetClone
.FindFirst "SomeKeyField = " & lngKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtSomeKeyField = True
End If
End With

 

[nouveauricheinvestments]

An update query that uses the same table, fields, and criteria as your form
and subform record selection criteria would do the trick. You would need to
execute the query, then requery the subform. You would also need to
bookmark the record you just checked so it would stay as the current record
and be rechecked. This example is just to offer an idea, it is not meant to
actually work:

Dim strSQL As String
Dim lngKey As Long

'Save the current record key so we can get back to it
lngKey = Me.txtSomeKeyField

'Set All check fields to false

strSQL = "UPDATE MySubTable SET MyChkFld = FALSE WHERE SomeKeyField = "
& Me.txtSomeKeyField;"
CurrentDb.Execute strSQL, dbFailOnError

'Requery the records and check the selected record

Me.Requery
With Me.RecordsetClone
.FindFirst "SomeKeyField = " & lngKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtSomeKeyField = True
End If

Okay great thank you...It seems to work from the point of deselecting
the previously selected option and then selecting the option i have
just selected. But, the problem is it is giving me an error: "Syntax
Error (Missing Operator) in expression."

It highlights the .FindFirst line. To be honest I'm not sure
why...This is the code I am using though...


Private Sub SelectTicket_Click()
Dim strSQL As String
Dim lngKey As String

DoCmd.OpenForm "ViewSelectedOpenTicket", acNormal, , , acFormReadOnly,
acHidden
lngKey = Form_ViewSelectedOpenTicket.Explanation
strSQL = "UPDATE [Pending Tickets] SET SelectTicket=FALSE"
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery
With Me.RecordsetClone
If Not .NoMatch Then
.FindFirst "Explanation = " & lngKey
Me.Bookmark = .Bookmark
Me.SelectTicket = True
End If
End With


End Sub

 

[nouveauricheinvestments]

An update query that uses the same table, fields, and criteria as your form
and subform record selection criteria would do the trick. You would need to
execute the query, then requery the subform. You would also need to
bookmark the record you just checked so it would stay as the current record
and be rechecked. This example is just to offer an idea, it is not meant to
actually work:

Dim strSQL As String
Dim lngKey As Long

'Save the current record key so we can get back to it
lngKey = Me.txtSomeKeyField

'Set All check fields to false

strSQL = "UPDATE MySubTable SET MyChkFld = FALSE WHERE SomeKeyField = "
& Me.txtSomeKeyField;"
CurrentDb.Execute strSQL, dbFailOnError

'Requery the records and check the selected record

Me.Requery
With Me.RecordsetClone
.FindFirst "SomeKeyField = " & lngKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.txtSomeKeyField = True
End If

Okay great thank you...It seems to work from the point of deselecting
the previously selected option and then selecting the option i have
just selected. But, the problem is it is giving me an error: "Syntax
Error (Missing Operator) in expression."

It highlights the .FindFirst line. To be honest I'm not sure
why...This is the code I am using though...

Private Sub SelectTicket_Click()
Dim strSQL As String
Dim lngKey As String

DoCmd.OpenForm "ViewSelectedOpenTicket", acNormal, , , acFormReadOnly,
acHidden
lngKey = Form_ViewSelectedOpenTicket.Explanation
strSQL = "UPDATE [Pending Tickets] SET SelectTicket=FALSE"
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery
With Me.RecordsetClone
If Not .NoMatch Then
.FindFirst "Explanation = " & lngKey
Me.Bookmark = .Bookmark
Me.SelectTicket = True
End If
End With

End Sub

I figured it out. This is what I have if you are interested in
knowing the solution:

Dim strSQL As String
Dim lngKey As String


DoCmd.OpenForm "ViewSelectedOpenTicket", acNormal, , , acFormReadOnly,
acHidden
Form_ViewSelectedOpenTicket.Explanation.SetFocus
lngKey = Form_ViewSelectedOpenTicket.Explanation.Text
strSQL = "UPDATE [Pending Tickets] SET SelectTicket=FALSE"
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
With Me.RecordsetClone
Me.Explanation.SetFocus
.FindFirst Explanation.Text = lngKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.SelectTicket = True
End If
End With
DoCmd.Close acForm, "ViewSelectedOpenTicket"

Me!SelectTicket.SetFocus
end sub