NotInList - add new record to combo - creates duplicate entry

I Have a Mainform/Subform that is used for Invoice DataEntry.  

If the Vendor name is not in the current list of Vendors in the dropdown, I need to be able to allow the user to add new to the list.

I have tried using the List Item edit form and also tried the NotInList code.
NotInListHowever, the first option I used was working and now it is creating a duplicate record that includes the New Vendor name, AgencyID and PolarID, but the remaining data for that record is left blank.  so when the user selects the Newly added Vendor name and then allows the user to complete the record.  but the duplicate record already exists, but not completed.
 DupRecHow do I have the current record on the subform to match the newly added record on the Vendor Edit form - and will this prevent duplicate record creation?
also tried sample code from MSN NotInList.  http://support.microsoft.com/kb/197526
Still displaying Duplicate records.

Here is all the code for the Invoice/Vendor infomation:
' Procedure : cboVendorName_AfterUpdate
' Date      : 2/7/2014
' Purpose   : After Selection of Vendor Name - the Following Fields are automatically populated.
            ' Agency Id
            ' AgencyPID
            ' Currency
'---------------------------------------------------------------------------------------
'
Private Sub cboVendorName_AfterUpdate()
   On Error GoTo cboVendorName_AfterUpdate_Error
    
        gInvID = Nz(Me.IDInvoice, 0)

        Me.AgencyID = Me.cboVendorName.Column(1)
        Me.AgencyPID = Me.cboVendorName.Column(2)
        Me.Currency = Me.cboVendorName.Column(3)
        Me.InvoiceDate.SetFocus
        'Me.cboVendorName.Requery

On Error GoTo 0
   Exit Sub

cboVendorName_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure cboVendorName_AfterUpdate of VBA Document Form_frmInvoiceSub"
End Sub
Private Sub cboVendorName_DblClick(Cancel As Integer)
    gInvID = Nz(Me.IDInvoice)
    gContractID = Nz(Me.ContractNumber)
End Sub

Private Sub cboVendorName_NotInList(NewData As String, Response As Integer)
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    Select Case MsgBox("This Vendor name does not exist. Create it", vbYesNo Or _
        vbQuestion Or vbDefaultButton1, "Add New Data")
    
        Case vbYes
            Response = acDataErrAdded
            rs.AddNew
            rs!VendorName = NewData
            rs.Update
        Case vbNo
            Me!VendorName = Null
            Exit Sub
    End Select
End Sub

'---------------------------------------------------------------------------------------
' Procedure : Currency_AfterUpdate
' Author    : c-kschaefer
' Date      : 2/7/2014
' Purpose   : Depending on Currency Type different field become visible.
'---------------------------------------------------------------------------------------
'
Private Sub Currency_AfterUpdate()
    Dim strsql As String
    Dim i As Integer
    Dim X As String
    
   On Error GoTo Currency_AfterUpdate_Error
    
    Me.cboVendorName.SetFocus

   On Error GoTo 0
   Exit Sub

Currency_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure Currency_AfterUpdate of VBA Document Form_frmInvoiceSub"

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Dim ctl As Access.Control
  
    For Each ctl In Me.Controls
        If ctl.Tag = "X" Then
            If IsNull(ctl.value) Then
               Cancel = True
               Exit For
            End If
        End If
    Next ctl

  If Cancel = True Then
     MsgBox "Please fill out all fields"
  End If

End Sub

'---------------------------------------------------------------------------------------
' Procedure : Form_Current
' Author    : c-kschaefer
' Date      : 2/7/2014
' Purpose   : if the Cording Slip has been processed the Record is locked preventing
            ' the user from editing the current record.
            ' However, allowing Add new records.
'---------------------------------------------------------------------------------------
'
Private Sub Form_Current()
   
Dim curDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String
Dim X As Integer
Dim strSQLCK As String
   
   On Error GoTo Form_Current_Error

