Row source of table referring to same table?

  • Thread starter Thread starter Noozer
  • Start date Start date
N

Noozer

I have a table and one of the columns of this table contains a key that
references another row in the same table. When I open this table I would
like this column to be a combo box showing the key for all of the available
rows. This would be simple to do, except I want to filter this dropdown
based on the value of another column...

Table: Places

Key: Autonumber
Name: Text - textbox
City: Text - textbox
OtherPlace: Number - combobox (link to same table KEY field)

So when I open the table and click a field in the OTHERPLACE column I will
see a list of all the NAMEs from this table that have the same CITYas the
row I've clicked on.

The following query explains what I'm trying to do, but obviously doesn't
work...

SELECT Name, Key FROM Places WHERE City = SelectedRow.City;

....So, how do I filter based on the selected row? What should the RowSource
of this combobox be? Remember this is a query for a rowsource in a table and
not on a form, so no VBA can be used.

....and how complex is this question? Should I be kicking myself in the ass
for not knowing? : )
 
Dear Noozer:

Assuming your writing in an MDB, you can reference a control on your
form from within the query. Assuming your form is "YourForm" and the
control is named "YourControl" then you can write the query as:

SELECT [Name], [Key] FROM Places WHERE City =
[Forms]![YourForm]![YourControl]

That about covers it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
No forms involved at all. This is simply the rowsource for this column in my
table. Data is bound to column two of the rowsource query.

I've had no problems doing the same thing when getting data from other
tables. I *think* my problem is the filter, but I'm not sure (I hate being a
newbie)

Using...

SELECT Name, Key FROM Places;

....I get a list of all the NAMES, but I want only the NAMEs from the rows
that have the same CITY as the current row selected in the table.

Thanks!
Assuming your writing in an MDB, you can reference a control on your
form from within the query. Assuming your form is "YourForm" and the
control is named "YourControl" then you can write the query as:

SELECT [Name], [Key] FROM Places WHERE City =
[Forms]![YourForm]![YourControl]
Click to expand...
 
Back
Top