Solved Combo Box Not In List Event (1 Viewer)

mib1019

Member
Local time
Yesterday, 20:18
Joined
Jun 19, 2020
Messages
88
Hi. Hope everyone is well.

I'm not sure this is possible so I'm here to find out.

I have a combo box on a form with LimitToList property set to Yes. The data for the list is from a lookup table.

Sometimes the user will select an item from the list but then may need to add a little more info to the field. These little adds don't need to be recorded in the lookup table, i.e. it is particular to that record and may not ever be needed again. So I don't want Access to save it.

I have the Not In List Event working correctly essentially, asking if the I want to add what he's entered to be added to the table. If he chooses no, I'd like to have the field 'temporarily' to not be limited to the list and simply move on to the next field.

If I select No on the MsgBox, the standard message displays saying that I need to pick a value in the list, and drops the list open. I can then move to the next field, so it appears that the LimitToList property = False is working.

When I go back to design mode on the form, the LimitToList property is still set to Yes. I wondered if I need to have my code reset it to True after the temporary change to False.

Hope this makes sense and welcome any alternatives. Code is below.

Thanks,
MIB1019

Code:
Private Sub cboProgram_NotInList(NewData As String, Response As Integer)
On Error GoTo ErrorHandler
  
   Dim dbsMM As DAO.Database
   Dim rstProgram As DAO.Recordset
   Dim intAnswer As Integer
   Dim StationName
   StationName = DLookup("CompanyName", "tblCompanies", "Company_ID = " & Me.Parent.Company_ID)


   intAnswer = MsgBox("Do you want to add '" & NewData & "' to the program list for " & StationName & "?", _
      vbQuestion + vbYesNo)

   If intAnswer = vbYes Then

      ' Add program stored in NewData argument to tblProgramLookup
      Set dbsMM = CurrentDb
      Set rstProgram = dbsMM.OpenRecordset("tblProgramLookup")
      rstProgram.AddNew
      rstProgram!Program = NewData
      rstProgram.Update

      Response = acDataErrAdded ' Requery the combo box list.
  
   Else
      Me.cboProgram.LimitToList = False
      
   End If

   rstProgram.Close
   dbsMM.Close

   Set rstProgram = Nothing
   Set dbsMM = Nothing
  
Exit_Handler:
Exit Sub

ErrorHandler:
   Resume Exit_Handler
End Sub
 

June7

AWF VIP
Local time
Yesterday, 18:18
Joined
Mar 9, 2014
Messages
5,423
If you want the combo to be limited for next record then yes, set property to True. Use Current event.
 

mib1019

Member
Local time
Yesterday, 20:18
Joined
Jun 19, 2020
Messages
88
I am posting my code again. Added additional variables to complete the addnew to the lookup table.
It's not working though. If I answer Yes, the standard comes up telling me to pick an item in the list. If I respond no, it gives me that message but allows me to move on.
Here's the revised code.
Code:
Private Sub cboProgram_NotInList(NewData As String, Response As Integer)
On Error GoTo ErrorHandler
  
   Dim dbsMM As DAO.Database
   Dim rstProgram As DAO.Recordset
   Dim intAnswer As Integer
   Dim CompanyID
   Dim StationName
   Dim Item
   CompanyID = Me.Parent.Company_ID
   StationName = DLookup("CompanyName", "tblCompanies", "Company_ID = " & CompanyID)
   Item = Me.cboItem

   intAnswer = MsgBox("Do you want to add '" & NewData & "' to the program list for " & StationName & "?", _
      vbQuestion + vbYesNo)

   If intAnswer = vbYes Then

      ' Add program stored in NewData argument to tblProgramLookup
      Set dbsMM = CurrentDb
      Set rstProgram = dbsMM.OpenRecordset("tblProgramLookup")
      rstProgram.AddNew
      rstProgram!Company_ID = CompanyID
      rstProgram!Item = Item
      rstProgram!Program = NewData
      rstProgram.Update

      Response = acDataErrAdded ' Requery the combo box list.
  
   Else
      Me.cboProgram.LimitToList = False
      'what else needs to happen?
   End If

   rstProgram.Close
   dbsMM.Close

   Set rstProgram = Nothing
   Set dbsMM = Nothing
  
Exit_Handler:
Exit Sub

ErrorHandler:
   Resume Exit_Handler
End Sub
 

June7

AWF VIP
Local time
Yesterday, 18:18
Joined
Mar 9, 2014
Messages
5,423
Just use the original working code.

Then in form Current event:

Me.cboProgram.LimitToList = True
 

strive4peace

AWF VIP
Local time
Yesterday, 21:18
Joined
Apr 3, 2020
Messages
1,003
@mib1019, you can't say its ok for something to be entered that's not in the list if the Bound Column isn't visible, or isn't the one you type into

Instead of opening a recordset to add a record, you can also use an SQL statement. Not sure what would work best for you, just thought I'd mention it since I just posted a video about NotInList on YouTube:

Add New Data with NotInList VBA for Combo box

and here is the page with the VBA code:

VBA > Form > combo box NotInList
http://msaccessgurus.com/VBA/Code/Combo_NotInList.htm
 

strive4peace

AWF VIP
Local time
Yesterday, 21:18
Joined
Apr 3, 2020
Messages
1,003
ps, @mib1019, I wouldn't be suprised if LimitToList can't be changed on the fly. I recently ran into that issue with AutoExpand. I tried every way I could think of to change it, but nothing worked. What did work was using the Zoom box (Shift-F2) to enter a value.

Perhaps, if there are occasions where you want the user to pick a temporary value, you could add a textbox with the same control source?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:18
Joined
May 7, 2009
Messages
19,169
on the Response to No, you need to set Response = acDataErrContinue.
Code:
Private Sub cboProgram_NotInList(NewData As String, Response As Integer)
On Error GoTo ErrorHandler
 
   Dim dbsMM As DAO.Database
   Dim rstProgram As DAO.Recordset
   Dim intAnswer As Integer
   Dim CompanyID
   Dim StationName
   Dim Item
   CompanyID = Me.Parent.Company_ID
   StationName = DLookup("CompanyName", "tblCompanies", "Company_ID = " & CompanyID)
   Item = Me.cboItem

   intAnswer = MsgBox("Do you want to add '" & NewData & "' to the program list for " & StationName & "?", _
      vbQuestion + vbYesNo)

   If intAnswer = vbYes Then

      ' Add program stored in NewData argument to tblProgramLookup
      Set dbsMM = CurrentDb
      Set rstProgram = dbsMM.OpenRecordset("tblProgramLookup", dbOpenDynaset)
      rstProgram.AddNew
      rstProgram!Company_ID = CompanyID
      rstProgram!Item = Item
      rstProgram!Program = NewData
      rstProgram.Update

       rstProgram.Close
    
       Set rstProgram = Nothing
       Set dbsMM = Nothing
      
      Response = acDataErrAdded ' Requery the combo box list.
 
   Else
  
       Response = acDataErrContinue
      
   End If

 
Exit_Handler:
Exit Sub

ErrorHandler:
   Resume Exit_Handler
End Sub
 

Users who are viewing this thread

Top Bottom