option button (radio button) on each record of continuous form

user_791533 0
Viewed 415

This is for a project in MS Access 2016. I'd like to implement a radio button toggle across all records displaying in a continuous form. only one record can have the toggle "on" so when it's clicked it has to reset the previous record's flag to off. I'm only finding help on radio button usage to select from multiple values of a field for one record, usually on single form. Can this be done?

Example of continuous forms with radio buttons

ms-access
3 Answers

Radio button must be bound to a yes/no field then use UPDATE action SQL to make sure all records except current have field set to 0. Need a unique record identifier field such as autonumber.

Private Sub Option29_Click()
CurrentDb.Execute "UPDATE tablename SET fieldname = 0 WHERE ID <>" & Me.ID
End Sub

Be aware that in a multi-user database users can conflict with each other and another solution will be needed. Depends what you need to do with the selected record.

user_76071900

Use the RecordSetClone of the form:

Private Sub Active_AfterUpdate()

    Dim Records         As DAO.Recordset
    
    Me.Dirty = False
    
    If Me!Active.Value = True Then
        Set Records = Me.RecordsetClone
        Records.MoveFirst
        
        While Not Records.EOF
            If Records!Id.Value <> Me!Id.Value Then
                If Records!Active.Value = True Then
                    Records.Edit
                        Records!Active.Value = False
                    Records.Update
                End If
            End If
            Records.MoveNext
        Wend

        Records.Close
    End If
    
End Sub
user_35272970

You can do it in easy way:

1- Add to your table a new Yes/No Field ( e.g named CurrentRec)

2- Put the Radio Button and link it to this field ( e.g named rdCurrRec)

3- For the radio button Set 'Enabled' property to False and 'Locked' property to 'True'. This will disable Click - Double Click and Hover response.

4- Put the following code

Dim PreBookmark(4) As Byte   'Save old bookmark

Private Sub Form_Current()
    If Not Me.NewRecord Then  'Make sure not on new record
        Me.rdCurrRec = True
        If (PreBookmark(0) <> 0 Or PreBookmark(1) <> 0 Or PreBookmark(2) <> 0 Or PreBookmark(3) <> 0) Then
            Me.RecordsetClone.Bookmark = PreBookmark
            Me.RecordsetClone.Edit
            Me.RecordsetClone.Fields("CurrentRec") = False
            Me.RecordsetClone.Update
        End If
        PreBookmark(0) = Me.Bookmark(0)
        PreBookmark(1) = Me.Bookmark(1)
        PreBookmark(2) = Me.Bookmark(2)
        PreBookmark(3) = Me.Bookmark(3)
    Else  'If new record remove bullet from previous record
        If (PreBookmark(0) <> 0 Or PreBookmark(1) <> 0 Or PreBookmark(2) <> 0 Or PreBookmark(3) <> 0) Then
            Me.RecordsetClone.Bookmark = PreBookmark
            Me.RecordsetClone.Edit
            Me.RecordsetClone.Fields("CurrentRec") = False
            Me.RecordsetClone.Update
        End If
    End If
End Sub

Private Sub Form_Close()
    'Remove the mark before exiting to avoid it appear next time to open the form in two places
    If Not Me.NewRecord Then
        If (PreBookmark(0) <> 0 Or PreBookmark(1) <> 0 Or PreBookmark(2) <> 0 Or PreBookmark(3) <> 0) Then
            Me.RecordsetClone.Bookmark = PreBookmark
            Me.RecordsetClone.Edit
            Me.RecordsetClone.Fields("CurrentRec") = False
            Me.RecordsetClone.Update
        End If
    End If
End Sub

This will not iterate all the data so for large number of records it will not delay the response as it works only on two records

user_85335100
Related