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).
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?
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: