how do I correctly define Subform Recordsource criteria?

  • Thread starter Thread starter Morris
  • Start date Start date
M

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
 
Morris said:
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'.
Click to expand...
[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 said:
Or am I doing it wrong way from the very beginnig?
Click to expand...
I've got a table called 'Invoices' which holds 1700 invoices together
with InvoiceIDs (being integer numbers)
Click to expand...
I've got a Form called 'Jobs' (based on 'Jobs' table) with a Subform
inside.
Click to expand...
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'.
Click to expand...

[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.
Click to expand...

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

And also - how long do I need to train to become MVP? :)
 
Back
Top