Solved

NotInList - add new record to combo - creates duplicate entry

Posted on 2014-03-04
9
376 Views
Last Modified: 2014-03-04
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

0
Comment
Question by:Karen Schaefer
  • 5
  • 4
9 Comments
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 500 total points
ID: 39904147
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
 

Author Comment

by:Karen Schaefer
ID: 39904302
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
 

Author Comment

by:Karen Schaefer
ID: 39904369
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
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39904466
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Karen Schaefer
ID: 39904504
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
 

Author Comment

by:Karen Schaefer
ID: 39905068
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
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39905212
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
 

Author Closing Comment

by:Karen Schaefer
ID: 39905213
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
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39905218
good, perseverance won :-)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now