wawa2001
asked on
Add item to combo box using OnNotinList event dosen't work
i have used the following code as an example to
adds the new entry to the underlying table and refreshes the available list
in the combo box but it does't work ( i'am using access 2002)
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Airport Name" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Airports ", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!City_code = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
adds the new entry to the underlying table and refreshes the available list
in the combo box but it does't work ( i'am using access 2002)
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Airport Name" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Airports
On Error Resume Next
rs.AddNew
rs!City_code = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
when you have added a new record, you need to set the Response parameter to acDataErrAdded. That was tha step that you were missing.
AW
AW
I suspect that you do not have LimitToList set to Yes. The NotInList event does not fire unless you do that. Your code appears that it would work OK if you set the LimitToList to Yes.
ASKER
i am already set have LimitToList set to Yes.
but i have arun time error in the my insert into statement after i mae the changes that AW had advice to me & i do not why
but i have arun time error in the my insert into statement after i mae the changes that AW had advice to me & i do not why
I just tested your original code with limittolist set to Yes and it worked fine. Try your original code that you submitted exactly as it was. Make sure you have the combo box LimitToList property set to yes and it should work - it does for me in a simple test form I threw up this morning.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the original code DOES NOT set Response=asDataErrAdded in the case when the user repsonds "YES" to the message box question to add the new name. In that case, the ComboBox is NOT going to be automatically requeried, and it will appear that the name has NOT been added, when in fact, then new name HAS BEEN added to the underlying table, but it will not show up in the combobox.
AW
AW
Sorry, Arthur, I disagree. The original code DOES set Response according to whether there was an error or not. Assuming the update worked OK, Response is set to acDataErrAdded. I tried a test program that works like a charm. Here is the code. The only change I made to the original code was the actual field name (which in my case was "Airport"):
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Airport Name" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Airports ", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!airport = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Airport Name" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Airports
On Error Resume Next
rs.AddNew
rs!airport = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
ok, I stand corrected. did not closely look at the if Err block.
AW
AW
WAWA2001, you should really accept an answer in this thread if it solved your problem. You may find that folk will not rush to answer your question(s) if you do not finalise them when you get a solution that works. I know my solution works, as I took the trouble to create a test harness and ensure that what I was advising would be a solution for you. It would be appreciated if you completed your end of the bargain and Accepted the posting that solved your problem. Cheers! --- P.
ASKER
ok gyes thx for ur help both of u , sorry for being late i had problem with my connection
from:
Set db = CurrentDb
Set rs = db.OpenRecordset("Airports
On Error Resume Next
rs.AddNew
rs!City_code = NewData
rs.Update
to:
Dim strSQL as String
strSQL = "INSERT into Airports City_Code values ('" & NewData & "')"
Set db = CurrentDb
db.Execute strSQL
Response = acDataErrAdded
AW