Linking Tables with AutoNumber Primary Keys

[Guest]

In my database I have two tables: Jobs and Invoices.

In Jobs, I have the fields: Job No (Key), Invoice No and etc
In Invoices, I also have the fields: Job No, Invoice No (Key), and etc

Let me make it simple to assume that a 1 to 1 relationship between jobs and
invoices. I use autoNumber for both Job No and Invoice No, and the procedure
is:

Create new Job No, enter all other details except Invoice No. Then after
some days, I create a new Invoice by choosing a Job No from a combo box
(which is a query somehow, listing all the jobs that fit my requirement).
After choosing the Job No, Access says that "The value you entered isn't
valid for this field". I have already typed in the format of Job No (which is
J00000000) to that combo box, so I don't know why it says my format isn't
correct.

Another problem is when I choose the Job No from the new invoice, I want it
to update this auto-generated Invoice No to my corresponding Job No in the
Jobs table (to fill up a blank). I set a relationship (let's say one-to-one)
and checked "Cascade update records". Is that all I have to do? So far I
haven't been successful.

Thanks a lot for helping.

 

[Douglas J. Steele]

If Job No is an AutoNumber, then it doesn't have a J in front it:
AutoNumbers are strictly numeric (they're Long Integers).

You may have set a format to put the J in front, but that's a display
consideration only: it doesn't change the value at all.