Button sometimes selects the wrong record

HalloweenWeed · Dec 8, 2020

Hello,
As some of you might have noticed a few months ago, I have a very complicated and automated multi-institutional database. Here is the problem:
I have a form and subform that selects a person's record, to fill in that component of another form. Sometimes the button works, and other times it selects the first record on the list (I haven't been able to discern when/why), even though clicked on another record. What I need to know, is not necessarily how to fix the problem, but how to troubleshoot it. Now before you get any ideas about simplifying my forms or anything like that I need to explain some things. I cannot post this database, as it is a split (2) database, and contains confidential information. These forms will not work without a great deal of other forms being present.

The form uses complicated algorithms to "live search," instantly updating the results in the subform as you type in the single search criteria field. This field looks for even partial matches in multiple records multiple fields, and writes them in a table, after erasing said table, immediately every time a letter is typed, all via VBA, and the subform is bound to a simple query of this table. In order to do this, it was necessary to produce the results in a subform within the parent form (otherwise it interferes with typing in the criterion field). The parent form is a "single form." The subform is a "continuous form."

The button in the subform simply chooses the record and passes on the information to the proper form, also closing it's parent form.
The button's VBA (I've had to delete some to make the forum limit, see attached for full text):

Code:

Private Sub GrievanceStrt_Click()
On Error GoTo Err_Handler

