asked on

Not in List Event Procedure is not updating list

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

Nick67

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

Nick67

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

DavidDF1913

ASKER

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

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DavidDF1913

ASKER

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

Dsvid