Lookup issue

[bonzie]

I'm having a bit of an issue with field input to lookup and change on it's own (Via table ref)

Basically i have a layout that creates new jobs. It asks various questions like:

- JobID (string)

- CustomerID (references another table)

- Description

- amount

The problem is with the customer id input. It was originally a pull down select using a list,

which for me works. But the users know all the customer codes (2 digit), and would prefer to be able to tab thru all the fields and just enter the code (faster for them)

Now the problem comes in here. The Job table has a column "CustomerID" which references Customer table. But the customer table has the following fields: CustomerID (sequence), ID (2 digit), Name (String

I'm having difficultly getting it to let them enter "84" (Customer::ID), and it will look that up, and set the Job::CustomerID) to say 8 (primary key/link column)

I tried using a calculated field, but that didn't seem to work.

Any suggestions? Thanks for any feedback.

[aldipalo]

Do you mean you are using a value list that points to the customer table to get the Job::fk_ID for the Job table? Is the relationship set Customer::ID -> Job::fk_ID?

If so, it shouldn't matter whether you enter the key from the keyboard or the drop down. Is the Customer::ID field the first field in the tab order?

If it is all the user need do is enter the ID number and the rest of the customer data should appear on the layout. (Provided, of course, there are other customer fields on the layout.) The JobID, if set as a serial number, should be there upon creation of the new record.

I'm also not clear what you mean by you want them to enter '84' in the Customer::ID field and get '8' in the Job::CustomerID field? How do you use this field and where are you expecting the field to get this number from?

You're naming convention may be causing you some grief.

If this is not what you need please explain further or post a clone of the file for us to look at.

hth

[bonzie]

I hope this helps.. I'm attaching 2 screenshots..

Basically I never use anything buy sequence based keys for the primary key in a table. Which is how all my tables are related. But the client wants to enter data based on a 2nd column (call it not id, but perhaps ident) .. but of course the application must convert that "ident" into the sequence number in order to establish the relationship.

Thanks for you help.

Edited July 21, 2008 by Guest

[aldipalo]

If I'm reading this correctly and understanding what you want to accomplish is put in the Customer::CustomerID (25) into the Job::CustomerID (25) and have the ID field populated (4) as well. Is that correct?

If so, the ID field on the Job layout should be Customer::ID not Job::ID based on your relationship.

You could also set up a lookup. See attached picture.

ID_Lookup.bmp

Edited July 21, 2008 by Guest