Easy one. Auto completing in a combo box 1

[vanleurth]

Hi, Everybody

Some time ago I found a thread about how to enter a non existing text in a combo box row source automatically if the string doesn't exist. Unfortunately, I can figured out what were those keywords I use.

What I want is to auto complete the city in a combo box if the city already exist, if not then to add the new city string into the table the combobox is connected to.

Thanks,

V.

 

[Lightning]

Paste this code into the form's module, and amend field/table names as necessary

Option Compare Database
Option Explicit
'====================================================================
Originally written for Access 97 (ie DAO 3.6).
'The method updates a look-up list with only one field,
'and works directly from the "Not In List" event itself.
'====================================================================
Private Sub Company_NotInList(NewData As String, Response As Integer)
'This event procedure is called from the
'ComboBox's NotInList event

'The procedure adds the Company Name to the
'underlying table upon confirmation from the user

'does user want to add new value to list?
If MsgBox("Add " & NewData & " to list?", 33, "Company Name") = 1 Then
   
   'Inform Event Procedure we're handling the error
   Response = acDataErrAdded
   
   'Declare the database and Table
   Dim NewCompany As String
   Dim db As Database
   Dim TB As Recordset
   Set db = CurrentDb
   
   NewCompany = NewData
   'Open the required table:
   Set TB = db.OpenRecordset("tblCompany", dbOpenTable)
   
   'Prepare Table for new record being added
   TB.AddNew
   
   'Write Data to fields
   TB("Company") = NewCompany
   TB.Update
   TB.Close

Else
   'Cancel the event returning the user to
   'the combo box
   DoCmd.CancelEvent
   
   'inform the event procedure to ignore
   'errormessage
   Response = acDataErrContinue
End If

End Sub

If you are using Access 2000 or greater, add a reference to DAO 3.6 object library to your database.

HTH
Lightning