Dynamic combobox depends on text box

[Alhakeem1977]

Hi All,

I am struggling with the below query criteria to return specific Combobox items depends on another text box field called [test].

1. if [test] is = "Case" should returns [StatusID] (4, 11, 12).
2. If [test] is = "Form" should returns [StatusID] (1,2,3,8,9,11,12).

The issue is that the [Status]Combobox does not return anything.

Below is the expression and a screenshot of the Combobox query:

IIf([Forms]![Case Details]![test]="Case",Eval([tblStatus].[StatusID]) In (4,11,12), Eval([tblStatus].[StatusID]) In (1,2,3,8,9,11,12))

Thank you so much in advance!

 

[MajP]

I would think that is not flexible, if PKs change or you add or delete.
I would make a small table

TblChoices
  Category
  StatusID_FK

with values like
Case 4
Case 11
Case 12
.....
Form 12

Now do a join to tblStatus by StatusID to StatusID_FK. Use this as the rowsource
Now simply filter on Category

 

[Alhakeem1977]

Thanks for your earliest response, actually I will change those items in the future.
Any other suggestions to achieve this as expression query criteria as I requested?

 

[CJ_London]

you cannot build criteria in this way

try adding another column to your query for [Forms]![Case Details]![test] (you can untick the show option)
on the first criteria line for this column put 'Case' and on the same line under statusID put 'In (4,11,12)'
on the next criteria line put 'Form' under your test column and 'In (1,2,3,8,9,11,12)' under the statusID column

 

[arnelgp]

majp already give you something to think about.
this demo is based on his idea.
see tblStatus structure.
see qryStatus.

 

[Alhakeem1977]

Thank you for your help and support I will apply it in my project

 

[arnelgp]

you're welcome