Linking two tables with autoNumber Primary Keys

[Atr2]

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.

 

[Damian S]

Use a Main-form and subform. Have your Jobs on the main form, Invoices on
your subform, with the forms linked using JobNo. This will cause any Invoice
entered under the selected Job to have the JobNo appropriate to it.

Get this working and most of your issues will be solved. Reply again if you
need further help.

Regards

Damian.

ps: Cascade updates has no effect when you are dealing with an autonumber
field... it would be used for if your Primary Key was a text field and you
changed it from "ABC" to "XYZ" - cascade updates would propagate these
changes to existing records. Cascade updates has no bearing on new records,
only updating existing records.

 

[J. Goddard]

Hi -

First, I suggest you take Invoice No out of the Jobs table - you don't
need it, becaue you already have job No - Invoice no. in the invoices table.

You said in the header of the post that your Keys are autonumber, which
means that Job Number is numeric, which means that it must be numeric in
the invoices table as well, if it is to act as a Foreign Key. Why,
then, do you format it as J000000000 in the combo box? My guess is that
you are trying to put a character string - J00000000 - into a numeric
field - Job No. However, you could easily have the combo box display
J00000000, but have the numeric part as its bound value.

As for your second problem - see my comment above about taking the
invoice number out of the jobs table. In fact, by doing that you now
make the jobs to invoices relationship a one to many - which you will
need if you ever get big jobs which you bill in installments.

HTH

John

 

[Atr2]

Hi, thanks for the comments and reminder.

But even if I put nothing in the format of the Job No combo box, the same
error still occurs, I think perhaps it's because I'm using a value chosen
from another combo box to be the criteria for filtering this Job No combo
box, but I have no clue about the error.

My idea of putting the invoice no. in the jobs table is that when I first
add a job, the invoice no field in the Jobs table is blank. Then when I
create a new invoice relating to that particular job, the invoice no. field
will be filled up. So the result will be some Jobs having invoice no as blank
(when not yet billed) and those billed will have the invoice no appearing. So
that when I create new invoices, I can filter out all the Blank rows in Jobs
and let the user choose only jobs that are not yet billed. Would that be too
clumsy to do? Would there be any simpler methods?

Thanks a lot again.

 

[J. Goddard]

Hi-

For the combo box error of invalid data -

When does the error occur - when you set the filter for the combo box,
or when you make a selection?
What does the filter SQL look like?
What is the source for the combo box, i.e. SQL or query?
How many columns in the combo box, including invisible ones?
Which is the bound column?
What database field is the combo box bound to, and what is it's data type?

You may find the solution to the problem in the answers to some of these
- look for quotes where there should not be any, for example.

To find jobs without related invoices -
Create a query linking the jobs and invoices tables on the job number
Make the relationship an outer join, so that it shows ALL jobs,
including those without related invoices. Jobs without invoices will
have Null in the invoices.jobnumber field. Put Null in the criteria box
for the invoices.jobnumber field, and you have a list of jobs that have
not been invoiced (Hint for combo box source!)

Cheers

John