.Selected property in subform (1 Viewer)

AZ_CC

Registered User.
Local time
Yesterday, 18:01
Joined
Apr 25, 2018
Messages
11
I'm having issues with a snippet of my code. I have a mainform, called frm_Youth, and two subforms I'm using to emulate a split form, called sbfrm_CasenoteSummary and sbfrm_Casenote. One of the fields on the sbfrm_Casenote subform is a mutivalue combo box. I want to require than input, but haven't been able to simply set it to Required = Yes, since the record attempts to save (and causes an error) when moving between the two subforms. I have code written to check the selected values, and ensure that the user selects and option, and does not select the option 'None' along with anything else.

I've used versions of this code in a few different event triggers. The problem is, it only seems to function properly if the combo box in question, casenote_WorkArea, is actually active (open to make selections). I've tried pre-empting these lines with a .SetFocus, which also does not seem to help. This is a version of the code I tried in the subform control's OnExit property (which triggered the Cancel = True in all circumstances, which is why I commented it out).

Code:
Private Sub sbfrm_Casenote_Exit(Cancel As Integer)

On Error GoTo err_handling

'determine how many selections have been made in the casenotes_WorkArea list, and if any of the selections is 'None'
Dim iTotalSelected As Integer
Dim iSelectedNone As Integer
Dim iCurrentRow As Integer

For iCurrentRow = 0 To (Forms.frm_youth.sbfrm_Casenote.Form.casenote_WorkArea.ListCount - 1)
    'Me.sbfrm_Casenote.Form.casenote_WorkArea.SetFocus
    If Me.sbfrm_Casenote.Form.casenote_WorkArea.Selected(iCurrentRow) = True Then
        iTotalSelected = iTotalSelected + 1
        If iCurrentRow = 6 Then iSelectedNone = 1 'note: row 6 = 'None'. This will break if list is ever resorted, need to make more robust. Tried .ItemData property to look for key value, but method is returning jibberish?
    End If
Next iCurrentRow

'check value; remove or switch to a debug.print once selection checking is working properly
MsgBox "Total Topics Selected: " & iTotalSelected
MsgBox "Selected 'None': " & iSelectedNone


'warn user if improper selections were made or the field was left blank, and set focus back to casenote_WorkArea control.
'CURRENTLY NONFUNCTIONAL, COMMENTED OUT
'If iTotalSelected = 0 Then
'    Dim iBlankResponse As Integer
'    Cancel = True
'    Me.sbfrm_Casenote.Form.casenote_WorkArea.SetFocus
'    iBlankResponse = MsgBox("You must make a selection for the work done during this contact. If none apply, select 'None'.", vbOKOnly, "Error!")
''ElseIf (iTotalSelected > 1 And iSelectedNone > 0) Then
'    Dim iNotBlank As Integer
'    Cancel = True
'    Me.sbfrm_Casenote.Form.casenote_WorkArea.SetFocus
'    iNotBlank = MsgBox("You can not select 'None' and another response. Please correct!", vbOKOnly, "Error!")
'End If

exit_procedure:
   Exit Sub

err_handling:
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Number & "in sbfrm_Casenotes control OnExit event. " & Err.Description
    End If
    Resume exit_procedure

End Sub

There are a number of other additions I'd like to make the code, for example usin the .ItemData method to search for the key associated with the 'None' selection rather than a fixed row reference, but I have to get this portion working before tackling that. I suspect there's a common reason for the properties not acting like I expect based on the documentation, but I'm not sure what it is.

I could work around this by updating values in unbound textboxes using the same basic code in the AfterUpdate event of the combo box, but that's not necessarily ever triggered (for example, if the combo box is left blank) and is also pretty opaque if I ever turn the system over to anyone else. Any have any suggestions on getting the existing code working more like I would expect before I resort to that?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:01
Joined
May 7, 2009
Messages
19,169
Did you know that Multivalue field is actually a Recordset , actually Recordset2, and not a List.
You are already in the correct path, adding the value of the mvf to an unbound textbox( do it on Change event if mvf). So you just test the unbound textbox if empty.
 

AZ_CC

Registered User.
Local time
Yesterday, 18:01
Joined
Apr 25, 2018
Messages
11
That will work, although it's not as elegant as I'd like. I can probably also use a DCount of one flavor or the other in the OnExit as a safeguard in case the combo box AfterUpdate event is never called (because the user left it blank).

I assume that's the same reason I can't use .ItemData to search for the key value? That just seems like a breakdown waiting to happen, but there may not be a good way around it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:01
Joined
May 7, 2009
Messages
19,169
as for me the unbound textbox on the mainform is your best alternative. Just hide it using its Visible property.
The Value if the unbound txtbix can be either blank, single string or string with comma as delimiter.
To get the count:

If trim(me.parent!unboundtextbox & "")= vbnullstring then
' count is zero
Else
' count = Ubound(Split(me.parent!unboundtextbox , ","))+1
End if
 
Last edited:

AZ_CC

Registered User.
Local time
Yesterday, 18:01
Joined
Apr 25, 2018
Messages
11
It ends up working to use DCount as a more robust solution; I have a query set up to break out the value selected in the multi valued field, and can use that so that these Dcount statements get me the info I need on the selections made:

Code:
    Dim iSelectedTopics As Integer
    Dim iSelectedNone As Integer
    
    iSelectedTopics = DCount("[casenote_WorkAreaID]", "qry_Casenotes", "[casenote_ID]= " & txt_CaseIDMirror.Value)
    iSelectedNone = DCount("[casenote_WorkAreaID.Value]", "qry_CasenotesWorkAreaValues", "[casenote_ID]= " & txt_CaseIDMirror.Value & " AND [casenote_WorkAreaID.Value]=7")

That also has the benefit of working based on key value and not being vulnerable if the sorting is changed. the problem is it's...slow. Glacially slow. ~5 seconds for my test database with less than a hundred sample entries in it. This looks like it's because I can't index the foreign key in the multi-valued field. That looks like a pretty major problem, as much as I want to slap a good old "Works fine in test environment!" bow on it an move on.

Is there a solution to that problem? Or did I just trade one dilemma for another?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:01
Joined
Feb 19, 2002
Messages
42,981
Private Sub sbfrm_Casenote_Exit(Cancel As Integer)
is the wrong event in which to do validation. Validation belongs in the Form's BeforeUpdate event. That event can be cancelled and you can prevent invalid records from being saved. By the time the subform's Exit event runs, the bad record has already been saved so think of it as closing the barn door after all the horses have escaped.
 

AZ_CC

Registered User.
Local time
Yesterday, 18:01
Joined
Apr 25, 2018
Messages
11
is the wrong event in which to do validation. Validation belongs in the Form's BeforeUpdate event. That event can be cancelled and you can prevent invalid records from being saved. By the time the subform's Exit event runs, the bad record has already been saved so think of it as closing the barn door after all the horses have escaped.

I thought the DCount/DLookup I'm relying on in the end only worked if those changes had been saved to the underlying record source? I can run the code in that event if the D functions will function, but I'm also concerned about triggering what's turned out to be a very slow operation unnecessarily when other data is changed, and since the worst case is users skipping entry entirely I also can't skip over this validation if the user never changes the MVF field.
 

Users who are viewing this thread

Top Bottom