Code in subform bombs single choice (1 Viewer)

[spikepl]

I have a form and a continuous subform. It is for a multiple-choice selection, and the subform displays all the available choices just fine. A choice is selected by clicking on a checkbox in the desired line.

I have the code below, so that when a choice is made (or remade) by clicking on a checkbox, all other checkboxes get deselected. This works fine in the subform when used standalone. Nothing happens when I use the main form with this subform embedded. The code does get executed, but obviously I am missing something, because now (in the embedded form) I can select more than one choice.

Private Sub chbChoiceSelected_AfterUpdate()
    Dim rst As DAO.Recordset
    Dim myBookmark As String
    Dim tempBookmark As String
    Set rst = Me.RecordsetClone
    myBookmark = Me.Recordset.Bookmark
    
    With rst
        .MoveFirst
        Do While Not .EOF
            tempBookmark = .Bookmark
            If tempBookmark = myBookmark Then
            Else
            .Edit
            !ChoiceSelected = False
            .Update
            End If
            .MoveNext
        Loop
        .Bookmark = myBookmark
    End With

    Set rst = Nothing
End Sub

I don't really care how I accomplish what I want - if you have a better suggestion then please come forward

Update:
The .edit -bit is never reached when the form is embedded, because the bookmark of current line never matches the remembered bookmark (marking the line where I clicked). Why not, when it works just fine when the subform is run standalone ?

 

[md57780]

I've done something similar, but have preferred to use an SQL statement. Maybe it can help you.

With the code below, you would need to change "tableName" and "UniqueRecordID" to the appropriate names for your situation.

Private Sub chbChoiceSelected_AfterUpdate()
On Error Goto ErrorTrap
Dim strSQL as string 
 
     strSQL = "UPDATE tableName SET ChoiceSelected = FALSE WHERE ( UniqueRecordID <> " & Me.UniqueRecordID & ");"
     DoCmd.SetWarnings FALSE
     DoCmd.RunSQL strSQL
 
ExitSub:
     DoCmd.SetWarnings TRUE
     Exit Sub
 
ErrorTrap:
     MsgBox Err.Description, vbCritical
     Resume ExitSub
 
End Sub

 

[spikepl]

Thanks - now you triggered something, because I have some code similar to yours elsewhere, so thanks for that.

But now I've got to the point that I want to know what the heck is going on, because it makes no sense to my limited imagination

 

[md57780]

Instead of using the bookmark as a reference to your current record, you might try using a UniqueRecordID.

Private Sub chbChoiceSelected_AfterUpdate()
On Error Goto ErrorTrap
Dim rst As DAO.Recordset
 
    Set rst = Me.RecordsetClone
 
    With rst
        .MoveFirst
        Do While Not .EOF
            If .UniqueRecordID <> Me.UniqueRecordID Then
                .Edit
                !ChoiceSelected = False
                .Update
            End If
            .MoveNext
        Loop
    End With
 
ExitSub:
    Set rst = Nothing
    Exit Sub
 
ErrorTrap:
    MsgBox Err.Description, vbCritical
    Resume ExitSub
 
End Sub

 

[spikepl]

#4 Good idea, i'll give it a whirl. I am still curious as to why my stuff failed, though.

 

[spikepl]

OK that works just fine.... but I am still CURIOOOOOOOUS

 

[md57780]

Not positive, but from what I've replicated, it seems to be related to the data type of the bookmark and how it is being saved into the STRING variable.

When watching that variable, it seems to always have a value of "?
", even when records change. And since it's always being stored the same way, the IF statement in your example would never evaluate to FALSE, thus never reaching the .Edit line.

I've tried using a VARIANT variable instead of a STRING. In this case, it stores the Bookmark as a Byte Array. To my knowledge, there's not a simple inline comparison of arrays to use in IF statement as in:

If Array1 = Array2 Then...

I'm sure there are those more qualified to answer in proper technical conclusions, but I'm offering my two cents anyway.

 

[spikepl]

But then why does it work in the subform on its own?

 

[md57780]

that, I don't know. If you can post a working example as you have it, I'll be glad to look.

 

[spikepl]

I appreciate the thought, but it's too much work just to satisfy my curiosity. I'll just have to live with another of Bill Gates' mysteries

Thanks for your efforts, at least now I can get on in the incessant search for the next trap.