Restricting Row Source to unselected values is perplexing me

[canufrank]

OK, I hope that I can make this clear... I just started with Access and this is my first posting here

[Table1] has fields [t1ID], [text1], [text2] and [bool] containing records {{1;Jane;Smith;0}; {2;John;Davis;-1}; {3;Bob;Jones;-1}; {4;Al;Brown;0}}

[Query1] reads "SELECT t1ID, [text2] & ", " & [text1] AS fullname FROM Table1 WHERE (bool = False) ORDER BY [text2] & ", " & [text1];" so it returns the records {{4;Brown, Al}}; {1;Smith, Jane}}

[Table2] has fields [t2ID], [t1ID] and [whatever] containing no records as yet. [t1ID] has the following properties:
Data Type: 'Number' (Long Integer)
Indexed: 'Yes (No Duplicates)'
Row Source: 'SELECT t1ID, fullname FROM Query1'
Bound Column: '1'
Column Widths: '0";1"' (i.e. t1ID is hidden)

So, in datasheet view, the options in the t1ID dropdown are "Brown, Al" and "Smith, Jane". Even though the field stores 4 or 1, it displays the fullname. This is true after closing and re-opening the table. (These behaviours are actually noteworthy, even though most readers might find them obvious.)

What I want to accomplish is this: If a record is created with t1ID="4", then I want the dropdown to only offer "1" for a new record. So, (for demonstrational purposes) I created [Table3] which looks like [Table2], except t1ID.RowSource = "SELECT t1ID, fullname FROM Query1 WHERE t1ID NOT IN (SELECT t1ID FROM Table3)" but it lead to so many anomalies...

If [Table3] has no records, the options offered are as expected: both possible records. If a name is selected and the first record is created, the second record still offers both names. (I assume this has to do with early binding.) If I now close and reopen the table, the second record now offers only the unselected value. Great right?

Well, no - because now the first record shows 4 or 1 instead of the fullname. What happened there?

And, while you're here, how can I make the 'Row Source' late binding? such that the query is run against the current values in Table3?

 

[Beetle]

A few issues to consider here;

First, you seem to be saying that you want to allow only 1 record in table2 for every 1 record in table1, which begs the question, what is the purpose of table2? Do you require sub-classing of some type? One-to-One relationships are typically only used under limited circumstances.

Second, even if you do have a legitimate need to set up a One-to-One relationship like this, you should not be using Lookup Fields (aka drop down/combo boxes) in your tables. Tables are intended for data storage only, not for user interaction. User interaction should be handled via forms, which is the appropriate place for combo boxes. Microsoft, in it's attempt to make it easier for beginners to use Access, has implemented some things which are actually poor practice, thereby doing a disservice to those beginners in the long run. For more on Lookup Fields in tables, see this link.

If a name is selected and the first record is created, the second record still offers both names.

That's because the combo box (lookup field) cannot display a new list of values until it is requeried, which can't be done from within the table. When you close and re-open the table, the combo box is requeried.

I assume this has to do with early binding

No. Early/Late binding is related to using OLE Automation to programmatically control another application. It has nothing to do with tables/queries.

Well, no - because now the first record shows 4 or 1 instead of the fullname. What happened there?

Probably because you didn't set the Column Count and/or Column With properties appropriately, but, again, you shouldn't be doing this at the table level in the first place.

And, while you're here, how can I make the 'Row Source' late binding? such that the query is run against the current values in Table3?

It should first be established whether or not table2/3 should exist to begin with. If so, then you would need to do this at the form level using some code in a form module (the code however would have nothing to do with late binding).