Is there a simple way of populating dropdown in this Access Database schema?

Question

Hello! I have 3 tables which look like this:

Location                Node                 Sektor
-----                   -------              -------
PK: ID - Autonumber     PK: ID - Autonumber  PK: ID - Autonumber
Name                    NodeName             Sektor
Height                  Aksess               Frequency
Latitude                Tag                  Coverage
Longtitude              IP

Each location is connected with multiple nodes which is connected with multiple sektors.

Now the interesting part. In Microsoft Access you can make schemas which allows users to easily add data. I have the final table looking like this which I want to use my schema to insert data into:

Customers
-------
PK: CustID
Name
Subscribtion
Sektor

This is where I want the magic to be done. I want the user to be able to first select a location, then be presented with available nodes (preferably in dropdown) and finally he can pick the correct sektor for the customer hes adding.

Anyone know a fairly easy way of doing this? I started making a macro for this, but my memory of macros are really bad atm, and I dont have the correct literature with me atm to look it up.

Any help appriciated =)

Answer 1

It is a very bad idea indeed to name anything Name.

It seems to me that you need cascading comboboxes. You will need a little VBA.

Two combo boxes called, say, cboLocation and cboNodes, on a forrm called, say, frmForm

cboLocation

RowSource: SELECT ID, [Name]
FROM Locations
ORDER BY [Name]

ColumnCount: 2

ColumnWidths: 0;2.00cm  ''The second column can be any suitable width

LimitToList: Yes

Events:

Private Sub cboLocation_AfterUpdate()
    Me.cboNode.Requery
End Sub

CboNode

RowSource: SELECT ID, NodeName
FROM Nodes
WHERE IP=[Forms]![frmForm]![cboLocation]
ORDER BY NodeName

ColumnCount: 2

ColumnWidths: 0;2.00 ''Ditto

LimitToList: Yes

Events:

Private Sub cboNode_GotFocus()
    If Trim(Me.cboLocation & "") = vbNullString Then
        MsgBox "Please select location"
        Me.cboLOcation.SetFocus
    End If
End Sub

You will also need a form event:

Private Sub Form_Current()
    Me.cboNode.Requery
End Sub