Paul Cook-Giles
asked on
Using a combo box ListItemsEditForm property
I want to use the new-in-2017 combo box property "ListItemsEditForm".
I have a combo box that displays names from my PeopleTb (FirstName concatenated with a space and LastName); when an entry is chosen it populates FirstName LastName into a text field in my Data table. The combo box properties are LimitToList : Yes and List Items Edit Form : MaintainPeopleFrm. There is no code in the On Not In List event.
if a name that is not already in PeopleTb is typed into the combo box, and the box looses focus, I get this behavior: A dialog box appears saying "The text you entered isn't an item in the list. Do you want to edit the items in the list?" If I click Yes, the MaintainPeopleFrm opens, all records available, focus on the first record in the table. If I go to a new record, enter a first and last name, and close the form, I get the "The text you entered isn't an item..." dialog box again.
What I want is a custom dialog box ("This name isn't in the People Table; cancel to choose a person already entered, or OK to enter a new person.") If OK is clicked, the MaintainPeopleFrm will open in DataEntry mode and accept entry of a new first and last name. On close of the MaintainPeopleFrm , the combo box will contain the new name, and the row source refreshed so the "The text you entered isn't an item..." dialog box is not displayed again, but will be available if I want to choose the name a second time in the current session.
FWIW, I have code that accomplishes this goal with a generic input box, but I want to use the ListItemsEditForm property without modifying the MaintainPeopleFrm.
I have a combo box that displays names from my PeopleTb (FirstName concatenated with a space and LastName); when an entry is chosen it populates FirstName LastName into a text field in my Data table. The combo box properties are LimitToList : Yes and List Items Edit Form : MaintainPeopleFrm. There is no code in the On Not In List event.
if a name that is not already in PeopleTb is typed into the combo box, and the box looses focus, I get this behavior: A dialog box appears saying "The text you entered isn't an item in the list. Do you want to edit the items in the list?" If I click Yes, the MaintainPeopleFrm opens, all records available, focus on the first record in the table. If I go to a new record, enter a first and last name, and close the form, I get the "The text you entered isn't an item..." dialog box again.
What I want is a custom dialog box ("This name isn't in the People Table; cancel to choose a person already entered, or OK to enter a new person.") If OK is clicked, the MaintainPeopleFrm will open in DataEntry mode and accept entry of a new first and last name. On close of the MaintainPeopleFrm , the combo box will contain the new name, and the row source refreshed so the "The text you entered isn't an item..." dialog box is not displayed again, but will be available if I want to choose the name a second time in the current session.
FWIW, I have code that accomplishes this goal with a generic input box, but I want to use the ListItemsEditForm property without modifying the MaintainPeopleFrm.
Private Sub cmbLimitWithGenericInput_NotInList(NewData As String, Response As Integer)
'20170406 PCG Initial Development
Dim strNewValue As String, strFirst As String, strLast As String
10 strNewValue = cmbLimitWithGenericInput.Text
20 varResponse = InputBox(Chr(34) & strNewValue & Chr(34) & " is not a valid choice. If it should be added to the dropdown list, " & _
"edit the value below as necessary, and click OK. If not, click Cancel.", CurrentDb.Name, strNewValue)
30 If varResponse = vbCancel Then
40 GoTo EndSub
50 Else
'parse Text as necessary
60 strNewValue = varResponse
70 strFirst = FirstBit(strNewValue, " ")
80 strLast = BitsAfterLast(strNewValue, " ")
'insert into table; note that all required fields must be populated!
90 DoCmd.SetWarnings False
100 DoCmd.RunSQL "Insert into PeopleTb (FirstNa, LastNa) select '" & strFirst & "', '" & strLast & "'"
'refresh recordsource
110 Me.cmbLimitWithGenericInput = Me.cmbLimitWithGenericInput
'set cmbText = strResponse
120 Me.cmbLimitWithGenericInput = strFirst & " " & strLast
'supress the standard Error message
130 Response = acDataErrContinue
140 End If
EndSub:
End Sub
ASKER
Thanks, Pat. I have code elsewhere in the db that recognizes and handles prefixes, suffixes, and middle names (and I need to tweak it to handle punctuated and multi-part surnames); I didn't want to include it here because it's not germane to this question... which is "How do I use the ListItemsEditForm property to generate a custom notification message and set the OnOpen options for the defined form?"
Perhaps your users are more conscientious than others in my experience.
Did you try the requery?
Did you try the requery?
ASKER
Good morning, Pat. :) I've used Requery, and also Me.cmbLimitWithGenericInpu t =Me.cmbLimitWithGenericInp ut; they seem to accomplish the same thing.
What I'm really asking is if there's a way to use the ListItemsEditForm property the way I want (with a customizable message and the designated input form opened as Data Entry). If not, no stress-- I can continue with what I've been doing.
What I'm really asking is if there's a way to use the ListItemsEditForm property the way I want (with a customizable message and the designated input form opened as Data Entry). If not, no stress-- I can continue with what I've been doing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Me.cmbLimitWithGenericInpu
I don't ever do this. I find that allowing people to make entries in a list on the fly makes them sloppy and defeats the purpose because we end up with all the variations we were hoping to avoid.
I especially wouldn't do it in this case since you have to parse the name. What happens to Betty Lou Smith? or John Jones Jr? Or Mike Mac Donald? What happens to Annie O'Mally?