Composite key lookup

[zkarj]

Before I start, I know there is much about "Cascading Combos" in the forums, but I'd rather not go that way.

I have a table of Servers (only field is Server ID) and a table of Domains (Server ID, Domain ID). This is one-to-many, as each server will have multiple domains within it.

I also have a table of Applications which has as part of its primary key Server ID and Domain ID (i.e. that's where the app is installed).

I have used the Lookup tab in table design to easily provide a drop-down list of Server IDs for any new Application record, coming straight from the Servers table.

I want to (simply) add a drop down to the Domain ID field to select Domain IDs that are defined *for the entered Server ID*.

I already have the referential integrity enforced, but would like this simple aid as there are heaps of Servers and lots of similarly named Domains. It's looking to me like this is not possible, as the nub of the issue is how to qualify the Server ID field in the new record i.e.

SELECT [Domain ID] FROM Domains WHERE Domains.[Server ID] = <current record>.[Server ID]

How to define <current record>?

I know some will say forms are the tool for this job, but I need to see lots of records at once as I am entering (or changing) data.

I may be hoping against hope that this lookup capability can cope with a simple composite key.

 

[Liv Manto]

You can design your form to look like a table - datasheet view.

 

[zkarj]

Defeated

I think I will admit defeat. I created a form but still need to read through the examples to make it work. Although I suspect I will now need to create additional (and somewhat pointless) forms for entries to other tables, simply so the users have a consistent place to go for data entry.

It just seems incongruous that such a GUI database can't handle a very simple situation very simply. I guess I'm used to a pure SQL interface and when I see extras like lookups I expect a lot. Mind you, without the GUI, I suppose there is a lot of programming involved.

 

[Mile-O]

tblServers
ServerID
Server

tblDomains
DomainID
Domain
ServerID

Using the unique autonumber will allow what you want rather than indexing text fields which is, ultimately, slower.

 

[Pat Hartman]

I know some will say forms are the tool for this job, but I need to see lots of records at once as I am entering (or changing) data.

Cascading combos cannot be implemented directly in a table for the simple reason that the technique requires an event in which you can place code to cause the second combo to be requeried.

I don't know what your problem is with forms. A form in datasheet view looks and feels just like a recordset made from a query or opening a table. The advantage is you get form events in which to add the necessary code. And you get to use queries that can refer to the current record of a particular form.

It just seems incongruous that such a GUI database can't handle a very simple situation very simply. I guess I'm used to a pure SQL interface and when I see extras like lookups I expect a lot. Mind you, without the GUI, I suppose there is a lot of programming involved.

I challenge you to define cascading combos in an SQL server table (or other RDBMS). This is not a particular failing of Access.