Set curDB = CurrentDb

    gContractID = Nz(Me.ContractNumber, 0)
    gInvID = Nz(Me.IDInvoice, 0)
    gFilePath = DLookup("PrvYrFilePath", "tblgeneralInfo")
    
    If gPvYr = -1 Then
        strSQLCK = "Select * From tblinvoice IN " & Chr(39) & gFilePath & Chr(39) & ""
    Else
        strSQLCK = "Select * From tblinvoice"
    End If

    If Nz(Me.RecordLock, 0) = -1 Then
        Me.cboVendorName.Locked = True
        Me.AgencyID.Locked = True
        Me.AgencyPID.Locked = True
        Me.InvoiceDate.Locked = True
        Me.InvoiceNumber.Locked = True
        Me.Currency.Locked = True
        Me.AllowEdits = False
        Me.AllowDeletions = False
    Else
        Me.cboVendorName.Locked = False
        Me.AgencyID.Locked = True
        Me.AgencyPID.Locked = True
        Me.InvoiceDate.Locked = False
        Me.InvoiceNumber.Locked = False
        Me.Currency.Locked = False
        Me.AllowEdits = True
        Me.AllowDeletions = True
    End If
         
    If QueryExists("qryTemp1") = True Then
        DoCmd.DeleteObject acQuery, "qryTemp1"
    End If
    
    Set qdf = curDB.CreateQueryDef("QryTemp1")
    Application.RefreshDatabaseWindow
    qdf.SQL = strSQLCK
    
         strsql = "Select VendorName, AgencyID, AgencyPID, Currency, ContractNumber" & _
                " FROM QryTemp1" & _
                " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & "" & _
                " GROUP BY VendorName, AgencyID, AgencyPID,Currency, ContractNumber" & _
                " ORDER BY VendorName"
        Me.cboVendorName.RowSource = strsql
        gInvID = Nz(Me.IDInvoice, 0)
        Me.Refresh
   On Error GoTo 0
   Exit Sub

Form_Current_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure Form_Current of VBA Document Form_frmInvoiceSub"
End Sub

'---------------------------------------------------------------------------------------
' Procedure : Form_GotFocus
' Author    : c-kschaefer
' Date      : 2/25/2014
' Purpose   : Sets the Active Invoice Number
'---------------------------------------------------------------------------------------
'
Private Sub Form_GotFocus()
   On Error GoTo Form_GotFocus_Error

    gInvID = Nz(Me.IDInvoice, 0)

   On Error GoTo 0
   Exit Sub

Form_GotFocus_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_GotFocus of VBA Document Form_frmInvoiceSub"
End Sub

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COACHMAN99Commented:
I would add a small popup form to add a new vendor, then requery the dropdown on form activate, instead of trying to maintain vendors, invoices and line-items on one master/detail form.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
I already have the popup form, and the problem seems to be in that when I complete the data for the invoice record it is not going to the correct record to add the new data it is create a new record in it entirety.

How would you rectify this issue, to make sure the newly create record is the record that is being completed?

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
I even tried using the NotInList Code from MSN online - but that has a flaw in  the code that it does not like data found end of SQL string.

ctl.RowSource = ctl.RowSource & ";" & NewData

Private Sub cboVendorName_NotInList(NewData As String, _
        Response As Integer)
    Dim ctl As Control

    ' Return Control object that points to combo box.
    Set ctl = Me.cboVendorName
    ' Prompt user to verify they want to add new value.
    If MsgBox("Value is not in list. Add it?", _
         vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        ctl.RowSource = ctl.RowSource & ";" & NewData
    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If
End Sub

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

COACHMAN99Commented:
I probably would have a control form with the client dropdown, and add-new client button.
Then I would have a second button to open the invoice form (subsetted by the selection in theclient ID/dropdown), and use the '*' on the navigation bar to add a new invoice for the client.

Not sure if this is on topic.
0
Karen SchaeferBI ANALYSTAuthor Commented:
Ok upon further investigation, upon add new record the combo is being populated and on enter of the field or tab to next field a new record is being created,  However, when I hit esc and cancel the entry of the new record - Refresh the screen the record that was created is now visible.

FYI, this is a bound field to the VendorID

I know there is a cleaner way to handle this problem, but I am stumped.
0
Karen SchaeferBI ANALYSTAuthor Commented:
ok I tried using the popup but it is still creating 2 records when I go to completeing the invoice - it does not use the initial record - how do I get the combo to display the newly added value/record?

k
0
COACHMAN99Commented:
I do not understand 'when I complete the data for the invoice record it is not going to the correct record to add the new data it is create a new record in it entirety.'

Why would the process go anywhere when you are editing a record on a form?

If you are editing the current record then it will not create a new record by itself - you must have an event that is triggering this.

Do you have a 'main/control' page as I mentioned above? or are you trying to maintain client, invoice and invoice-detail on one form? The main page should control the maintenance of clients via a button/form, and another button should maintain the master/detail invoice form.
0
Karen SchaeferBI ANALYSTAuthor Commented:
Your suggestion worked, with 1 minor change I changed the data entry form for the Vendor to an unbound form this eliminated the duplicate entries into the Invoice table.

Thanks
0
COACHMAN99Commented:
good, perseverance won :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.