Dim TempTxt As String
Dim stDocName As String, stOpenArgs As String, Msg As String

    Me.Parent.MD_OrMRsearch.SetFocus
    Me.Parent.Form.SetFocus
    stOpenArgs = Me.Parent.Form.Name

        If CurrentProject.AllForms("GrievanceForm").IsLoaded Then
            DoCmd.Close acForm, "GrievanceForm", acSaveYes
        End If
        stDocName = "NewGrievanceForm"

        If Me.Parent.Form!ParntForm = stDocName Then
            If Not CurrentProject.AllForms(stDocName).IsLoaded Then
                MsgBox "Error: New Grievance form (short form) has been closed. Cannot continue."
                GoTo CloseForm
            End If
        Else
            If CurrentProject.AllForms(stDocName).IsLoaded Then
                DoCmd.Close acForm, stDocName, acSaveYes
            End If

            Call ChkGrvncNumbers
            DoCmd.OpenForm stDocName, , , , acFormAdd, , stOpenArgs
        End If

        With Forms![NewGrievanceForm]

            If Not IsNull([MD_No]) Then ![MD_No] = [MD_No]
            If Not IsNull([MR_No]) Then ![MR_No] = [MR_No]
            ![GrievantLN] = [GrievantLN]
            If Not IsNull([GrievantFN]) Then ![GrievantFN] = [GrievantFN]
            If Not IsNull([GrievantMN]) Then ![GrievantMN] = [GrievantMN]
            ![LN_FN_MN] = [LN_FN_MN]
            ![GrievantID] = [GrievantID]
            If Not IsNull([GrievantDOB]) Then ![GrievantDOB] = [GrievantDOB]

            ![GrievantAddyLn1] = [GrievantAddyLn1]
            ![GrievantAddyLn2] = [GrievantAddyLn2]
            ![GrievantAddyCty] = [GrievantAddyCty]
            ![GrievantAddySt] = [GrievantAddySt]
            ![GrievantAddyZip] = [GrievantAddyZip]
            ![GrievantAddyPhone] = [GrievantAddyPhone]
            ![GrievantAddyCell] = [GrievantAddyCell]
            ![GrievantAddyEmail] = [GrievantAddyEmail]

            If Me.Parent.Form!ParntForm = "NewGrievanceForm" Then

                ![ModifiedUser] = Forms![SecurityCurrentUserName]![CurrentUser]
                ![ModifiedDate] = Now()

            Else

                ![IsComplaint] = False
                ![L1DateFiled] = Date
                ![LastLevelDateFiled] = Date
                ![L1ResponseDue] = WorkDayNext(Date, 5)
                ![L1DateDue] = WorkDayNext(Date, 5)
                ![DateDue] = WorkDayNext(Date, 5)
                ![OnTime] = "N/A"
                ![Level] = 1
                ![RespAgency] = Forms![SecurityCurrentUserName]![Agency]
                                                        ' initialize variables (zero) for logic
                ![L1CorrAct] = 0
                ![L1ExtReq] = 0
                ![L1FindSubst] = 0
                ![L2CorrAct] = 0
                ![L2ExtAgreed] = 0
                ![L2ExtReq] = 0
                ![L2FindSubst] = 0
                ![L3ComRev] = 0
                ![L3CorrAct] = 0
                ![L3HrngOffcrFinding] = 0
                ![L3HearingStatus] = 0
                ![GrievID_NoOnly] = 0
                ![UrgentStatus] = 0
                ![UrgentClientNotified] = 0
                ![UrgentHearing] = 0
                ![UrgentRefBackStatus] = 0


                Select Case Forms![SecurityCurrentUserName]![Agency]

                    Case 3
                        TempTxt = "Default value, NOT TO BE USED FOR OFFICIAL RESPONSE: I have " & Chr(13) & Chr(10)
                        TempTxt = TempTxt & Chr(13) & Chr(10) & "FINDINGS: " & Chr(13) & Chr(10)
                        TempTxt = TempTxt & Chr(13) & Chr(10) & "DETERMINATION: " & Chr(13) & Chr(10)
                        TempTxt = TempTxt & "I have determined that a violation of your rights did not occur "
                        TempTxt = TempTxt & "because: The Rights of Recipients of Mental Health Services"

                        ![L1ReasFind] = TempTxt

                End Select

                If Forms![HiddenAgencySettingsFrm]![Units] Then
                        ![UnitNo] = Me.Parent![UnitNo]

                    Select Case Me.Parent![UnitNo]

                        Case 1
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U1Name]

                        Case 2
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U2Name]

                        Case 3
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U3Name]

                        Case 4
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U4Name]

                        Case 5
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U5Name]

                        Case 6
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U6Name]

                        Case 100
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U0Name]

                    End Select
                End If

                If Forms![HiddenAgencySettingsFrm]![Units] And _
                            Not Forms![HiddenAgencySettingsFrm]![UnitsSameNo] Then

                    Select Case Me.Parent![UnitNo]

                        Case 1
                            ![GrievID_NoOnly] = Forms![GrvncNmbrngFrm]![U1_G]
                            ![GrievanceID] = Forms![GrvncNmbrngFrm]![U1_Gtxt]
                        ' increment Grievance number for next Grievance
                            Forms![GrvncNmbrngFrm]![U1_G] = _
                                    CheckForDupsGriev(Forms![GrvncNmbrngFrm]![U1_G] + 1, _
                                    Forms![GrvncNmbrngFrm]![U1_Gmin], _
                                    Forms![GrvncNmbrngFrm]![U1_Gmax], CStr(![UnitAbbriev]))



                    End Select

                Else

                    ![GrievID_NoOnly] = Forms![GrvncNmbrngFrm]![U0_G]
                    ![GrievanceID] = Forms![GrvncNmbrngFrm]![U0_Gtxt]
                        ' increment Grievance number for next Grievance
                    Forms![GrvncNmbrngFrm]![U0_G] = _
                            CheckForDupsGriev(Forms![GrvncNmbrngFrm]![U0_G] + 1, _
                            Forms![GrvncNmbrngFrm]![U0_Gmin], _
                            Forms![GrvncNmbrngFrm]![U0_Gmax], CStr(![UnitAbbriev]))

                End If

                If Forms![HiddenAgencySettingsFrm]![Units] Then
                        ![UnitNo] = Me.Parent![UnitNo]

                    Select Case Me.Parent![UnitNo]

                        Case 1
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U1Name]

                        Case 2
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U2Name]

                        Case 3
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U3Name]

                        Case 4
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U4Name]

                        Case 5
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U5Name]

                        Case 6
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U6Name]

                        Case 100
                            ![UnitAbbriev] = Forms![HiddenAgencySettingsFrm]![U0Name]

                    End Select
                End If
            GoTo ExitSub        'do not close this (Parent) form if ParntForm <> a grievance form
            End If
        End With
    End If

