DavidDF1913
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(Ne wData 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
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(Ne
'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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I finslly got it to work. Thanks very much for your help.
Dsvid
Dsvid
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
Open in new window