Not In List

GGladu

New Member
Joined
Jul 13, 2014
Messages
12
I'm having a problem with this little app I created to print checks. I have a combo box with the limit to list set to yes. Sometimes I have a payee name that is only going to be used once so I don't want to add it to the list of payees but I can't get past the combobox. What am I doing wrong? Here's the code

Code:
Private Sub cmbPayeeList_NotInList(NewData As String, Response As Integer)

Dim tblPayee As DAO.Database
Dim rsPayee As DAO.Recordset
Dim intResp As Integer
Dim strMsg As String


  ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
    Else
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set tblPayee = OpenDatabase("PayeeList")
        Set rsPayee = tblPayee.OpenRecordset("tblPayee", dbOpenDynaset)
        ' Create a new record.
        rsPayee.AddNew
        rsPayee!strPayee = NewData
        rsPayee.Update
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
    End If
Exit_CustomerID_NotInList:
       Response = acDataErrContinue
       Exit Sub
Err_CustomerID_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue
 End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
What do you want to do? You cant add items to a list that LIMIT TO LIST = YES.

You could have button to turn OFF the limit, then enter it into the list... LIMIT TO LIST = false
 

GGladu

New Member
Joined
Jul 13, 2014
Messages
12
Thanks! I didn't use a button. Here's what I did the reset the LimitToList back true when the next control got focus

Private Sub cmbPayeeList_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
cmbPayeeList.LimitToList = False
cmbPayeeList_AfterUpdate

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,445
Messages
5,528,801
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top