List records not in table with multiple criteria

[cactuspete13]

When I try to make the ActorID hidden in the drop down (changing the column width on the subform to 0";1";1") the Actor's name vanishes. The name is only present while that row is highlighed, once I put the focus on another row, it becomes invisible.

[June7]

If form is continuous or datasheet, dependent combo or list box won't work nice if it's RowSource is a lookup with alias. The filtered RowSource means not all alias values are available for display in the records. This is a well-known issue with dependent combo and list boxes and not easily dealt with. You can include the lookup table in the form RecordSource (RIGHT JOIN) and bind textbox to the alias field. Set the textbox as Locked Yes and TabStop No.

[recyan]

Can think of 2 ways :
1) 1st Option :
a) In the qryMovAct, add the LastName & FirstName fields to the query.
b) Add 2 Locked Textboxes to the MovieActorSubform using LastName & FirstName fields from the query qryMovAct as ControlSource. Make these textboxes locked.
c) See what happens when you open the main form MovieList

2) 2nd Option :
Have 2 subforms connected to the Main form MovieList.
The 1st subform will display the current actors listed against the selected Movie & is ReadOnly.
The 2nd subform is only for the purpose of adding a new Actor record & will show only one blank row for adding a new record. On adding an actor here, the 1st subform will automatically refresh & display the added record.
The 2 subforms discussed here are nothing but a bit of modified version of our MovieActorSubform subform.

Thanks

[cactuspete13]

I was thinking about doing something similar: using one sub form with add new records turned off that only shows matching actors, with another sub form that's only purpose is to list actors not selected for that movie and use an append query to add the actor. Thanks for your help

[recyan]

I was thinking about doing something similar: using one sub form with add new records turned off that only shows matching actors, with another sub form that's only purpose is to list actors not selected for that movie and use an append query to add the actor. Thanks for your help

There is no need to create any additional query.
You can work with a slightly modified version of the SubForm which we already have, for adding records & a slightly modified version of the SubForm for listing the Actors. Check the attached version & see if it gives you some guidelines. Be sure to check the event OnUpdate for the 2nd SubForm field MovieID in the CodeBuilder & also the Form Properties.


Thanks

[recyan]

Attaching the 1st option version for an alternative. See how it goes. ( Personally, I prefer the 2nd option of 2 subforms ).

Thanks