Only one true value per set of records... (1 Viewer)

[KenHigg]

Hi all -

I have a table with say 100 rows. There is a pk. There is also a fk. So say 4 of these records share a common fk. Now in the form I show these filtered records. One of the other flds is a check box - yes/no type fld. I only want the user to be able to have this checked true for one of the 4 records. So to start with when the 4 records are displayed none of the check boxes are selected. The the user checks one. No problem. Now the next time the user pulls up the 4 records they display correctly with the one check box in the one record selected. But now they want the check box in one of the other records to be selected. So they check it. But now I need to uncheck the other records checkbox... Any suggestions on how to best do this?

 

[arnelgp]

use the Click event on every checkbox you have, like:

Private Sub Check0_Click()
Call subUncheckOthers(Screen.ActiveControl.Name)
End Sub

Private Sub subUncheckOthers(sControlName As String)

Dim c As Control

For Each c In Me.Controls

If TypeOf c Is CheckBox Then
If c.Name <> sControlName Then c.Value = False
End If

Next c

End Sub

 

[KenHigg]

Interesting but I'm not sure that would work...

 

[The_Doc_Man]

The way I might approach this is based on how you probably have the items displayed.

1. On the "button click" routine, before you do anything else, trap the criteria that would describe this button among all others. I believe you said it was the FK that was common to the other items? So if this is a parent/child case, you have maybe four records (as in your example) with the same FK. Also trap the PK and hold it for a moment as well.

2. Write an SQL UPDATE statement that clears the underlying YES/NO flag for every record where the FK field matches the trapped FK.

3. Write an SQL update statement that sets the underlying YES/NO flag for the one record has the PK that matches the trapped PK.

4. Requery & Refresh the sub-form.

I.e. simulate a variable-entry radio button.

 

[arnelgp]

Interesting but I'm not sure that would work...

i already tested it on forms and subform using bound checkbox.

 

[KenHigg]

Thanks for the idea Doc. I have been pecking around and seem to have a solution. If one checkbox is already selected and they select another, in the after update I save the new record id and then using recordsets uncheck all other record check boxes...

Private Sub blnTopicClosedStatus_AfterUpdate()
    Dim inTopic
    Dim intRecno As Integer

    intTopicID = Me.intTopicID
    intRecno = Me.ID

    Dim MyDB As Database, RS As Recordset
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    
    Set RS = MyDB.OpenRecordset("tblTopicStatusTypes", dbOpenDynaset)
      
    RS.MoveFirst
      
    Do Until RS.EOF
        RS.Edit
        If (RS!intTopicID = intTopicID) And (RS!ID <> intRecno) Then
            RS!blnTopicClosedStatus = False
            RS.Update
        End If
        RS.MoveNext
    Loop
    
    RS.Close
    MyDB.Close
    Set RS = Nothing
    Set MyDB = Nothing
    
    Me.Requery

End Sub

 

[Galaxiom]

The foundation to robust database design is using a data structure that precludes invalid data ever being stored. By using a structure that enables multiple records to be selected you risk multiple records being selected. From there on anything to stop it happening is really just a kludge.

In this case I would use a related table to store the PK of the designated records. A unique index would prevent two entries for the same PK ever being entered making it utterly impossible to store invalid data.

I am sure it would even be possible to present the data on the form with a checkbox in precisely the same way you do now.

It can't find it right now but I do have an example of adding a phantom checkbox to a form's recordsource. Don't bother searching online because I am pretty sure it is a Galaxiom unpublished original. It uses a query with the Cartesian product of a table with a single boolean field having one record and the real data source. Remarkably, although based on just a single record, the checkbox in the RecordSource is independently updateable on each record in the form.

VBA could be applied to store the selected data by loping through the form's recordset.

It is too late for me to recreate tonight but I think I could do a demo tomorrow night if you are interested.

 

[KenHigg]

Thats ok, It seems to be working now - Thanks all the same

 

[Galaxiom]

Thats ok, It seems to be working now - Thanks all the same

Sure it is working but I hope all readers understand the point I was making about data structure being the key to integrity. A robust database won't accept invalid data even if it is being interfaced at the table level.

The solution accepted by the OP is implemented at form level. Someone can still screw it up by editing the tables. I don't know what Normal Form I am advocating in this case but I expect it would be near the top of the pyramid.