how do I correctly define Subform Recordsource criteria?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Morris - 02 Oct 2007 14:12 GMT

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

Reply to this Message

Rick Brandt - 02 Oct 2007 14:29 GMT

> Or am I doing it wrong way from the very beginnig?
>
[quoted text clipped - 8 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Morris - 02 Oct 2007 14:51 GMT

> > Or am I doing it wrong way from the very beginnig?
>
[quoted text clipped - 31 lines]
> Email (as appropriate) to...
> RBrandt at Hunter dot com

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

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

Reply to this Message