At 02:24 AM 17/06/2003 +0000, you wrote:

>this is my stand alone IB_lookupcombo
>----------------------------------------------
>object IB_LookupCombo1: TIB_LookupCombo
> Left = 136
> Top = 16
> Width = 121
> Height = 21
> DataSource = ds_items
> TabOrder = 4
> DisplayField = '"Name"'
> ShowButton = True
> end
>
>
>this is my IB_grid with embeded IB_lookupcombo
>------------------------------------------------------------
>object IB_Grid1: TIB_Grid
> Left = 0
> Top = 41
> Width = 589
> Height = 207
> CustomGlyphsSupplied = []
> DataSource = ds_ingredients
> Align = alClient
> TabOrder = 2
> TitleAlignment = gtaCenter
> object IB_LookupCombo2: TIB_LookupCombo
> Left = 136
> Top = 16
> Width = 121
> Height = 21
> DataSource = ds_items
> Visible = False
> TabOrder = 0
> DisplayField = '"Name"'
> ShowButton = True
> end
> end
>
>my parenttable
>------------------
>
>object ingredients: TIB_Query
> ColumnAttributes.Strings = (
> 'ASDF=COMPUTED')
> DatabaseName = 'D:\Works\KSK\database\ACCTDB.GDB'
> FieldsVisible.Strings = (
> '"IngredientsID"=FALSE'
> '"IngredientsName"=TRUE'
> 'ASDF=TRUE')
> IB_Connection = Form_main.IB_Connection1
> SQL.Strings = (
> 'select "ItemsID", "IngredientsID",'
>
> ' (select "Name" from "items" where "items".ID
>= "ingredients"' +
> '."IngredientsID") as ASDF,'
> ' "Qty", "Unit"'
> 'from "ingredients" m'
> '')
> ColorScheme = True
> DefaultValues.Strings = (
> '"Qty"=0')
> DeleteSQL.Strings = (
> 'DELETE FROM "ingredients"'
> 'WHERE'
> ' "IngredientsID" = :"OLD_IngredientsID" AND'
> ' "ItemsID" = :"OLD_ItemsID"')
> EditSQL.Strings = (
> 'UPDATE "ingredients" SET'
> ' "IngredientsID" = :"IngredientsID", /*PK*/'
> ' "ItemsID" = :"ItemsID", /*PK*/'
> ' "Qty" = :"Qty",'
> ' "Unit" = :"Unit"'
> 'WHERE'
> ' "IngredientsID" = :"OLD_IngredientsID" AND'
> ' "ItemsID" = :"OLD_ItemsID"')
> InsertSQL.Strings = (
> 'INSERT INTO "ingredients"('
> ' "IngredientsID", /*PK*/'
> ' "ItemsID", /*PK*/'
> ' "Qty",'
> ' "Unit")'
> 'VALUES ('
> ' :"IngredientsID",'
> ' :"ItemsID",'
> ' :"Qty",'
> ' :"Unit")')
> MasterSource = ds_stocks
> MasterLinks.Strings = (
> '"ingredients"."ItemsID" = "items".ID')
> MasterSearchFlags = [msfOpenMasterOnOpen,
>msfSearchAppliesToMasterOnly]
> BufferSynchroFlags = []
> FetchWholeRows = True
> Left = 736
> Top = 8
> ParamValues = (
> '"MLNK_ItemsID""_0"=0')
> end
>
>
>
>my lookup table
>-------------------
>
>object items: TIB_Query
> DatabaseName = 'D:\Works\KSK\database\ACCTDB.GDB'
> IB_Connection = Form_main.IB_Connection1
> SQL.Strings = (
> 'select ID, "Name", "Unit2" from "items"')
> ColorScheme = False
> KeyLinks.Strings = (
> '"items".ID = "ingredients"."IngredientsID"')
> KeyDescLinks.Strings = (
> '"items"."Name" = ASDF')
> KeySource = ds_ingredients
> MasterSearchFlags = [msfOpenMasterOnOpen,
>msfSearchAppliesToMasterOnly]
> OrderingItemNo = 2
> OrderingItems.Strings = (
> 'ID=ID;ID DESC'
> '"Name"="Name";"Name" DESC')
> OrderingLinks.Strings = (
> 'ID=ITEM=1'
> '"Name"=ITEM=2')
> SearchingLinks.Strings = (
> '"Name"="Name"')
> BufferSynchroFlags = []
> FetchWholeRows = True
> Left = 736
> Top = 104
> end
>
>
>Note: Don't mind the master detail that I have setup because I have a
>master table ... the parent table here is the detail table, I don't
>think that has anything to do with my problem since I have tested
>doing it starting from scratch with out the master detail setup ...

OK, I think your problem here is due to ambiguities in the parent query's
SQL, not from any mistakes in what you have done to implement the lookup.

Try correcting the parent set's SQL property to the following:

select
m."ItemsID",
m."IngredientsID",'
(select i."Name" from "items" i
where i.ID = m."IngredientsID") as ASDF,'
m."Qty",
m."Unit"
from "ingredients" m

That should fix it; although you might well have more ambiguities in the
master set, that are also messing up the detail set...

You might even do better to use the full table qualifiers instead of
aliases in the detail query, viz.

select
ingredients."ItemsID",
ingredients."IngredientsID",'
(select items."Name" from "items"
where items.ID = ingredients."IngredientsID") as ASDF,'
ingredients."Qty",
ingredients."Unit"
from "ingredients"


regards,
Helen