none
Access 2013: Lookup Field Troubles RRS feed

  • Question

  • I have a "Cities" table that is tied to a Lookup Field in another table. 

    This Lookup Field works great so long as I am trying to look up cities already populated in the "Cities" table, however I would like to be able to enter a non-populated city name into the Lookup Field and have it work backwards to populate the "Cities" table with a new record.

    So for example, I have "London", "NYC", and "Berlin" as records within the "Cities" Table. However, from the lookup field I want to add "Osaka" and have that then populate the "Cities" Table for easy lookup from that same field later.

    Does anyone know how to do this?

    Thursday, July 30, 2015 8:34 PM

Answers

  • On a form where you use the lookup field, create a not-in-list-event like:

    Private Sub City_NotInList(NewData As String, Response As Integer)
    On Error GoTo Fout
    
    If MsgBox("No such city." & vbLf & vbLf & "Add it?", vbYesNo, "Unknown") = vbYes Then
        Response = acDataErrAdded
        DoCmd.RunSQL "INSERT INTO tblCities VALUES('" & NewData & "')"
    Else
        Response = acDataErrContinue
        Me.City.Undo
    End If
    
    Einde:
        Exit Sub
    
    Fout:
        MsgBox Err.Description
        Resume Einde
    
    End Sub


    Groeten, Peter http://access.xps350.com/

    Friday, July 31, 2015 6:16 AM
  • Exactly this scenario is covered in the NotInList.zip demo in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Note that, because a Cities table will almost certainly have non-key columns such as the regional entity in which the city is located (e.g. US states, French départments etc, called Regions generically in my demo) the code in the NotInList event procedure in my demo does not transparently insert a row into the Cities table, but opens a form in dialogue mode in order that the region can be entered/ selected.  The NotInList event procedure for the region combo box in this form does insert a row transparently into Regions because Regions has no similar non-key columns, though it might do if the country in which the region is located where included.

    However, while my demo does use cities as one example of the use of the NotInList event procedure, this is actually a bad example.  City names can legitimately be duplicated (see https://en.wikipedia.org/wiki/List_of_the_most_common_U.S._place_names for examples in the USA).  City names are even duplicated within a single state, so city and state is not a candidate key.  A surrogate CityID primary key is really the only sensible solution.  Consequently, while the NotInList event procedure could be used to enter the first of the 41 Springfields say, it a cannot be used again for entering any of the other 40 as the name is no longer not in the list.  A solution to this is given in my demo in the case of personal names, which can also legitimately be duplicated, and frequently are, even in quite small groups of people (I was once present at a clinic where two patients arrived within minutes of each other, both female, both with the same first and last names and both with the same date of birth!).  This is addressed in the opening form of my demo which includes a command button to add a new contact.  This opens a form in dialogue mode to do so, and the new contact details are then passed back to the combo box in the opening form.

    BTW you might care to take a look at http://www.mvps.org/access/lookupfields.htm to see why the lookup field wizard is deprecated by most experienced developers and should be avoided like the plague.  Don't worry unduly about having used it already, however; it's not fatal, but I'd recommend not doing so in the future.  There is no need; it is very easy to create a referenced Cities table and then create an enforced relationship to a referencing table like Contacts in my demo.  The combo box for data entry can then be created in a form, which the control wizard will do for you, though it's a simple job to hand-craft it.

    Ken Sheridan, Stafford, England


    Friday, July 31, 2015 9:38 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Office, I'll move your question to the MSDN forum for Access

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, July 31, 2015 5:52 AM
  • On a form where you use the lookup field, create a not-in-list-event like:

    Private Sub City_NotInList(NewData As String, Response As Integer)
    On Error GoTo Fout
    
    If MsgBox("No such city." & vbLf & vbLf & "Add it?", vbYesNo, "Unknown") = vbYes Then
        Response = acDataErrAdded
        DoCmd.RunSQL "INSERT INTO tblCities VALUES('" & NewData & "')"
    Else
        Response = acDataErrContinue
        Me.City.Undo
    End If
    
    Einde:
        Exit Sub
    
    Fout:
        MsgBox Err.Description
        Resume Einde
    
    End Sub


    Groeten, Peter http://access.xps350.com/

    Friday, July 31, 2015 6:16 AM
  • Exactly this scenario is covered in the NotInList.zip demo in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Note that, because a Cities table will almost certainly have non-key columns such as the regional entity in which the city is located (e.g. US states, French départments etc, called Regions generically in my demo) the code in the NotInList event procedure in my demo does not transparently insert a row into the Cities table, but opens a form in dialogue mode in order that the region can be entered/ selected.  The NotInList event procedure for the region combo box in this form does insert a row transparently into Regions because Regions has no similar non-key columns, though it might do if the country in which the region is located where included.

    However, while my demo does use cities as one example of the use of the NotInList event procedure, this is actually a bad example.  City names can legitimately be duplicated (see https://en.wikipedia.org/wiki/List_of_the_most_common_U.S._place_names for examples in the USA).  City names are even duplicated within a single state, so city and state is not a candidate key.  A surrogate CityID primary key is really the only sensible solution.  Consequently, while the NotInList event procedure could be used to enter the first of the 41 Springfields say, it a cannot be used again for entering any of the other 40 as the name is no longer not in the list.  A solution to this is given in my demo in the case of personal names, which can also legitimately be duplicated, and frequently are, even in quite small groups of people (I was once present at a clinic where two patients arrived within minutes of each other, both female, both with the same first and last names and both with the same date of birth!).  This is addressed in the opening form of my demo which includes a command button to add a new contact.  This opens a form in dialogue mode to do so, and the new contact details are then passed back to the combo box in the opening form.

    BTW you might care to take a look at http://www.mvps.org/access/lookupfields.htm to see why the lookup field wizard is deprecated by most experienced developers and should be avoided like the plague.  Don't worry unduly about having used it already, however; it's not fatal, but I'd recommend not doing so in the future.  There is no need; it is very easy to create a referenced Cities table and then create an enforced relationship to a referencing table like Contacts in my demo.  The combo box for data entry can then be created in a form, which the control wizard will do for you, though it's a simple job to hand-craft it.

    Ken Sheridan, Stafford, England


    Friday, July 31, 2015 9:38 AM