CloseForm:
    DoCmd.Close acForm, Me.Parent.Form.Name, acSaveNo   'no need to save this form, it does not modify info

ExitSub:
    Exit Sub

Err_Handler:
    MsgBox ("Error #" & Err.Number & ": " & Err.Description)
    Resume ExitSub

End Sub

The problem is, sometimes it pulls the info from the first record instead of the record of the button clicked on.
Before you ask: yes, it compiles. Access 2016/Office 365, and it is an .accdb, I can't make an executable out of it, it edits tables in the FE.
It all works fantastic otherwise.
If I click on the desired record of the subform, away from buttons (there are no other active controls), and then click on the button it works right 100% of the time.
I would appreciate any troubleshooting ideas. Thank you.

theDBguy · Dec 8, 2020

Hi. Did you already try stepping through the code? That's about the only advice I could give you.

Isaac · Dec 8, 2020

I would add that I try never to use code that relies on things like having the focus or parent, if at all possible refer to the explicit form, every time.

HalloweenWeed · Dec 8, 2020

Something just occurred to me: it might be in my live search code. It relies on two events: OnKeyup & AfterUpdate - for the textbox control on the main form. Could it be that the list is created on OnKeyup, then when you click on the button it runs the AfterUpdate event, which also requeries - the subroutine GrvtSrch() requerys at the end of the subroutine?
OnKeyUp:

Code:

Private Sub LN_FNinput_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Handler

    If Not (IsMissing(KeyCode) Or IsNull(KeyCode)) Then                     'pre-filter unworkable inputs
        If KeyCode < 8 Then GoTo ExitSub                        'tabbed into form
        If KeyCode > 8 And KeyCode < 32 Then GoTo ExitSub       'tabbed into form
        If KeyCode = 45 Or KeyCode = 190 Then                           ' "."
            MsgBox "Please don't use period ('.') when entering name."
            KeyCode = 0
            LN_FNinput.Text = vbNullString
            GoTo ExitSub
        End If
' Live search code starts here
        Error2185cnt = 0
        [LN_FNsearch] = AddSpaceAfterComma(LN_FNinput.Text)
                                                'copy the newly input text to the query input field
        Call Me.Form.[GrvntSrchSubform].Form.GrvtSrch
    End If

ExitSub:
    Exit Sub

Err_Handler:
    'trap error: "You can't reference a property or methods for a control unless the control has the focus."
    If Err.Number = 2185 Then
        Error2185cnt = Error2185cnt + 1
        If Error2185cnt < 4 Then    'limit to 4 iterations per KeyUp
            Me.LN_FNinput.SetFocus
            Resume Next
        Else
            Err.Clear
            Resume ExitSub 'give up and abort subroutine if throwing error 2185 twice, without bothering the human
        End If
    End If
    MsgBox ("Error #" & Err.Number & ": " & Err.Description)
    Resume ExitSub

End Sub

AfterUpdate:

Code:

Private Sub LN_FNinput_AfterUpdate()
On Error GoTo Err_Handler

    If Not ZeroLength(LN_FNinput) Then
        LN_FNsearch = AddSpaceAfterComma(LN_FNinput)
        Call Me.Form.[GrvntSrchSubform].Form.GrvtSrch
    End If

ExitSub:
    Exit Sub

Err_Handler:
    MsgBox ("Error #" & Err.Number & ": " & Err.Description)
    Resume ExitSub

End Sub

I will do some testing, try disabling AfterUpdate and see if that helps. Sorry.

EDIT: Oh yeah, forgot to mention the main (parent) form is unbound, and created that way from scratch (it has never been bound).

Pat Hartman · Dec 12, 2020

Where is the button? it will select the CURRENT record. If the button is on the main form or on the header/footer of a subform, the record it will select may not be the one you thought was current. The only sure way to select a subform record is for the button to be in the detail section of the subform.

HalloweenWeed · Dec 14, 2020

