We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Not in List Event Procedure is not updating list

DavidDF1913
DavidDF1913 asked
on
Medium Priority
450 Views
Last Modified: 2012-05-11
I have attached the Not-in-list Event Procedure for my combo box called Combo_Clients.
Also attached is the After Update Procedure.  I want to search for clients on the client No. field (cClientno - numeric) which is in the ClientTbl. (main form name is ClientFrm).

If user enters a client in the list it is displayed in the form.  If a client number is  not in the list the user is prompted to create new client, display it in the form where the user can complete the other fields (clientFld, contact, tel, etc.) and add it  to the list.  The code below creates a new client but evidently does not update the list so it asks to add it again.  This creates an error that states a duplicate will be created.  Thanks for any help.

David


Private Sub Combo_Clients_NotInList(NewData As String, Response As Integer)
'Original version Created by Helen Feddema
'modified 4-25-2011   ' Limit to List set to Yes  & using Access 2010
On Error GoTo ErrorHandler
    Dim cbo As Access.ComboBox
    Dim dbs As DAO.Database
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String
      strTable = "clientTbl" ‘combo box's row source
      strEntry = "Client No."  'type of item to add to the table
    strFieldName = "[cClientNo]"  'field in lookup table where new entry is stored
    Set cbo = Me.ActiveControl    'The add-to combo box
    'Display msg box asking whether user wants to add new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)
    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add new record to lookup table.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strTable)
        rst.AddNew
        rst(strFieldName) = NewData
        rst.Update
        intResponse = acDataErrAdded
        intResponse = acDataErrAdded
        rst.Close
        Me.Dirty = False
       ' Me.Requery
       ' intResponse = acDataErrAdded
        GoTo ErrorHandlerExit
     End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Private Sub Combo17_AfterUpdate()
'Find the record that matches the control.
  Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cClientno] = " & Me![Combo17]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
You've got a lot of stuff in there!
It looks like Helen built a generic NotInList handler.
I don't know if I'd go that route.
It's fairly complex.

Just straight up in the controls NotInList event
Dim db As Database
Dim rs As Recordset
Dim lngClientID As Long  '<------------ this depends on what YOUR primary key is
If vbYes = MsgBox("'" & StrConv(NewData, vbProperCase) & "' is not entered as a current Client." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM [Clienttbl] WHERE 1=2;", dbOpenDynaset, dbSeeChanges)
    With rs
       .AddNew
       ![Client No.] = NewData
       .Update
       .Bookmark = .LastModified
       lngOwnerID = ![Client ID] '<------------ this depends on what YOUR primary key is
            
     End With
     rs.Close
     db.Close
     Set rs = Nothing
     Set db = Nothing
     DoCmd.OpenForm "frmClients", , , "[Client No.]=" & lngOwnerID  '<------------ this depends on what YOUR primary key is, and the form where the rest is to be entered
        
    DoCmd.SelectObject acForm, "frmClients" '<------------ this depends on what YOUR form, mine is "frmClients"
    DoCmd.Maximize      
    response = acDataErrAdded
    Else
        response = acDataErrContinue
    End If

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
If the author wants to do that, he can of course.
He hasn't given me any feedback on my post.
<The solution was not related to my question>

My code is an adaptation of production code that deals with a notinlist event by creating a new record and opening a form to edit it.
If it was not related, I clearly did not understand the question, and was not given any re-direction.
It's the author's call

Author

Commented:
Hi:
I am sorry, I didn't mean the solutionoffered  was not good but i tried to adapt  it for my problem in every way I could think of and it failed.   I may have phrased the question poorly.  The key thing I believe is pulling up the requested record if it exists but adding a new record  using the key field, cClientNo, if user requests it. Perhaps my event prodedure After Update is wrong.

David
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I finslly got it to work. Thanks very much for your help.

Dsvid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.