Browse All Articles > Access Tips & Tricks: The Smart Combo
Introduction
In a modern interface, the user expects all controls to be "smart". The interface should understand a user entry even if it doesn't conform to the obvious logic, if it contains a typo, or when it's a clever attempt to accelerate the process.
This article shows a method to make an Access combo box "smarter". The main example comes from a database using a French interface, and dealing with countries. It's been simplified to a single data table (Données), linked to a table of countries (Pays). Internally, the ISO code is used as key, but the interface normally shows the French country names in full.
However, the source of the data being entered is varied: documents in several languages, the Internet, other databases. Using a simple combo, the user is thus expected to translate on the fly: Uganda to Ouganda, Chad to Tchad, or even DZ to Algeria and HR to Croatia, depending on the source material.
So, if the database uses ISO codes and contains the English names, why can't the combo accept a valid ISO code, or an English name, even if the combo displays only French names. "GR" will work fine, it expands to "Grèce", close enough to "Greece". But "LK" should be accepted for "Sri Lanka" and "Korea" should be understood even if the French call them "Corée".
This article explains how to do that. The demo uses a French interface, but will accept English country names for data entry as well as ISO codes and even partial search strings. It's probably best to quickly try the demo before reading the code in detail.
If you are only interested in the concept, a very simple and rather artificial example is given first, also with a demo file. Its purpose is to demonstrate how a combo can be made "smarter" gradually.
Technical Overview
When the user does not select an element from the list, the event "not in list" is triggered, and can be used to implement the "smart" response. For this to happen the property "limit to list" must be set, which is the automatic setting when the key column is hidden. When that is the case, the combo in effect translates a key field (a number or the ISO country code) into something readable. While editing, the reverse occurs: the user selects a readable row from the list, and the combo stores the corresponding key value in the underlying field or in the control. Here, we want something that doesn't appear in the list to be translated to a key value nonetheless.
The obvious solutions to manage alternative entries, valid but not in the list, would be to replace the string supplied by the user with the correct French country name, or to write directly a new key value into the field, but that is not possible using an Access combo box. In fact, there are only three possible return values from that event: the entry has been added, it hasn't and the default message should be displayed (the default behaviour), or it hasn't and no message is needed (e.g. if a custom message has already been displayed). In the last two cases, the combo is dropped open, and the combo remains in editing mode until a valid choice is made or the editing is cancelled.
The solution is thus to create an entirely new source for the combo box. If the provided string is unambiguous, e.g. "United States", a single row is returned, translating that string to the ISO code "US". If there are several matches, e.g. "United", they are displayed in the combo by using the return value "not added to the list".
After data entry, the record source of the combo is reset to its original state, again translating correctly "US" to "États-Unis". The same should occur if the user presses the [Esc] key.
A potential problem is the effect of these manipulations on other instances of the same combo box, i.e. on other visible records in a continuous form or a datasheet. Luckily, since the temporary recordset exists only during the sequence of events ending with "after update", the form will not have time to redraw. This is true even when the combo box temporarily displays several potential matches. As long as the editing of that instance isn't complete, or undone, the form will not redraw.
A simple example
Let's imagine that some users at Northwind Traders make the following request: having to enter product categories on a daily basis, they would like to be able to enter just the category number instead of picking it in a list. Naturally, new employees still require the normal combo, and nobody memorized the number of rarely used categories.
In other words, they would like the regular combo box to be enhanced in order to accept numbers, but not only numbers. This can be achieved with a simple event handler.
The steps are simple: the user types '4', and tabs out of the combo. This triggers the "not in list" event, and the handler receives the number as "new data". After checking that there is indeed a category number four, the row source is changed to read "4;4", and the returned value (the "response") indicates that the value has been added to the list. The first '4' is the key field, the second replaces the category name.
Nothing has been added, naturally. Category four existed before, but the number wasn't one of the available choices. Now it's the only choice. Access continues processing the events, discovers that '4' is now indeed a valid entry from the list, and terminates the editing of the combo box, writing '4' in the underlying field or control.
This leaves the combo in poor shape: it's left with a single row, showing '4'. So the "after update" event must reset it to its original state:
Note that the event only handles numeric entries. Anything else will trigger the default generic message box, "the text you entered isn't an item in the list".
Although a bit artificial, such a simple enhancement can greatly improve productivity. After all, the user knows that 'Condiments' is the category number two, and the database "knows" it as well. Why shouldn't the combo box understand that as well?
After the success of the categories combo box, the users of Northwind Traders now want the same thing when entering an employee name. What's more, since it's a small team, they want to be able to use first names or last names interchangeably. This means we need to manage the case when the "new data" isn't numeric, and assume it must be a first name (note that if a last name is entered, the normal behaviour of the combo has already selected the employee, and the event isn't triggered).
The behaviour after a numeric entry is very similar. The only difference with the previous example is that a recordset is used instead of a value list.
For a non-numeric entry, for example "Steven", a new query is written to find all employees with that first name. The query is opened as a recordset, populated, and the number of records is examined. If there are none, the default message will pop up ("please select an item from the list"). If there is only one, Access is instructed that the name has been added. Indeed, the single record reads "5;'Steven'", so Access will store 5 (the employee number) in the control and proceed.
If more than one records are found, for example for "Robert", Access needs to know whether to store 7 or 11, the relevant employee numbers. Since all this happens in a combo box, the natural way to ask the user is to show both Roberts in the same place. Thus a new query is written to include the last names, and Access is instructed to "continue", meaning that the entry is rejected, but that no message is needed.
This creates a new problem, because the user can at that point choose to cancel by pressing the [Esc] key. We already know that the combo box must be restored in the "after update" event, the same thing must occur also in the "undo" event. To reset the combo after it has been assigned a temporary recordset, it is sufficient to set it to 'Nothing' (see the demo file or the next example).
Note that the "Case 0", when no employees are found with the supplied first name, isn't treated. This results in the default response 'acDataErrDisplay' -- display the default message. A custom message box would be more friendly, in which case the response parameter should be set to 'acDataErrContinue' -- do not display the default message.
As shown in these simple examples, there are two ways to build the temporary record: a value list or a recordset. Circumstances dictate which solution is preferable in each case.
A Realistic Event Handler
The following example is actually in use in a few databases, with great success. It's the French country name combo described in the introduction, and it demonstrates all aspects of the "smart combo": entry using the key field (in this case not a number but the ISO code), entry using a different column (the English name), and a fully functional multi-lingual search engine.
As explained above, three event handlers are needed: "not in list", "after update", and "undo". Of these, only the first is at all complicated, and that only because of the nature of the data. The basic idea, as stated, is to analyse the entry made by the user, and translate that to a new temporary record source for the combo.
The name of the combo is "cboPays".
The event handler calls one external custom function, QuoteSQL(), which basically adds quotes around the passed string. The function is of course included in the demo file.
The dummy Do/Loop is used to avoid a Goto instruction, for the benefit of the purists among us. "Exit Do" is functionally equivalent to a Goto in this context.
The first test is whether exactly two characters have been supplied. In that case, they are interpreted as an ISO code and the code is searched in the list of countries.
If that fails, the second test mimics "auto complete", but using the English column. If the user types "All", "Allemagne" is presented directly in the combo. If the user types "Ger", "Germany" would be presented in an English interface. Hence, "Ger" isn't treated as a search criteria.
Finally, if both the ISO test and the English "auto-complete" have failed, a full search is performed on both the French and the English columns.
All search methods result in a string containing a SQL query. The last step is to determine whether the query returns zero, one, or more records. If zero, a message is displayed (in French). If a single record is returned, it means that the entry was unambiguous, so the returned value is "data was added to the list"; however the list will simply contain the translation from whatever the user typed to the correct ISO code. If several matches are available, the returned value is "continue", meaning that the combo is dropped open to allow the selection of one matching country; both French and English names can be displayed.
In the last two cases (one or more matches), a temporary recordset is assigned to the combo box, based on the SQL string.
The other events are quite simple; both the "after update" and "undo" events can be simplified to a single line:
When the combo's recordset is set to Nothing, it will automatically regenerate a recordset based on the record source string. In other words, it implicitly performs a "requery".
More Ideas
As said earlier, another method to construct the temporary records is to use a value list (see the very first example). Even when building a multi-record set, it might be easier to use a value list, depending on the circumstances. The "add item" method can be put to good use to build such a list. Naturally, the "after update" and "undo" events should revert to a "table/query" record source when resetting the combo.
Incidentally, when entering "*" as search criteria, the result is a mixed list of both French and English country names. This would seem a valid simple solution to the same problem: displaying all names directly in the combo removes the need for any "smart code". However, this also means that some countries will be shown in English, depending on which comes first in alphabetical order.
The "smart combo" can look in several columns to find the desired record, but it could just as well look in a linked table of synonyms. This answers a general problem when dealing with natural languages: the database needs to enforce a single accepted wording or spelling, but the user might prefer to search by other more familiar synonymic designations.
Finally, note that the combo box can display a list of results matching a search criteria. This idea can easily be expanded for an unbound combo box used solely for that purpose: provide a quick search box. I have used this idea to create a search engine for botanical names. It's totally impractical to display a quarter of a million plant names in a combo, but once the user has provided at least a partial name or name elements, the combo box can be populated with potential matches.
The Demo Files
The attached files are Access databases (2000 format). The first is the simple example, and contains two forms, one with the Categories combo, the second with the Employees combo, each with a brief explanation on how to use them.
SmartCombo1.mdb
The extended realistic demo contains two tables, Données and Pays, and a single form, in French. It includes the code above as well as further code not presented here in detail. The form suggests some entries to try: partial names, ISO codes, and search strings. It might be useful to open the table Pays alongside when testing the combo.
SmartCombo2.mdb
Conclusion
Most examples found on the Internet dealing with the "not in list" event show various ways to add a new item to the source data. The idea here is to treat the "not in list" condition as a request for a "smart" search, and to use the combo box itself to display the results when needed.
I hope the idea of the "smart combo" and this sample code prove useful to other Access developers.
Markus Fischer
(°v°)
about unrestricted access
In a modern interface, the user expects all controls to be "smart". The interface should understand a user entry even if it doesn't conform to the obvious logic, if it contains a typo, or when it's a clever attempt to accelerate the process.
This article shows a method to make an Access combo box "smarter". The main example comes from a database using a French interface, and dealing with countries. It's been simplified to a single data table (Données), linked to a table of countries (Pays). Internally, the ISO code is used as key, but the interface normally shows the French country names in full.
However, the source of the data being entered is varied: documents in several languages, the Internet, other databases. Using a simple combo, the user is thus expected to translate on the fly: Uganda to Ouganda, Chad to Tchad, or even DZ to Algeria and HR to Croatia, depending on the source material.
So, if the database uses ISO codes and contains the English names, why can't the combo accept a valid ISO code, or an English name, even if the combo displays only French names. "GR" will work fine, it expands to "Grèce", close enough to "Greece". But "LK" should be accepted for "Sri Lanka" and "Korea" should be understood even if the French call them "Corée".
This article explains how to do that. The demo uses a French interface, but will accept English country names for data entry as well as ISO codes and even partial search strings. It's probably best to quickly try the demo before reading the code in detail.
If you are only interested in the concept, a very simple and rather artificial example is given first, also with a demo file. Its purpose is to demonstrate how a combo can be made "smarter" gradually.
Technical Overview
When the user does not select an element from the list, the event "not in list" is triggered, and can be used to implement the "smart" response. For this to happen the property "limit to list" must be set, which is the automatic setting when the key column is hidden. When that is the case, the combo in effect translates a key field (a number or the ISO country code) into something readable. While editing, the reverse occurs: the user selects a readable row from the list, and the combo stores the corresponding key value in the underlying field or in the control. Here, we want something that doesn't appear in the list to be translated to a key value nonetheless.
The obvious solutions to manage alternative entries, valid but not in the list, would be to replace the string supplied by the user with the correct French country name, or to write directly a new key value into the field, but that is not possible using an Access combo box. In fact, there are only three possible return values from that event: the entry has been added, it hasn't and the default message should be displayed (the default behaviour), or it hasn't and no message is needed (e.g. if a custom message has already been displayed). In the last two cases, the combo is dropped open, and the combo remains in editing mode until a valid choice is made or the editing is cancelled.
The solution is thus to create an entirely new source for the combo box. If the provided string is unambiguous, e.g. "United States", a single row is returned, translating that string to the ISO code "US". If there are several matches, e.g. "United", they are displayed in the combo by using the return value "not added to the list".
After data entry, the record source of the combo is reset to its original state, again translating correctly "US" to "États-Unis". The same should occur if the user presses the [Esc] key.
A potential problem is the effect of these manipulations on other instances of the same combo box, i.e. on other visible records in a continuous form or a datasheet. Luckily, since the temporary recordset exists only during the sequence of events ending with "after update", the form will not have time to redraw. This is true even when the combo box temporarily displays several potential matches. As long as the editing of that instance isn't complete, or undone, the form will not redraw.
A simple example
Let's imagine that some users at Northwind Traders make the following request: having to enter product categories on a daily basis, they would like to be able to enter just the category number instead of picking it in a list. Naturally, new employees still require the normal combo, and nobody memorized the number of rarely used categories.
In other words, they would like the regular combo box to be enhanced in order to accept numbers, but not only numbers. This can be achieved with a simple event handler.
Private Sub cboCategory_NotInList(NewData As String, Response As Integer)
' check for a numeric entry
If IsNumeric(NewData) Then
' check whether a category exists with that number
If IsNull(DLookup("ID", "Categories", "ID=" & NewData)) Then
' failure: display a custom message
MsgBox "This Category doesn't exist"
Response = acDataErrContinue
Else
' success: write a special temporary row source
cboCategory.RowSourceType = "Value List"
cboCategory.RowSource = NewData & ";" & NewData
Response = acDataErrAdded
End If
End If
End SubThe steps are simple: the user types '4', and tabs out of the combo. This triggers the "not in list" event, and the handler receives the number as "new data". After checking that there is indeed a category number four, the row source is changed to read "4;4", and the returned value (the "response") indicates that the value has been added to the list. The first '4' is the key field, the second replaces the category name.
Nothing has been added, naturally. Category four existed before, but the number wasn't one of the available choices. Now it's the only choice. Access continues processing the events, discovers that '4' is now indeed a valid entry from the list, and terminates the editing of the combo box, writing '4' in the underlying field or control.
This leaves the combo in poor shape: it's left with a single row, showing '4'. So the "after update" event must reset it to its original state:
Private Sub cboCategory_AfterUpdate()
' if the combo was modified, reset it:
If cboCategory.RowSourceType = "Value List" Then
cboCategory.RowSourceType = "Table/Query"
cboCategory.RowSource = "Categories"
End If
End SubNote that the event only handles numeric entries. Anything else will trigger the default generic message box, "the text you entered isn't an item in the list".
Although a bit artificial, such a simple enhancement can greatly improve productivity. After all, the user knows that 'Condiments' is the category number two, and the database "knows" it as well. Why shouldn't the combo box understand that as well?
After the success of the categories combo box, the users of Northwind Traders now want the same thing when entering an employee name. What's more, since it's a small team, they want to be able to use first names or last names interchangeably. This means we need to manage the case when the "new data" isn't numeric, and assume it must be a first name (note that if a last name is entered, the normal behaviour of the combo has already selected the employee, and the event isn't triggered).
Private Sub cboEmployee_NotInList(NewData As String, Response As Integer)
Dim recTemp As DAO.Recordset
Dim strSQL As String
If IsNumeric(NewData) Then
' for a numeric entry, try to find that employee number
If IsNull(DLookup("ID", "Employees", "ID = " & NewData)) Then
' failure: display a custom error message
MsgBox "There is no employee with that number"
Response = acDataErrContinue
Else
' success: build a dummy recordset with one single record
' (both columns simply contain the employee number)
strSQL = "SELECT " & NewData & "," & NewData
Set recTemp = CurrentDb.OpenRecordset(strSQL)
Set cboEmployee.Recordset = recTemp
Response = acDataErrAdded
End If
Else
' not numeric: assume it's a first name
' let's build a recordset of all matching employees
strSQL _
= " SELECT ID, FirstName" _
& " FROM Employees" _
& " WHERE FirstName = '" & NewData & "'"
Set recTemp = CurrentDb.OpenRecordset(strSQL)
If recTemp.RecordCount Then recTemp.MoveLast
' examine the returned records
Select Case recTemp.RecordCount
Case 0
' no match, the default message will be displayed
Exit Sub
Case 1
' single match, already added to the temporary recordset
Response = acDataErrAdded
Case Else
' multiple matches: the user will have to select one
' let's write a filtered query of employees
' (but no message is needed)
strSQL _
= " SELECT ID, FirstName+' '+LastName, ID" _
& " FROM Employees" _
& " WHERE FirstName = '" & NewData & "'" _
& " ORDER BY LastName"
Set recTemp = CurrentDb.OpenRecordset(strSQL)
Response = acDataErrContinue
End Select
' assign the temporary record to the combo
Set cboEmployee.Recordset = recTemp
End If
End SubThe behaviour after a numeric entry is very similar. The only difference with the previous example is that a recordset is used instead of a value list.
For a non-numeric entry, for example "Steven", a new query is written to find all employees with that first name. The query is opened as a recordset, populated, and the number of records is examined. If there are none, the default message will pop up ("please select an item from the list"). If there is only one, Access is instructed that the name has been added. Indeed, the single record reads "5;'Steven'", so Access will store 5 (the employee number) in the control and proceed.
If more than one records are found, for example for "Robert", Access needs to know whether to store 7 or 11, the relevant employee numbers. Since all this happens in a combo box, the natural way to ask the user is to show both Roberts in the same place. Thus a new query is written to include the last names, and Access is instructed to "continue", meaning that the entry is rejected, but that no message is needed.
This creates a new problem, because the user can at that point choose to cancel by pressing the [Esc] key. We already know that the combo box must be restored in the "after update" event, the same thing must occur also in the "undo" event. To reset the combo after it has been assigned a temporary recordset, it is sufficient to set it to 'Nothing' (see the demo file or the next example).
Note that the "Case 0", when no employees are found with the supplied first name, isn't treated. This results in the default response 'acDataErrDisplay' -- display the default message. A custom message box would be more friendly, in which case the response parameter should be set to 'acDataErrContinue' -- do not display the default message.
As shown in these simple examples, there are two ways to build the temporary record: a value list or a recordset. Circumstances dictate which solution is preferable in each case.
A Realistic Event Handler
The following example is actually in use in a few databases, with great success. It's the French country name combo described in the introduction, and it demonstrates all aspects of the "smart combo": entry using the key field (in this case not a number but the ISO code), entry using a different column (the English name), and a fully functional multi-lingual search engine.
As explained above, three event handlers are needed: "not in list", "after update", and "undo". Of these, only the first is at all complicated, and that only because of the nature of the data. The basic idea, as stated, is to analyse the entry made by the user, and translate that to a new temporary record source for the combo.
The name of the combo is "cboPays".
Private Sub cboPays_NotInList(strNewData As String, intResponse As Integer)
'
' Attempt to resolve the entered data into a country name, or a list
' of country names
Dim strCrit As String
Dim strSQL As String
Dim rec As DAO.Recordset
' The user typed something else than a French country name:
' open a recordset on the source table and try to find a match.
Set rec = CurrentDb("Pays").OpenRecordset(dbOpenDynaset)
Do ' dummy loop
' key field search: ISO country code
If Len(strNewData) = 2 Then
strCrit = "ISO = " & QuoteSQL(strNewData)
rec.FindFirst strCrit
If Not rec.NoMatch Then
' select single record
strSQL = "SELECT ISO From Pays WHERE " & strCrit
Exit Do
End If
End If
' alternate full name search: English names
If Not strNewData Like "*[[*?]*" Then
' no wildcard was provided
strCrit = "English Like " & QuoteSQL(strNewData & "*")
rec.FindFirst strCrit
If Not rec.NoMatch Then
' select matching English names
strSQL _
= " SELECT ISO, English FROM Pays" _
& " WHERE " & strCrit _
& " ORDER BY English"
Exit Do
End If
End If
' full search in both languages
If strNewData Like "*[[*?]*" Then
' user entered wildcards
strCrit = QuoteSQL(strNewData)
Else
' provide wildcards
strCrit = QuoteSQL("*" & strNewData & "*")
End If
' select all matching names from both columns
strSQL _
= " SELECT ISO, Français" _
& " FROM Pays" _
& " WHERE Français Like " & strCrit _
& " UNION SELECT ISO, English" _
& " FROM Pays" _
& " WHERE English Like " & strCrit _
& " ORDER BY 2"
Loop While False
' At this point, we have a query in strSQL;
' let's open it and check the record count.
Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rec.RecordCount Then rec.MoveLast
Select Case rec.RecordCount
Case 0
' no match found; manage error condition
intResponse = acDataErrDisplay
' better, custom message:
MsgBox "CUSTOM ERROR MESSAGE:" _
& vbCr _
& vbCr & "Aucun pays trouvé pour le critère :" _
& vbCr & strCrit _
& vbCr & "Veuillez choisir un pays dans la liste."
intResponse = acDataErrContinue
' done here (combo's recordset untouched)
Exit Sub
Case 1
' auto-accept the single matching record
strSQL = "SELECT " & QuoteSQL(rec!ISO) & "," & QuoteSQL(strNewData)
Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
intResponse = acDataErrAdded
Case Else
' let the user pick one matching record
intResponse = acDataErrContinue
End Select
' temporarily change the combo's recordset
Set cboPays.Recordset = rec
mfReset = True
End SubThe event handler calls one external custom function, QuoteSQL(), which basically adds quotes around the passed string. The function is of course included in the demo file.
The dummy Do/Loop is used to avoid a Goto instruction, for the benefit of the purists among us. "Exit Do" is functionally equivalent to a Goto in this context.
The first test is whether exactly two characters have been supplied. In that case, they are interpreted as an ISO code and the code is searched in the list of countries.
If that fails, the second test mimics "auto complete", but using the English column. If the user types "All", "Allemagne" is presented directly in the combo. If the user types "Ger", "Germany" would be presented in an English interface. Hence, "Ger" isn't treated as a search criteria.
Finally, if both the ISO test and the English "auto-complete" have failed, a full search is performed on both the French and the English columns.
All search methods result in a string containing a SQL query. The last step is to determine whether the query returns zero, one, or more records. If zero, a message is displayed (in French). If a single record is returned, it means that the entry was unambiguous, so the returned value is "data was added to the list"; however the list will simply contain the translation from whatever the user typed to the correct ISO code. If several matches are available, the returned value is "continue", meaning that the combo is dropped open to allow the selection of one matching country; both French and English names can be displayed.
In the last two cases (one or more matches), a temporary recordset is assigned to the combo box, based on the SQL string.
The other events are quite simple; both the "after update" and "undo" events can be simplified to a single line:
Set cboPays.Recordset = NothingWhen the combo's recordset is set to Nothing, it will automatically regenerate a recordset based on the record source string. In other words, it implicitly performs a "requery".
More Ideas
As said earlier, another method to construct the temporary records is to use a value list (see the very first example). Even when building a multi-record set, it might be easier to use a value list, depending on the circumstances. The "add item" method can be put to good use to build such a list. Naturally, the "after update" and "undo" events should revert to a "table/query" record source when resetting the combo.
Incidentally, when entering "*" as search criteria, the result is a mixed list of both French and English country names. This would seem a valid simple solution to the same problem: displaying all names directly in the combo removes the need for any "smart code". However, this also means that some countries will be shown in English, depending on which comes first in alphabetical order.
The "smart combo" can look in several columns to find the desired record, but it could just as well look in a linked table of synonyms. This answers a general problem when dealing with natural languages: the database needs to enforce a single accepted wording or spelling, but the user might prefer to search by other more familiar synonymic designations.
Finally, note that the combo box can display a list of results matching a search criteria. This idea can easily be expanded for an unbound combo box used solely for that purpose: provide a quick search box. I have used this idea to create a search engine for botanical names. It's totally impractical to display a quarter of a million plant names in a combo, but once the user has provided at least a partial name or name elements, the combo box can be populated with potential matches.
The Demo Files
The attached files are Access databases (2000 format). The first is the simple example, and contains two forms, one with the Categories combo, the second with the Employees combo, each with a brief explanation on how to use them.
SmartCombo1.mdb
The extended realistic demo contains two tables, Données and Pays, and a single form, in French. It includes the code above as well as further code not presented here in detail. The form suggests some entries to try: partial names, ISO codes, and search strings. It might be useful to open the table Pays alongside when testing the combo.
SmartCombo2.mdb
Conclusion
Most examples found on the Internet dealing with the "not in list" event show various ways to add a new item to the source data. The idea here is to treat the "not in list" condition as a request for a "smart" search, and to use the combo box itself to display the results when needed.
I hope the idea of the "smart combo" and this sample code prove useful to other Access developers.
Markus Fischer
(°v°)
about unrestricted access
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
Author
Commented:Additional Demo File
There has been a question today about using the “smart combo” technique in a more traditional setting, namely the the addition of a new record from the “not in list” event.
The idea is that the user can type “Baggins, Bilbo” in the combo box and that this name is added to the list feeding the combo box. This is straightforward. However, the user might forget the space as in “Doe,John” or correct a typo in the dialogue box used to validate the new name. In both cases, the exact string entered in the combo will not match the new row added to the source.
This demo shows how to overcome the problem. If a new name is validated (if the user presses [OK] in the dialogue), the row source of the combo is rewritten so that whatever it currently contains is accepted and translated to the newly created record.
The user can basically just type “X” and tab out, fill in the last and first names in the dialogue box, and see the new name selected in the combo. This avoids many tedious checks for the programmer, and a few potential error messages for the user.
This demo file is in Access 2000 format, although it was developed in Access 2007.
Q-27776325.mdb
If you find it useful, please vote the article useful instead!
Markus G Fischer — (°v°)
Commented:
THANKS.