Pat Hartman said:
Where is the button? it will select the CURRENT record. If the button is on the main form or on the header/footer of a subform, the record it will select may not be the one you thought was current. The only sure way to select a subform record is for the button to be in the detail section of the subform.

Subform. Well there is another issue: previously I had both the OnKeyUp and the AfterUpdate events of the criterion textbox control (on the main form) tied to a subroutine on the subform that requeried the subform. I believe this may have been the problem, as when you go to click the button on the subform, after the OnKeyUp event fired, then the AfterUpdate event fires requerying the subform again, as you press the button. Like I said, I have removed the AfterUpdate event, and am in the process of determining the results.

Pat Hartman · Dec 14, 2020

"on the subform" is imprecise. Is it in the DETAIL section?

HalloweenWeed · Dec 14, 2020

Pat Hartman said:
"on the subform" is imprecise. Is it in the DETAIL section?

Oh, sorry, yes. As a matter of fact the subform does not have a visible header/footer.

HalloweenWeed · Jan 27, 2021

Update 1/27/21: Well I worked around the problem by adding vba that required a second button press to activate the "On Click" code. I never did figure out the problem, on the first press it always just selects the first record, so I added a count to ignore the first press, and code on other parent form events to reset the count. I certainly would like to know what a proper fix would be. >.-

MajP · Jan 27, 2021

@Isaac provided the answer why this does not work. It has to do with the blind setting of focus. This is equally as bad as relying on "Active" anything. You need to specifically go to what you want. Anything that relies on the focus being correct is a problem. Same issue with code like Docmd.close and not specifying what to close.

You need to get the correct PK and move the recordset to that record. Then instead of using the form use the forms recordset or recordsetclone.

HalloweenWeed · Jan 27, 2021

MajP said:
@Isaac provided the answer why this does not work. It has to do with the blind setting of focus. This is equally as bad as relying on "Active" anything. You need to specifically go to what you want. Anything that relies on the focus being correct is a problem. Same issue with code like Docmd.close and not specifying what to close.

You need to get the correct PK and move the recordset to that record. Then instead of using the form use the forms recordset or recordsetclone.

Sir, I know you are right, and I strive to do the right thing too. But this button selects the record, and therefore it sets the record to be used. It is a record selector button, in a list of multiple records.

That said, reading your post I figured out what the problem was, and I thank both you and @Isaac for that. Looking at my code, I realized that the code:

Code:

    Me.Parent.MD_OrMRsearch.SetFocus
    Me.Parent.Form.SetFocus

had no meaningful use, and apparently was something I used in a previous method/version. After I took it out, it works fine. Thank you all.

MajP · Jan 27, 2021

But this button selects the record, and therefore it sets the record to be used. It is a record selector button, in a list of multiple records.

Yes if you click into the current subform it will select that record and as soon as you set the focus to the parent it is likely no longer on that record especially if it is a linked subform. Also you cannot set focus to a form 99.9% of the time

If a form contains controls for which the Enabled property is set to True, you can't move the focus to the form itself. You can only move the focus to controls on the form.

HalloweenWeed · Jan 28, 2021

MajP said:
Yes if you click into the current subform it will select that record and as soon as you set the focus to the parent it is likely no longer on that record especially if it is a linked subform. Also you cannot set focus to a form 99.9% of the time.

If a form contains controls for which the Enabled property is set to True, you can't move the focus to the form itself. You can only move the focus to controls on the form.

TY, GTK.

Pat Hartman · Jan 30, 2021

If you use the current event of the record to "select" it, you don't need fancy code or a button.

Button sometimes selects the wrong record

HalloweenWeed

Member

Attachments

theDBguy

I’m here to help

Isaac

Lifelong Learner

HalloweenWeed

Member

Pat Hartman

Super Moderator

HalloweenWeed

Member

Pat Hartman

Super Moderator

HalloweenWeed

Member

HalloweenWeed

Member

MajP

You've got your good things, and you've got mine.

HalloweenWeed

Member

MajP

You've got your good things, and you've got mine.

HalloweenWeed

Member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread