Need lookup field on a form- 2007

[JR Hester]

Who moved my cheese? Access 2007 running on Win XP
In past versions I have used look up fields on data entry forms to select
from, existing data. I cannot find a way to accomplish this task in 2007. Can
someone point me in the right direction please.

I have an employee table with EmpID, Fname, Lname, etc;
Session table with SessionID,date, time, location, max number of participants;
Transaction table storing TrxID, sessionID, EmpID, and additional details
about registration and attendance

I need the data entry form to to include a employee field which will allow
me to type part of employee name then takle me to that employee. Once
selected, the EmpID will be stored in the Trx table.

There has to be away which I just have not found yet. In earlier versions
there was a lookup field type, but I have not found this in the 2007 version.

My whole application is dependent on making this look up process work. I
eagerly await your guidance and suggestions.

Thank you,

 

[Tom Wickerath]

Hi JR,

I can assure you that lookup fields are still present in Access 2007, but
they're also just as evil as they have been in any version of Access. See the
2nd Commandment of Access, here, for reasons why you might want to reconsider
implementing lookup fields at the table or query level:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm


What you need is a combo box on the form. Set the Row Source for the combo
box to a saved query (or SQL statement):

SELECT EmpID, Lname & ", " & Fname AS EmpName
FROM Employees
ORDER BY Lname, Fname

Set the following additional properties for the combo box:

Bound column: 1
Column count: 2
Column widths: 0; 1.25"
Control Source: EmpID <---from the Transaction table



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[JR Hester]

Tom,

Thanks for your quick response. I did stumble upon a solution shortly after
I posted. I have been fighting this battle for 5 weeks off and on. I used a
similiar route. And after thinking through the process, I was confusing two
ideas-- the table/query lookup process and the combo box approach for the
form. I was stubborn for several years about continuing to use the lookup
field in tables, I was fortunate taht I did not suffer teh problems
associated with this. Again most of my databases have been for my own use and
i was aware of the issues.

I think you clarified a misconception I have been working under. The
terminilogy of control source; I thought referred to where the data was
coming FROM, but actually refers to where the data will be STORED.

Thanks again for your post and getting me righted around. HAppy new year to
all!

 

[Tom Wickerath]

Hi JR,

I think you clarified a misconception I have been working under. The
terminilogy of control source; I thought referred to where the data was
coming FROM, but actually refers to where the data will be STORED.

Correct. Control Source refers to the field, usually a foreign key field,
where the data will be stored. The Row Source property controls where the
data comes from.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________