Solved stuck on not in list (1 Viewer)

gakiss2

Registered User.
Local time
Today, 14:07
Joined
Nov 21, 2018
Messages
124
Its the simple things that are most frustrating. Trying to use a dropdown for a user to add an email to a list of recipients. It works mostly, user clicks the arrow on the combo then clicks on one of the emails listed then the email gets added to a text box which is a collection of emails chosen separated by semicolons, Perfect. However if the user just starts typing an email it 'autocompletes' based on the RowSource, as it should, then if the user hits enter, the underlying form suddenly goes into new record mode. I just want it to add that email to the text box (collection of email addresses), clear the combo box and wait for the next entry or for the user to move on to another field. Also I have a function to add an email to the list if it isn't already on it (fired by the not in list event) and it seems to be working fine datawise but the form behaves the same as previously after the user acknowledges with the obligatory "Are you Sure" mgsbox. Help is greatly appreciated.
 

theDBguy

I’m here to help
Local time
Today, 14:07
Joined
Oct 29, 2018
Messages
10,001
Hi. Sounds like you have an event procedure in both the AfterUpdate and NotInList events of your combo. Can you please post both of those? Thank you.
 

gakiss2

Registered User.
Local time
Today, 14:07
Joined
Nov 21, 2018
Messages
124
Its like you knew.... Yes you are correct. I am using the after update to accomplish the task of adding the selected email to the text box(collection of emails) Also, I want the newly added email to go there as well. I haven't coded it yet as I wanted to get past this speedbump first.

Private Sub cbonewEmail_AfterUpdate()
Me.txtEmailStr = Me.txtEmailStr & ";" & cbonewEmail.Column(0)
Me.Refresh
End Sub

Sub for 'not in list' is some code I found that defines a function for adding the new entry to the table. You just call it and put in the parameters which I do and am fairly certain there isn't an error there.

Public Function AddNewToList(NewData As String, stTable As String, _
stFieldName As String, strPlural As String, _
Optional strNewForm As String) As Integer
On Error GoTo err_proc
'Adds a new record to a drop down box list
'If form name passed, then open this form to the newly created record
'Declare variables
Dim rst As DAO.Recordset
Dim IntNewID As Long
Dim strPKField As String
Dim strMessage As String

' Display message box asking if user wants to add the new item
strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
"Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
"(Please check the entry before proceeding)."
If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
rst.AddNew
rst(stFieldName) = NewData 'Add new data from combo box
strPKField = rst(0).Name 'Find name of Primary Key (ID) Field
rst.Update
rst.Move 0, rst.LastModified
IntNewID = rst(strPKField)
'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

AddNewToList = acDataErrAdded 'Set response 'Data added'
Else
AddNewToList = acDataErrContinue 'Set response 'Data NOT added'
End If

exit_proc:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
err_proc:
MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
Resume exit_proc
End Function
 

MickJav

AWF VIP
Local time
Today, 22:07
Joined
Nov 28, 2005
Messages
1,784
You could give this a try.
 

theDBguy

I’m here to help
Local time
Today, 14:07
Joined
Oct 29, 2018
Messages
10,001
Its like you knew.... Yes you are correct. I am using the after update to accomplish the task of adding the selected email to the text box(collection of emails) Also, I want the newly added email to go there as well. I haven't coded it yet as I wanted to get past this speedbump first.

Private Sub cbonewEmail_AfterUpdate()
Me.txtEmailStr = Me.txtEmailStr & ";" & cbonewEmail.Column(0)
Me.Refresh
End Sub

Sub for 'not in list' is some code I found that defines a function for adding the new entry to the table. You just call it and put in the parameters which I do and am fairly certain there isn't an error there.

Public Function AddNewToList(NewData As String, stTable As String, _
stFieldName As String, strPlural As String, _
Optional strNewForm As String) As Integer
On Error GoTo err_proc
'Adds a new record to a drop down box list
'If form name passed, then open this form to the newly created record
'Declare variables
Dim rst As DAO.Recordset
Dim IntNewID As Long
Dim strPKField As String
Dim strMessage As String

' Display message box asking if user wants to add the new item
strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
"Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
"(Please check the entry before proceeding)."
If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
rst.AddNew
rst(stFieldName) = NewData 'Add new data from combo box
strPKField = rst(0).Name 'Find name of Primary Key (ID) Field
rst.Update
rst.Move 0, rst.LastModified
IntNewID = rst(strPKField)
'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

