Link to home
Start Free TrialLog in
Avatar of Paul Cook-Giles
Paul Cook-GilesFlag for United States of America

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.

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

Open in new window

Avatar of PatHartman
PatHartman
Flag of United States of America image

Try requerying the combo after you run the append query.

Me.cmbLimitWithGenericInput.Requery

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?
Avatar of Paul Cook-Giles

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?
Good morning, Pat.  :)  I've used Requery, and also Me.cmbLimitWithGenericInput =Me.cmbLimitWithGenericInput;  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.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial