Limit to List unless user authorizes (1 Viewer)

[gschimek]

I have a form with a combo box that holds Cities. The row source is a Select statement that finds all the unique values that have already been entered into that table.

I'm trying to eliminate misspellings (New York, New Yirk, NewYork), so I'd like to limit the field to list values, and use the Not In List event to trigger a popup. I've got some code to display the popup and take the Yes/No result, but I don't know what to do after that. How do I say "OK, save the data the user typed into the Control Source Field." Basically, I want it to ignore the Limit to List parameter at that point and just save the new data.

Here's the beginning of my code:

Private Sub City_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.'
    strTmp = "Add '" & NewData & "' as a new city?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

 

[RainLover]

A Google search will give you a large choice of options.

Here is just one. http://allenbrowne.com/ser-27.html

You should also create a separate table to store these values and use that table as your lookup.

 

[gschimek]

I did do some Google searching, but all I found was solutions like you suggested, where a separate table is used. I've done that before and it works, but in this case, the database is already created and being used, and already has actual city names in that field. To use a separate table I would probably have to change the value of that field to the record ID of the new table. I really just want to be able to store the entered data into the field.

I'm not sure what to search for. I don't want to add the new entry to the row source. I also don't want to add it to a new table. I need to know what it's called to just save the data. Then I can search for solutions.

Any suggestions?

 

[Isskint]

You could probably 'fudge' something, but it would be 'messy'. An approach i have used when wanting to check data for mis spelling, is to apply Soundex values. Soundex, basically, assigns values to the letters and phonomes and gives a word a 'score' that you can use to check for possible mis spellings. It is fairly accurate, but complex in programming. Search Soundex Codes.

As RainLover pointed out, the best (standard) approach here is a seperate table with the city names. You would not need to change much, only the record source of the combobox IF you make the new table a single field table for the city name - no ID field needed as each City Name will be unique. You can even use a query to make the new table with a SELeCT DISTINCT of the current table. This way you can add the NewData to the CityNames table using SQL in the NotInList event.

 

[RainLover]

You could probably 'fudge' something, but it would be 'messy'. An approach i have used when wanting to check data for mis spelling, is to apply Soundex values. Soundex, basically, assigns values to the letters and phonomes and gives a word a 'score' that you can use to check for possible mis spellings. It is fairly accurate, but complex in programming. Search Soundex Codes.

As RainLover pointed out, the best (standard) approach here is a seperate table with the city names. You would not need to change much, only the record source of the combobox IF you make the new table a single field table for the city name - no ID field needed as each City Name will be unique. You can even use a query to make the new table with a SELeCT DISTINCT of the current table. This way you can add the NewData to the CityNames table using SQL in the NotInList event.

I basically agree.

Copy the unique Cities from the current Source to the new Table. This table would only has one Field being the Primary Key. And that is of course is "City"
I believe this is technically correct and should satisify normalisation practices.
I haven't tested it howerer if there is a current violation of the "Not on List" it would not affect your current records. The "Not on List" will only apply to new entries.
Fixing the current entreis is another matter. The approach would depend on the number of records.

 

[John Big Booty]

Please refrain from deleting threads once they have received a response, as it shows a great deal of disrespect for those who have taken the time to help you with your problem.

 

[gschimek]

Sorry, didn't mean to delete. It's amazing what a 2 year old can do when left unattended with a browser open.

Thanks for the replies everyone. I got it working with an additional one column table.