Dynamic Listbox

[computergeek]

Hello,

I have an Assembly form where I need to filter out rows within a tags listbox that have already been assigned to an assembly.

Row Source for Tag List:
SELECT tbl_TagNames.TagID, tbl_TagNames.TagName FROM tbl_TagNames WHERE (((tbl_TagNames.TagID) Not In (SELECT tbl_Assembly.TagID FROM tbl_Assembly Where tbl_Assembly.AssemblyID <> Forms![frm_Assemby_Builder]![assemblyID] ) AND ((tbl_TagNames.active_flag)=Yes)) ORDER BY tbl_TagNames.TagName;

How/when do I requery the list when the user moves to a different assembly record where by the list only contains tags are not associated with another assembly (Not In (SELECT tbl_Assembly.TagID FROM tbl_Assembly ), BUT the list still contains the tag associated with the current assembly record, and any tags not associated with an assembly yet?

I don't think I am getting the current assemblyID value using - Forms![frm_Assemby_Builder]![assemblyID] . Can I get the current value within a Select Statement for the Row Source or can I only do this through code?

Help?
Thanks,

Computergeek

 

[computergeek]

Hi,

Okay I think I partially figured out how to go about this...

Me.cbo_TagID.RowSource =
"SELECT tbl_TagNames.TagID, tbl_TagNames.TagName FROM tbl_TagNames Where tbl_TagNames.TagID not in (Select tbl_Assembly.TagId From tbl_assembly where tbl_assembly.tagid <> " &
me!cbo_AssemID "

WHERE DO I FIRE THIS CODE? What event fires when the form moves from one record to the next?

Thanks,

Computergeek

 

[Ascentient]

If I understand your first post correctly you are wanting to continually update it from a "live" database. If this is the case you would place it in the GotFocus event. Doing so will cause the the list/combo box to pull current data everytime it is selected.

 

[PHV]

What event fires when the form moves from one record to the next
Try the Current event.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886