Dynamic combobox depends on text box (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 13:06
Joined
Jun 24, 2017
Messages
308
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:

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


StatusID.PNG




Thank you so much in advance!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:06
Joined
May 21, 2018
Messages
8,463
I would think that is not flexible, if PKs change or you add or delete.
I would make a small table
Code:
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

Registered User.
Local time
Today, 13:06
Joined
Jun 24, 2017
Messages
308
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

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:06
Joined
May 7, 2009
Messages
19,169
majp already give you something to think about.
this demo is based on his idea.
see tblStatus structure.
see qryStatus.
 

Attachments

  • TestStatus.zip
    28.2 KB · Views: 86

Alhakeem1977

Registered User.
Local time
Today, 13:06
Joined
Jun 24, 2017
Messages
308
Thank you for your help and support I will apply it in my project 🙂
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:06
Joined
May 7, 2009
Messages
19,169
you're welcome:)
 

Users who are viewing this thread

Top Bottom