how do I correctly define Subform Recordsource criteria?

[Morris]

Or am I doing it wrong way from the very beginnig?

I've got a table called 'Invoices' which holds 1700 invoices together
with InvoiceIDs (being integer numbers)

I've got a Form called 'Jobs' (based on 'Jobs' table) with a Subform
inside.

The Subform is supposed to show me 'Invoices' data. Because each job
can have anything from 0 to 3 invoices assigned to it (or more in the
future), inside my 'Jobs' Table I created a field called 'InvoiceIDs'
which for instance holds the value of '1303;1304;1310'.

Now when all the job details are displayed on the MainForm I want the
subform to display details of just these three invoices. When a user
accesses next Job (eg. with 'InvoiceIDs' = "25;30") I want a subform
to display those two invoices, and so on...

Any ideas how to achieve that or what's the common practice for this
type of problems?

Thanks
Morris

 

[Rick Brandt]

Or am I doing it wrong way from the very beginnig?

I've got a table called 'Invoices' which holds 1700 invoices together
with InvoiceIDs (being integer numbers)

I've got a Form called 'Jobs' (based on 'Jobs' table) with a Subform
inside.

The Subform is supposed to show me 'Invoices' data. Because each job
can have anything from 0 to 3 invoices assigned to it (or more in the
future), inside my 'Jobs' Table I created a field called 'InvoiceIDs'
which for instance holds the value of '1303;1304;1310'.

[snip]

Unless you are using Access 2007 and its ill-concieved multi-value field
option you should never store multiple
values in a single field. There is no way to use the built in MasterLink
and ChildLink properties of a subform with a field having semi-colon
separated values like that.

Can an invoice be against more than one job? If not then you did this
backwards. The invoice table should have a field that stores the JobID
rather than trying to store InvoiceIDs in the Job table. Then you have a
single field in each table to create the link and that field contains
exactly one value.

If invoices can be against multiple jobs then you have a many-to-many
relationship and that requires THREE tables. The third table being an
intersection table that matches JobIDs to InvoiceIDs.

 

[Morris]

Or am I doing it wrong way from the very beginnig?

I've got a table called 'Invoices' which holds 1700 invoices together
with InvoiceIDs (being integer numbers)

I've got a Form called 'Jobs' (based on 'Jobs' table) with a Subform
inside.

The Subform is supposed to show me 'Invoices' data. Because each job
can have anything from 0 to 3 invoices assigned to it (or more in the
future), inside my 'Jobs' Table I created a field called 'InvoiceIDs'
which for instance holds the value of '1303;1304;1310'.

[snip]

Unless you are using Access 2007 and its ill-concieved multi-value field
option you should never store multiple
values in a single field. There is no way to use the built in MasterLink
and ChildLink properties of a subform with a field having semi-colon
separated values like that.

Can an invoice be against more than one job? If not then you did this
backwards. The invoice table should have a field that stores the JobID
rather than trying to store InvoiceIDs in the Job table. Then you have a
single field in each table to create the link and that field contains
exactly one value.

If invoices can be against multiple jobs then you have a many-to-many
relationship and that requires THREE tables. The third table being an
intersection table that matches JobIDs to InvoiceIDs.

Thanks! That's what I'm gonna do now.

And also - how long do I need to train to become MVP?