Ms Access Tip: Self Learning Combo Box

Database data entry can be tedious at times. You can make your databases more user friendly if they have a way to select data to enter rather than typing in the same data over and over. Access provides a form control called a combo box to help with this task but it can lead to trouble. Also, if you find that the data is redundant, you will probably want to be able to sort on that field in the future for reporting. The combo box allows the user to select inputs from a preset location like a table, a list of values, or a preset query. This works well unless the option you want to enter is not in the preset data. Access will not let you put in a new value unless you set the Limit to List option to No. Then, users are allowed to put in any data without checks. This can be troublesome. The value of having the combo box can be quickly diminished if users are misspelling data as they enter it or using different data to mean the same thing. This is very easily seen as you can define the United States as US, USA, United States, United States of America all meaning the same thing. Subsequent reporting would be inaccurate with misspelled input and dissimilar input for the same field.

To over come that problem, it would be handy to have code that asks the user to confirm a new entry into the table before they go ahead, just to make sure they entered what they meant to. This example shows you how to accomplish this with what I call a self learning combo box. Meaning, it learns new entries and adds them to the list of preset values, but requests a confirmation from the user if the entry is not already in the table. Use the following to create a simple form to demonstrate the self learning combo box and make it a feature you give your users in the future.

Create a new database and do the following steps:
Set references by adding the Microsoft DAO 3.6 Object Library to the current checked list. Do this by opening the VBA editor and clicking on Tools/References then checking the library from the list.

Create a table named contacts with these fields.
ConId as autonumber set as the primary key field
ConName as text
ConType as text
Fill in a line or two of test data before going on to the next step.

Design a very simple form for the example with one text box and two combo box controls on it.
Set the form’s data source to contacts
Set the control source for the text box as ConName and name it ConName on the Other Tab

Set the control source for the first combo box as ConType and name it xType
Set the Row Source to SELECT Contacts.ConType FROM Contacts GROUP BY Contacts.ConType;

Set the control source for the second combo box to ConType and name it Type
You can do this later if you want, but you can set the Format control for Visible to No for this combo box.

In Design View of the Form cut and copy in the following Form Code

Option Compare Database
Private Sub Type_NotInList(NewData As String, Response As Integer)
Me.Type.LimitToList = False
End Sub

Private Sub xType_AfterUpdate()
Dim Resp
MiSQL = "SELECT ConTypes.ConTypes FROM ConTypes WHERE (((ConTypes.ConTypes)='" & xType & "'));"
GetMiRSet ‘ calls the function GetMiRSet from a module
If MiRec.RecordCount = 0 Then
Resp = MsgBox("The Type entered '" & xType & "' is not in list. Do you wish to add? Press OK or CANCEL..", vbOKCancel, xType & " Not On List")
If Resp = 1 Then
MiSQL = "INSERT INTO ConTypes ( ConTypes ) SELECT '" & xType & "' AS x1;"
PutMiRSet ‘calls the function PutMiRSet from a module
MiSQL = "SELECT ConTypes.ConTypes FROM ConTypes WHERE (((ConTypes.ConTypes)='" & xType & "'));"
GetMiRSet
Me.Type.Requery
Me.Type = MiRec!ConTypes
Form.Refresh
Else
xType = Null
End If
Else
Me.Type = MiRec!ConTypes
Form.Refresh
End If
End Sub

Now close the form and save it.

Next you will create a handy new module you can use in any database to save a little work.
This code is handy in Access because it makes it a little quicker to work with Recordsets by predefining command options ahead of time. Using this module you can simply call GetMiRSet when you want data from a table and PutMiRSet when you want to put data into a table with VBA. The required input for both functions is the SQL string defined as MiSQL. It is needed to define the data set you want to use in the format like the following:

MiSQL = "INSERT INTO ConTacts( ConType ) SELECT '" & xType & "' AS x1;"

You should see a line similar to this in the above list of commands.

Hint: Use the New Query function to create your SQL statements. After you have designed them in Design View, change to SQL view and you will see the SQL string. Copy this and place it into the VBA editor to save some time. You may have to make some modification to the SQL string you copy after you put it into your VBA code on the form. Like in our example, you can see where we are using the xType control name like a variable in the string. You can’t do this inside the string quotes so you have to concatenate two strings together with the & symbol. Also, in this sql string, xType is a string, so if you want to put it into the statement, like all strings, it has to be inside quotes. But, since the string is in a string, use single quotes. It will help to put a debug.print MiSQL statement after this line to see how Access is going to interpret the string.

Now create a New Module and put the following lines into it and save it under any name.

Option Compare Database
Option Explicit
Public MiSQL As String, Midb As Database, MiRec As DAO.Recordset, MiRec2 As DAO.Recordset

Public Function GetMiRSet()
Set Midb = CurrentDb()
Set MiRec = Midb.OpenRecordset(MiSQL, dbOpenDynaset)
End Function

Public Function GetMiRSet2()
Set Midb = CurrentDb()
Set MiRec2 = Midb.OpenRecordset(MiSQL, dbOpenDynaset)
End Function

Public Function PutMiRSet()
Dim Midb As Database ', MiRec As DAO.Recordset
Set Midb = CurrentDb()
Midb.Execute MiSQL
End Function

You are done. Go back to the form and give it a try. Enter a new value in the text box and then enter a new value in the first combo box and you should get a message box asking you to confirm the entry.

You can find a working example database at http://www.biomationsystems.com/AccessTips.htm