Is there an easy way to populate the drop-down list in this access database schema?

Question

I have 3 tables that 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 associated with several nodes that are associated with several sectors.

Now for the interesting part. In Microsoft Access, you can create schemas that allow users to easily add data. I have a final table similar to this one that I want to use in my schema to insert data into:

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

That's where I want the magic to be done. I want the user to be able to first select a location, then present the available nodes (preferably in the drop down list), and finally, he can choose the right segment for the client that is adding.

Does anyone know a pretty simple way to do this? I started making a macro for this, but my macro memory is really bad, and I don't have the right literature with me to watch it.

Any help appriciated =)

Answer 1

It is a very bad idea to actually name something Name.

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

Two combined blocks, called, for example, cboLocation and cboNodes, in forrm, called, for example, 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 

Developments:

 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 

Developments:

 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