Using AutoNumber and LookUp

[charles]

I am a beginner using Access to design a simple database.

I have a table called "Teachers" and a field called teacherID, a primary Key
set to AutoNumber and a Format of \T00000 (Giving T00001, T00002 etc...)

I want to lookup this field in another table where teacherID is a foreign
Key.
I select "lookup..." and the wizard creates the link to the Teachers
table....
but every time I try to select a teacherID from the drop down list
I get an error telling me that I tried to enter text into a numeric field.

Can you tell me what I'm doing wrong and how to fix it.

Thanks


CharlesW

 

[Jonas]

I am a beginner using Access to design a simple database.

I have a table called "Teachers" and a field called teacherID, a primary Key
set to AutoNumber and a Format of \T00000 (Giving T00001, T00002 etc...)
Charles,

I'm trying to envision what you are doing. The linking fields have to
be of the same data type. I think that it is also necessary to set
the relationships between the tables.

 

[John W. Vinson]

I am a beginner using Access to design a simple database.

I have a table called "Teachers" and a field called teacherID, a primary Key
set to AutoNumber and a Format of \T00000 (Giving T00001, T00002 etc...)

The Format does NOT store a letter T in the table. It just *displays* a letter
T and the leading zeroes when you see it in the table datasheet.

I want to lookup this field in another table where teacherID is a foreign
Key.
I select "lookup..." and the wizard creates the link to the Teachers
table....
but every time I try to select a teacherID from the drop down list
I get an error telling me that I tried to enter text into a numeric field.

Can you tell me what I'm doing wrong and how to fix it.

First thing you're doing wrong is trying to assign meaning to an Autonumber
field. An autonumber has one purpose, and one purpose ONLY: to provide a
meaningless unique key. Autonumbers will have gaps; they can even become
random (for instance if you Replicate the database) - teacherID 31 might be
followed by teacherID -1824226514 and then by 937114045. Autonumbers cannot be
edited, and cannot at all easily be assigned a desired value (you'll need to
create a new table and run an Append query to do so).

Second, you're packing two disparate pieces of information into one field -
the (meaningless to Access) letter T and a number. Fields should have only one
meaning.

Third, you're using Microsoft's misleading, obnoxious, and all but useless
Lookup misfeature:
http://www.mvps.org/access/lookupfields.htm
gives a critique.

If you want a numeric teacher ID, don't use Autonumber; instead, use a Long
Integer, and (if need be) assign it automatically in a Form. Don't use table
datasheets for anything but design and debugging; they're very limited, and
not intended for data presentation or editing (though Microsoft is trying to
push that idea...). Or, use an autonumber, but keep it "under the hood" -
concealed from user view; on a Form you can use a combo box which stores the
numeric value, but displays the teacher's name to the user.

John W. Vinson [MVP]

 

[charles]

John

It seems crazy to me that I cant do, what should be, a simple task in
Access.

I was hoping to force users to enter the Primary keys
in the format of T00000, S00000, C00000 etc... in different tables.

I dont yet have the sophistication to write VBA.

Many thanks for your reply.


CharlesW

 

[John W. Vinson]

John

It seems crazy to me that I cant do, what should be, a simple task in
Access.

I was hoping to force users to enter the Primary keys
in the format of T00000, S00000, C00000 etc... in different tables.

That is BAD DESIGN.

You can do it, sure; use a Text (not Autonumber) datatype; use an Input Mask
such as

"\T00000"

and make the users type the number into a textbox, and punish them if they
type it wrong.

But why would you *WANT* to do this!?

Users should be able to select a teacher from a list of teacher's names and
positions. They should never need to search by a meaningless T00312 teacher
ID; they should *certainly* never need to enter a teacher ID into a table. If
you want to populate a teacher ID into a new table (class assignments, say),
you'll use one of the many tools Access provides - a Combo Box letting the
user select "Higgins, Henry" from the list of teachers; or a Subform.

I'm afraid you're focusing too much on a preconcieved notion of how primary
keys are used, and missing some much more powerful and user friendly tools.

Now I may be completely off base here... if you could explain *why* you feel
you need such a completely non-standard primary key, or more about what you're
trying to accomplish in a real-world sense and how you're doing it, we could
help more effectively.

John W. Vinson [MVP]