See this is where not being able to reference a controls value to fill
a parameter in a query is a pain.
What you really want is a list box that has two possible query
statements for its values.
In one case it needs the all of the values from the Detail table, to
display the current values in the MasterDetail table.
In the second case when you are adding a new record you want the list
box to be filled with something like this:
SELECT "Txt", "ID"
FROM "Detail"
WHERE "ID" NOT IN (
SELECT "Detail"."ID" AS "ID"
FROM "Detail", "MasterDetail"
WHERE "Detail"."ID" = "MasterDetail"."DetailId"
AND "MasterDetail"."MasterlID" = ?)
This was you only are shown the detail records not already assigned to
a particular Master record.
On 9/23/07, Andrew Jensen <[EMAIL PROTECTED]> wrote:
> Great.
>
> One thing, you might want to create a unique index on ( MasterID,
> DetailID ) in the MasterDetail table?
>
> If not then you are allowing yourself to create duplicate records.
>
> For that matter you could of dropped the the IDENTITY field in the
> MasterDetail table altogether and just had the two FK values, and used
> both as the PK.
>
>
>
> On 9/23/07, Marc Santhoff <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I finally got it working, apparently I had messed up the linking in the
> > subform-container.
> >
> > With the following settings it worked nicely:
> >
> > Subform: type "table", linked from "ID" to "MasterID"
> > Tablecolumn: type "SQL", <select text, ID from "Detail">
> >
> > I had some errors inbetween when explicitly saving the current record
> > with the focus in the table control, but I wasn't able to reproduce
> > lastly.
> >
> > Marc
> >
> >
> > Am Sonntag, den 23.09.2007, 03:47 +0200 schrieb Marc Santhoff:
> > > Hi,
> > >
> > > I'm using a subform to show values of a m:n relation.
> > >
> > > Tables and relations:
> > >
> > > master master_detail detail
> > > ID(auto)
> > > ID(auto) <-> ID_master
> > > ID_detail <-> ID(auto)
> > > text
> > >
> > >
> > > The master_detail linking table is shown in a table control on a
> > > subform.
> > >
> > > One column of this table control shows the "text" field of the detail
> > > table linked by using a sql-based list field.
> > >
> > > No magic involved up to this point.
> > >
> > > But now I want to add one more row to the master_detail-table, a new
> > > record in the table control is made, the displayed "text" string for
> > > this one is selected.
> > >
> > > When the record is to be stored an error message appears telling that
> > > the field "ID_master" has to have a value (because it's a "NON NULL"
> > > colum).
> > >
> > > I will do this by using a little macro retrieving the current ID and
> > > write it to the subforms field, but:
> > >
> > > Could this be done without scripting? If yes, how?
> > >
> > > TIA,
> > > Marc
> > >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
