Auto-population of fields

[brittb]

Hi there. I'm a complete novice trying to create my first database, so please feel free to treat me as such.

I'm trying to track specimens coming through my lab, which will go through several steps of a long procedure. We want to track on what date each step happens for a specimen, and each specimen might go through each step multiple times (if say, a run fails), so I have separate tables for everything: Specimens, Extractions, PCR, Sequencing.

Each of those tables is linked to the next through a key -- each specimen can have multiple extractions, each extraction can have multiple PCRs. In the PCR table, I've built a combo box for people to select the Specimen ID. My question is this: How, after adding a Specimen to the PCR table, do I restrict the Extraction ID column to only those that match the selected Specimen ID? I'd prefer for the Extraction ID field to auto-populate with the latest entry for that Specimen ID selected, if possible.

I hope that's clear. See the picture of the relationships, it might help make more sense. Thanks in advance!
[imgnothumb]http://bytes.com/attachments/attachment/5936d1326413628/relationships.j pg[/imgnothumb]

[NeoPa]

You would use something like the following to populate the ComboBox. The code would run after the [Specimen_ID] control (EG. [txtSpecimen_ID]) had been updated :

Private Sub txtSpecimen_ID_AfterUpdate()
    Dim strSQL As String

    With Me
        strSQL = "SELECT [Extraction_ID] " & _
                 "FROM   [Exctractions] " & _
                 "WHERE  [Specimen_ID] = " & .txtSpecimen_ID
        .cboExtraction_ID.ControlSource = strSQL
    End With
End Sub

You need to replace the names I've used in the code for those which you've used obviously.