Lookup wizard on a formatted primary key (1 Viewer)

Hey guys,

So i have two tables, one being child to the other, first one is called "jobs" with a formatted primary key being job-0001, job-0002 etc...
The second table is "quotation" table, it has a field called jobs linked to the primary key of the "jobs" table with a lookup wizard established while creating the "quotation" table.

My issue is that when i'm filling up an entry in the quotation table and selecting the related job to its quotation, i'm getting the below message:

"The value you entered does not match the number data type in this column"

I know why this is happening since the primary key has letters in it and the lookup wizard is numerical.

Any way i can go around this issue and still get the result i want?

Yes this is quite a common problem. The first thing is only link your tables together with number values. Don't use anything with text in. Doing it this way allows you to take advantage of the autonumber feature of MS Access when you create new records. Now as to the problem of your custom ID numbers you just create those with a query. You can either use the unique ID from the record, or you can generate your own unique ID.

1. Get rid of the table level lookup. You don't need it. It is only a crutch for people who don't know how to write code or create queries.
2. Do not format the PK on the table. That just obfuscates the actual value. Format it on forms and reports.

Thank you guys! i formatted it in the forms to appear as i want it to and kept numerical in the tables.
It's always the simplest solution that works the best!

You're welcome.