AddNewToList = acDataErrAdded 'Set response 'Data added'
Else
AddNewToList = acDataErrContinue 'Set response 'Data NOT added'
End If

exit_proc:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
err_proc:
MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
Resume exit_proc
End Function
Hi. Thanks! But just to be safe, can you also please post the actual code in the NotInList event of the combo that shows it calling the function you just posted? Cheers!
 

gakiss2

Registered User.
Local time
Today, 14:07
Joined
Nov 21, 2018
Messages
124
You could give this a try.

I already have that part. I can successfully add the new item to the rowsource table. The issue is the behavior after that occurs. The form goes into new record mode rather than adding the new item to another text box.
 

gakiss2

Registered User.
Local time
Today, 14:07
Joined
Nov 21, 2018
Messages
124
Hi. Thanks! But just to be safe, can you also please post the actual code in the NotInList event of the combo that shows it calling the function you just posted? Cheers!
I also threw in code to requery on GotFocus event.
Code:
Private Sub cbonewEmail_GotFocus()
Me.cbonewEmail.Requery
End Sub

Private Sub cbonewEmail_NotInList(NewData As String, Response As Integer)

Response = AddNewToList(NewData, "tblcontacts", "Other", "Email List")

End Sub
 

theDBguy

I’m here to help
Local time
Today, 14:07
Joined
Oct 29, 2018
Messages
10,001
I also threw in code to requery on GotFocus event.
Code:
Private Sub cbonewEmail_GotFocus()
Me.cbonewEmail.Requery
End Sub

Private Sub cbonewEmail_NotInList(NewData As String, Response As Integer)

Response = AddNewToList(NewData, "tblcontacts", "Other", "Email List")

End Sub
Okay, thanks again. Unfortunately, I don't see anything obvious in those code to make the form go to a new record. The closest suspect would be the Me.Refresh line, but I don't expect it to cause that behavior.
 

MickJav

AWF VIP
Local time
Today, 22:07
Joined
Nov 28, 2005
Messages
1,784
Can you post the code in post 3 with code tags it's a lot easier to read

Q: Is the cmbo last in the tab order
Q: How is the cycle set I.E. All Records, Current Record Etc
 

gakiss2

Registered User.
Local time
Today, 14:07
Joined
Nov 21, 2018
Messages
124
Can you post the code in post 3 with code tags it's a lot easier to read

Q: Is the cmbo last in the tab order
Q: How is the cycle set I.E. All Records, Current Record Etc
Sorry, getting used to using code tags

Code:
Private Sub cbonewEmail_GotFocus()
Me.cbonewEmail.Requery
End Sub

Private Sub cbonewEmail_NotInList(NewData As String, Response As Integer)

Response = AddNewToList(NewData, "tblcontacts", "Other", "Email List")

End Sub




Private Sub CboSQEName_AfterUpdate()
Call AddHistory(CboSQEName, "SQE Assigned")
End Sub
Better?

Q: Is the cmbo last in the tab order No - You're a GENIUS . changed it and that fixed the problem.
Q: How is the cycle set I.E. All Records, Current Record Etc - Guessing this doesn't matter now and I don't know.
 

theDBguy

I’m here to help
Local time
Today, 14:07
Joined
Oct 29, 2018
Messages
10,001
Sorry, getting used to using code tags

Code:
Private Sub cbonewEmail_GotFocus()
Me.cbonewEmail.Requery
End Sub

Private Sub cbonewEmail_NotInList(NewData As String, Response As Integer)

Response = AddNewToList(NewData, "tblcontacts", "Other", "Email List")

End Sub




Private Sub CboSQEName_AfterUpdate()
Call AddHistory(CboSQEName, "SQE Assigned")
End Sub
Better?

Q: Is the cmbo last in the tab order No - You're a GENIUS . changed it and that fixed the problem.
Q: How is the cycle set I.E. All Records, Current Record Etc - Guessing this doesn't matter now and I don't know.
Hi. Glad to hear you got it sorted out. I guess you were saying the combo was the "last" one in the tab order, so you changed it to "not be the last one," correct? Good luck with your project.
 

gakiss2

Registered User.
Local time
Today, 14:07
Joined
Nov 21, 2018
Messages
124
Hi. Glad to hear you got it sorted out. I guess you were saying the combo was the "last" one in the tab order, so you changed it to "not be the last one," correct? Good luck with your project.
sorry for late reply. After getting that irritating issue fixed I was of to the races making up for lost time. Yes, just making it not last helped a lot.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom