Lookup wizard ID and number overide

[Guest]

I am a new user of Access 2000 but don't think this is a new user question,
so am posting here.
I have made a table with a list of schools, which I intended to a second
table to look and find.
In edit table of the second table, I name the field "Schools) and then use
the lookup wizard to set up the link in the data type column.
I select the first table and select field 1 which contains the school names.
It shows me the school names, asks me about resizing the column and naming
the lookup column.
Then my problems start:
I get a message saying that the table must be saved before the relationship
can be created, Yes or No (I say Yes) ... this may not be a problem in
itself, I suppose!
When I do, the field name (in the second table) is changed to ID from
"school" and the data type to Number.
I have tried starting again, remaking the tables, searching in help and on
the internet, but no joy.
What is the matter? Am I doing something wrong?

Looking forward to getting your advice and suggestions,

Catherine

 

[tina]

recommend you don't use Lookup fields in tables at all. they cause many more
problems than they're worth. see http://www.mvps.org/access/lookupfields.htm
for details. you don't "need" a lookup field in a table, because you don't
do data entry in tables; you do data entry in forms. in a form, you can use
a combo box control to show you a list of values from another table, that
you can choose from.

for your specific situation:

you have a table that is a list of schools. i'll describe it as

tblSchools
SchoolID (primary key)
SchoolName

you have another table, where you need to enter a specific school in each
record. since i have no idea what the table is about, i'll just describe it
as

tblMain
MainID (primary key)
fkSchoolID (foreign key from tblSchools)
other fields that describe a "main" record.

the relationship between the two tables is

tblSchools (parent) 1:n tblMain (child)

the two tables are linked on the "SchoolID/fkSchoolID" primary/foreign key
fields.

to enter data in tblMain, create a form with the RecordSource set to
tblMain. add a combo box control. set the following properties in the combo
box as

ControlSource: fkSchoolID
RowSourceType: Table/Query
RowSource: SELECT SchoolID, SchoolName FROM tblSchools ORDER BY
SchoolName (the RowSource all goes on one line)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
ListWidth: 1.25"
LimitToList: Yes

suggest you read up on the above combo box properties, so you'll understand
how they work. when you add a record in the form, and open the "droplist" in
the combo box, you'll *see* the list of schools to choose from. what will be
*saved* in the fkSchoolID field will be the value of the SchoolID primary
key field from tblSchools.

the above describes a correctly normalized table design and standard form
setup. if you're not familiary with the concepts of data modeling and
normalization, suggest you read up on the topic. see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

hth

 

[Guest]

Thanks for your help. I must say that I am sufficiently bemused by the
complexities of Access that it was a bit hard to follow all of it, but
creating a combo box in the form (which I did have too) certainly solved the
initial problem for me.
Thanks
Except now when I select an item from my combo list, it puts the same value
on every record form (replacing correct items) and then all disappear when
I close and reopen it.

Catherine

 

[tina]

sounds like your combo box control is unbound. see the combo box Properties
settings listed in my previous post, specifically the ControlSource property
at the top of the list.

hth

 

[Guest]

Tina,

I was having the same problem as CatherineNorth and followed your advice.

Do you have any ideas about the following?
Using your same example, if you change the BoundColumn to 2, it appears the
data is recorded correctly in the table with the contents from BoundColumn 2,
but the form shows the data of Column 1 from the combo list. I'm happy it's
correctly recording the data of column 2 (the lookup fields will not do this
no matter what you try) but it's hard for the users since the form shows the
content of column 1.

Using your example to kill this poor dead horse:
If the tblMain recorded SchoolName instead of fkSchoolID and the combo box
dispalys the 2 columns as you have, schoolId and schoolName, but I need to
record schoolName in tblMain...it inserts the data correctly but the form
shows column 1 in the combobox control. Any way to get it to show column 2?

Thanks!

 

[John Vinson]

Using your same example, if you change the BoundColumn to 2, it appears the
data is recorded correctly in the table with the contents from BoundColumn 2,
but the form shows the data of Column 1 from the combo list. I'm happy it's
correctly recording the data of column 2 (the lookup fields will not do this
no matter what you try) but it's hard for the users since the form shows the
content of column 1.

Using your example to kill this poor dead horse:
If the tblMain recorded SchoolName instead of fkSchoolID and the combo box
dispalys the 2 columns as you have, schoolId and schoolName, but I need to
record schoolName in tblMain...it inserts the data correctly but the form
shows column 1 in the combobox control. Any way to get it to show column 2?

A Combo Box will STORE the value in its Bound Column; when it's not
dropped down it will DISPLAY the first non-zero width column.

You can base the combo on a Query selecting the fields in any order,
and you can adjust the ColumnWidths property to display columns or not
as you wish, by setting the width of any column which should be
concealed (fkSchoolID for instance) to zero.

Generally your tblMain should NOT contain a field for the school name.
Instead, it should contain a numeric ID, linked to the primary key of
the School table. This numeric key will be smaller and more efficient,
and it protects you from problems if two schools (in different
districts presumably) happen to have the same name, and from school
name changes.

John W. Vinson[MVP]

 

[tina]

see John's reply in this thread.

Tina,

I was having the same problem as CatherineNorth and followed your advice.

Do you have any ideas about the following?
Using your same example, if you change the BoundColumn to 2, it appears the
data is recorded correctly in the table with the contents from BoundColumn 2,
but the form shows the data of Column 1 from the combo list. I'm happy it's
correctly recording the data of column 2 (the lookup fields will not do this
no matter what you try) but it's hard for the users since the form shows the
content of column 1.

Using your example to kill this poor dead horse:
If the tblMain recorded SchoolName instead of fkSchoolID and the combo box
dispalys the 2 columns as you have, schoolId and schoolName, but I need to
record schoolName in tblMain...it inserts the data correctly but the form
shows column 1 in the combobox control. Any way to get it to show column 2?

Thanks!