Problems with a dependent combo box on a sub form

[SOMSteve]

On my Sales Form I have basic information on the sales event (who bought it, how it was shipped, shipping method, etc...). I have put as a subform the items associated with that sale. For each item included in the sale, they must select where the item they are trying to bring in came from by picking a transfer number from the dropdown first. The item field then only displays items that have been previously associated with that transfer.

This works when I open the sub form by itself, but whenever I use it in the Sales Form a box pops up requesting the transfer number even though it has already been selected. It displays "[Forms]![subfrmSales].[TransferNum]" and when I type in the transfer number (that I had already selected) into the pop up, then it works fine. Any ideas on why this is happening and how to fix it?

I'm running Microsoft Access 2000 (SP-3).

My SQL in the Item Field:
SELECT tblItem.ItemNum, tblItem.ItemDescription, tblItemTransfers.TransferNum
FROM tblItem LEFT JOIN tblItemTransfers ON tblItem.ItemNum = tblItemTransfers.ItemNum
WHERE (((tblItemTransfers.TransferNum)=[Forms]![subfrmSales].[TransferNum]))
ORDER BY tblItem.ItemNum;


Let me know if you need to know anything else, thanks!

-Steve

 

[PercyN]

Try referncing the subform through its parent form like so:

WHERE (((tblItemTransfers.TransferNum)=[Forms]![YOURFORM1]![subfrmSales].[TransferNum]))
ORDER BY tblItem.ItemNum;

Replace "YOURFORM1" with the name of main form ie the subforms parent

 

[SOMSteve]

I replaced my reference with the recommendation of [Forms]![frmSales]![subfrmSales].[TransferNum] (the parent form's name is "frmSales"), but the pop up still appeared. This time it also appeared if I opened the sub form on its own. Thanks for the tip, any other ideas?

 

[PercyN]

I'm using something similar on SP2 and it works fine.

Maybe you should create a query that does the same operation and then open it in SQL view and observe the formular

 

[TheAceMan1]

and this:

[blue][Forms]![frmSales]![subfrmSales].[Form]![TransferNum][/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[SOMSteve]

That was it, thanks for your help! I've been working on trying to resolve that for a while now, it's good to finally be able to move on (to the next issue I'm sure ).

-Steve

 

[TheAceMan1]

SOMSteve . . .

For your reference: Refer to Form and Subform properties and controls

See Ya! . . . . . .

Be sure to see FAQ219-2884: