You still use a combo box control. The difference is that you create it and
place it on your form instead of having the combo box created for you by
default. The underlying principle is the same - you have two tables which
are related or linked based on a common field. Since I don't know the
details of your tables, lets go with a simple example - Customers and
Orders. In your Order form, you want to be sure that the Customer being
entered is already a valid customer that has been created on the Customer
table (via the customer form!). Custid, the primary key for the customer
table *could* be a "lookup field" on the Orders table (Created using the
lookup field wizard) or it could be a plain old numeric field which is
*related* to the Customers table. By *related*, I mean that you have add the
two tables to the relationships window and crated a relation between the
Custid fields in each by dragging the Custid field from Customers to the
Custid field in Orders. Even if you don't create the relationship (which you
should) the tables can still be related.
Now, assuming that Custid in the Orders table is a plain old numeric field,
we can either require our users to enter that numeric value into the Order
form (yuck!) or provide them a way to select one from a list (and thereby
also ensure that it is correct). Do this the old fashioned way by dragging a
combo control to the form, then if you have wizards enabled (the magic wand
button), the wizard will prompt you through the rest of it. If you opt out
of using the wizard, it still is not that difficult. The important
properties of the combo box are the following:
RowSource
BoundColumn
ColumnCount
ColumnWidths
I would suggest that you build the combo using the combo wizard then go back
and look at these properties to see how they are set for you. The end result
is essentially the same as it would be if you allowed Access to build the
control for you as the result of the field having been created using the
Lookup field wizard. The differenct is that you have more control over the
field, the control and you don't have other hidden things happening to your
design.
Now, you could dispense with all of the above and just delete the existing
control from your form(s) and then drag the field (which is set up using the
lookup wizard) back onto your form. Just be sure that you understand
everything that Access is doing on your behalf when you use a field created
with the "lookup wizard"
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
_Bigred said:
with that said, if I don't go with the lookup field (based on a table)
what would be the best way to ensure that a user inputting records only
inputs a keyword (probably 30-35 keywords) properly in this field.
I want to have the user input a keyword such as: overtime, discipline or
perhaps 230.36
but I want to make sure that they use MY desired keywords, and not
vary/deviate from them.
any ideas would be greatly appreciated,
_Bigred
Sandra Daigle said:
You have to manually convert those fields to combo boxes - which is
really the way to go anyway.
The "lookup field" concept is really just a way that Access allows you to
modify the display control for a field. Though it can be useful it also
causes some misunderstandings (such as yours) and it creates some
somewhat undesirable side effects. For more information see:
http://www.mvps.org/access/tencommandments.htm
http://www.mvps.org/access/lookupfields.htm
Just to be clear - the foreign key field itself is not the "evil lookup
field". It is essential in a relation database. It is the use of the
display control Combo box feature in the table designer and lookup field
wizard that create problems.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
_Bigred wrote:
(Access 2000)
I have several forms and ultimately I have just changed a field "Topics"
from a text field in the main table , to a lookup field based on a
"topics" table.
I opened up a couple of my pre-existing forms, and that lookup field
doesn't seem to be on those forms?
What do I have to do to get that lookup field to give me the drop down
list of values, like it should?
TIA,
_Bigred
