How to filter the contents of a combo box based on another combo box

[RenaG]

I am using Access 2007.

I am trying to have the values displayed in field DiagnosticType (combobox) driven by what has been selected in the field DiagnosticTool (also a combobox). I have found suggestions on how to accomplish this in various places and as far as I can tell have set it up correctly. But something is still not right because it doesn't work. Maybe another set of eyes will help me see what I have missed.

Here is the select stmt on the DiagnosticType field:

SELECT DiagnosticType.ID, DiagnosticType.Type
FROM (DiagnosticTools 
    INNER JOIN DiagTypeToolsXref 
    ON DiagnosticTools.ID = DiagTypeToolsXref.DiagToolID) 
    INNER JOIN DiagnosticType 
    ON DiagTypeToolsXref.DiagTypeID = DiagnosticType.ID
 
WHERE (((DiagTypeToolsXref.DiagToolID)=[forms]![sfrmDiagBreast]![DiagnosticTools].[ID]))
 
ORDER BY DiagnosticType.Type;

And I put this in the VBA:

Private Sub DiagnosticTool_AfterUpdate()

    Me.DiagnosticType.Requery
    Me.DiagnosticType.SetFocus
    Me.DiagnosticType.Dropdown
    
End Sub

Private Sub DiagnosticTool_GotFocus()
    
    Me.DiagnosticType = Null
    
End Sub

My tables are:
DiagnosticTools
ID DiagnosticTool
1 Surgery
2 Needle Biopsy

DiagnosticType
ID Type
1 Lumpectomy
2 CT

DiagTypeToolsXref
ID DiagTypeID DiagToolID
1 1 1
2 2 2

So if the user selects Surgery as the Diagnostic Tool they should only see Lumpectomy in the Diagnostic Type field.

I hope I have given enough information. I look forward to being shown what I have done wrong.

TIA!

~RLG

 

[TheAceMan1]

How are ya RenaG . . .

Try the following Where Clause:

[blue]WHERE (((DiagTypeToolsXref.DiagToolID)=Forms![MainformName]![sfrmDiagBreast].Form![DiagnosticTools]))[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RenaG]

Hey TheAceMan1~

Thanks for your reply! We had a nice long weekend here in the USA because of our 4th of July celebration so I am just now getting back to this.

I made the modifications you suggested but still no go. It looks like this:

WHERE (((DiagTypeToolsXref.DiagToolID)=[Forms]![Patient]![sfrmDiagBreast].[Form]![DiagnosticTools].[ID]))

I am very very new to Access (this is my second project). So, I don't know if this makes a difference or not but the main form consists of tabs and this subform is on one of the tabs. The tab is called Diagnostic. There is a field (date) between the DiagnosticTool field and the DiagnosticType field.

Any thoughts?

~RLG

 

[RenaG]

I still haven't been able to work through this. Does anybody have any other suggestions?

Thanks so much!

~RLG