Query for combobox row source

[LukeMatt]

Table: Historic Town Names

Current Name	Historic Name
Town1	Town1_OldName1
Town3	Town3_OldName1
Town3	Town3_OldName2

Table: Archaeological Finds

Town where found	Historic Town Name
Town 1
Town 2
Town 3

In the table "Archaeological Finds", the field "Historic Town Name" is a combobox.

For Town 1, the combobox list should contain "Town1_OldName1".
For Town 2, the combobox list should be empty.
For Town 3, the combobox list should contain "Town3_OldName1" and "Town3_OldName2".

I am having a difficult time writing the correct SELECT statement for the "Row Source" of the combobox. Would somebody please provide the correct SELECT statement? Thanks.

[ranman256]

Combos allow only 1 pick so, this is a 2 combo box event:

1 combo,cboArch, has Achaelogical list, pick 1
then combo 2 shows the historical list for the chosen item in cboArch
cboHist.rowsource = "select [historic name] from tHistoric where [town] = forms!frmMain!cboArch

[ItsMe]

In the table "Archaeological Finds", the field "Historic Town Name" is a combobox.

Are you using a lookup field in your table?

[LukeMatt]

Thanks for your response, ranman256.

I'm working with datasheet view right now.

When I try...

select [historic name] from tHistoric where tHistoric.[town] = Arch.[town]

Access displays an "Enter Parameter Value" dialog box. If I enter the "Town where found" value for the current record, the combobox list gets filled properly. Obviously, I don't want the "Enter Parameter Value" dialog box to appear.

Is it possible?

[LukeMatt]

Are you using a lookup field in your table?

The field "Historic Town Name" is Text data type. On the "Lookup" tab of this field, Display Control is set to "Combo Box", Row Source Type is set to "Table/Query", and I'm trying to write the correct SELECT statement for Row Source.

[ItsMe]

If you are going to use a Lookup Field in your table and the RowSource type is table, you might as well use a table name as the rowsource. If you do not want the entire table as the RowSource of your Lookup Field, you can create a SELECT query using the Query Designer. Save and name your new query and use its name in the Rowsource of your Lookup Field. To ensure your new query is retrieving the desired records, you can add criteria and view the results in Datasheet View.

Generally, Lookup Fields are frowned upon because they create issues in a database larger than very small. In addition, the names of your columns indicates a non-normalized data structure. It is recommended to study some basics in Relational Database before attempting to create tables.