Hi Georgina,
Depending on the size of your list, you could use a combo box, as long as
your list is not several thousand entries long. If you set the Auto Expand
property to Yes, the combo box will find the value as you type in the
characters.
An example is shown in the Northwind.mdb sample database. Open the Orders
form, and attempt to type in "B" and the list will find "Boston Crab Meat".
This example also shows you how to populate a textbox. It uses the DLookup
function to look up the Unit Price from the Products table. The use of domain
aggregrate functions (DLookup) is fine for single-user database, where the
data is on the local hard drive, but these functions can cause bottlenecks
when used in a multi-user database where the shared data is stored on a file
server.
There are other ways of doing this as well, without having to resort to
using a domain aggregrate function (DLookup). One method is to simply
include the postcode in the SQL statement that serves as the Row Source for
the combo box. So, for instance, you could use a statement similar to this:
SELECT CityID, City, PostCode FROM MyTable ORDER BY City, PostCode;
Set the combo box for (3) columns, but don't display the first one (CityID)
since this is the primary key. You may or may not want to display the
PostCode in the combo box. Displaying it will help to pick the correct one,
in the event that a city has more than one PostCode assigned. You can use the
Column Width's property to suppress displaying a column by setting it's width
to 0. So, for instance, if you decide to hide the postcode in the combo box,
you could set the following column widths:
0;1.5;0
This would make the second column selected in the SQL statement, City, 1.5"
in width. The first and the third columns would have zero width. Then, in the
After_Update event procedure for this combo box, instead of using the piggy
DLookup function, you could simply set the PostCode textbox as follows:
Private Sub cboCity_AfterUpdate()
On Error Goto ProcError
Me!txtPostCode = Me!cboCity.Column(2)
We use Column(2) to refer to the third column, since the columns are "zero
based" when using VBA code, ie. the first column selected, CityID, is
Column(0), the second column selected, City, is Column(1), and so forth.
If your list is very long, then you can use the idea shown in this
article--scroll to the topic titled "Combo and list box controls" to limit
the list to those towns that match the first two characters that you enter:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/ODC_CookbookCha pter8.asp
Tom
________________________________________
:
Australia Post enables you to download a list of all towns with their
corresponding postcode.
I would like to be able use this so that I can start typing the name of the
town and access will:
Finish typing the name (because it recognises the name); and
Enter the corresponding postcode into another field.
This prevents any incorrect spelling of towns and incorrect postcode.
I have looked everywhere for hints on how to do this...thought there may be
someone who's already done it and can let me in on their secret!
Many thanks
