combo boxes and data entry?

[Guest]

For keeping track of clients from about fifteen or sixteen countries I
created a separate table for Countries partly to speed up data entry, partly
to standardise different possible forms of certain country names. My data
entry form then has a cboCountries combo box from which to select. Problem is
that because the combo is really based on the (hidden) CountryID column
rather than the visible CountryName column, you can't use the combo box to
add new data (a new country not already in the underlying table). There must
be an obvious way around this, but I can't figure out what it is. We don't
add a new country all that often, so I don't really want one of those
standard lists where scrolling through dozens of countries is slower than
just typing the name in.

And this is in fact a more general question about combo boxes and data entry
that I'd like to sort out. How, if at all, can you use a combo box for data
entry as well as data selection? Or is that just not what they are for?

 

[Steve Schapel]

David,

Your last question first... Yes, data entry is primarily what comboboxes
are for.

So, in your case, I would assume that you have a CountryID field in the
Clients table, and the combobox on the form is bound to this field?

And if the combobox's Auto Expand property is set to Yes, then you don't
have to scroll down the list, just start typing the first few letters to
find the required country.

Mind you, in my opinion what you have done is unnecessarily complicated.
What is the purpose of the CountryID field in the Countries table?
The only purpose would be to ensure uniqueness of each record. But you
are not going to have the same country entered in there more than once
anyway, so the CountryName is already a unique field, so you might as
well drop the CountryID, and correspondingly use the CountryName in the
Clients table as well.

As for adding new countries to the list, you can use a little command
button on the form to open another form based on the Countries table, to
add a new country, or else there is code you can use on the combobox's
Not In List event, assuming that its Limit To List property is set to Yes.

However, in a case like this, I would not normally have a separate
Countries table in the first place. I would set the Row Source of the
combobox to the equivalent of this....
SELECT DISTINCT Country FROM Clients ORDER BY Country
And set the combobox's Limit To List property to No.
That way, the combobox will list all countries where you already have
clients. If you get a client form a new country, you can just enter it
in, and after that, this new country will also be in the list.

 

[Guest]

Its done by means of some code in the combo box's NotInList event procedure
which takes the value typed into the combo box and inserts a new row into the
Countries table like so:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new country to list?"

strSQL = "INSERT INTO Countries(CountryName) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

This assumes a table Countries with a text column CountryName and an
autonumber CountryID column.

A variation on this is where there are other columns in the referenced table
which need to have data entered. An example would be a Cities combo box
where when entering a new City you'd then want to assign it to a State. This
is done by opening a Cities form in dialog mode rather than executing an SQL
statement. The new city name is passed to the form as its OpenArgs property
and assigned as the form's CityName control's DefaultValue property in the
form's Open event procedure. When the cities form is closed code execution
then resumes in the NotInList event procedure. The code then checks to see
if the new city was in fact added (with a DLookup function call) and if so
sets the return value of the Response argument to the acDataErrAdded constant
to requery the combo box; if not it returns acDataErrContinue as the Response
argument and undoes the control.

Ken Sheridan
Stafford, England

 

[John Spencer]

Well, it works fairly well to use
SELECT DISTINCT Country FROM Clients ORDER BY Country
until you get a hundred thousand records or more and then the time to build the
list of 30 to 40 countries can take a while.

I usually use a table so I can keep the loading time down.

 

[Guest]

Thanks very much Steve. This is exactly the kind of guidance I was looking
for, with several good routes to take - especially the last one, which looks
as if it gets around the problem of repeat drop-down records that made me go
for the separate table in the first place.

I think I can now make things a good deal simpler for myself!

 

[Steve Schapel]

Hi John,

Thanks. Yes, I 100% agree in the case of large numbers of records. By
an accident of fate, I suppose, this does not apply to any of the
databases I have worked with.

 

[Guest]

Thanks for this Ken.

Steve's SELECT DISTINCT solution is exactly what I needed in the case of my
Countries combo box, but your code gives me the answer to my wider question.

 

[Guest]

How then do you control integrity? By having a Countries table and
enforcing referential integrity only a valid country name can be entered into
a referencing table, Without this there is nothing to stop United Kongdom or
United Stoats of America being entered in the referencing table. The real
life example of this sort of thing I always cite is a database I worked on in
which I found three versions of my own name as the author of technical
papers, two correct (missing the middle initial in one case) the other
incorrect (a V instead of a W as the middle initial. A table of Authors with
only one of me in it would have prevented this. I could have been entered
three times into Authors of course under different guises, but it would take
a really perverse user to do that!

I'd also argue that the principles of the referential model demand a
Countries table as Countries is an entity type and therefore should be
modelled by a table.

On the subject of surrogate versus natural keys Joe Celko collected together
a series of posts on this from the old CompuServe CASE forum back in 1997.
These can be obtained from:


http://community.netscape.com/n/pfx/forum.aspx?msg=19495.1&nav=messages&webtag=ws-msdevapps


Ken Sheridan
Stafford, England