Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add item to combo box using OnNotinList event dosen't work

Posted on 2003-12-10
11
Medium Priority
?
660 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:wawa2001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9915484
try this change:

from:

        Set db = CurrentDb
        Set rs = db.OpenRecordset("Airports", dbOpenDynaset)
        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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9915492
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
0
 
LVL 2

Expert Comment

by:PoppaGray
ID: 9917013
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:wawa2001
ID: 9919872
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
0
 
LVL 2

Expert Comment

by:PoppaGray
ID: 9920230
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.
0
 
LVL 2

Accepted Solution

by:
PoppaGray earned 375 total points
ID: 9920252
I just looked as Arthur's code to see why you would get a runtime error.  He has omitted the brackets that are required around the field names in the Insert clause. His code shoudl read as follows:

    Dim strSQL as String
    strSQL = "INSERT into Airports (City_Code) values ('" & NewData & "')"
    Set db = CurrentDb
    db.Execute strSQL
    Response = acDataErrAdded

However, as I said, your original code worked fine for me.  :)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9920698
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
0
 
LVL 2

Expert Comment

by:PoppaGray
ID: 9920825
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9922421
ok, I stand corrected.  did not closely look at the if Err block.  

AW
0
 
LVL 2

Expert Comment

by:PoppaGray
ID: 9928711
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.
0
 

Author Comment

by:wawa2001
ID: 9962517
ok gyes thx for ur help both of u , sorry for being late i had problem with my connection
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question