Populating a combo box but depends on the result of SQL

[mp3909]

Hello,



What I am trying to achieve?

I have this form.



The user needs to select the next cdd status by selecting a value from the combo box highlighted in yellow in the snapshot.

Currently, the combo box shows all possible values for the cdd status as you can see from the below snapshot.



I don't want that, I only want it to show only "Completed" because if you look at the Audit Trail of that Client in the snapshot, you can see the latest status (i.e. the one without an EndDate) is Fully Remediated and the next stage after Fully Remediated is Completed.
That is just one example. If the latest status was say "With Analyst" then the next possible stage is "With RM" or "With QC" so the dropdown combo box should show only 2 values "With RM" or "With QC" as the only possible values to select.

Getting the latest CDD Status is not difficult, I can use this SQL but not sure how to reference the caseID textbox that lives in my main form which contains the unique identifier for the client.



I guess I need to use a Query for this SQL right?

Then I need to write an if-else logic to display all possible scenarios as follows:

if SQL returns = "With Analyst" then
Combo Box for CDDStatus = should show "With RM" or "With QC"
else if SQL returns = "Fully Remediated" then
Combo Box for CDDStatus = should show "Completed"
else if SQL returns = "With QC" then
Combo Box for CDDStatus = should show "With FCT" or "Pending CIF Closure"
end if


Will anyone know how I can accomplish this please?
Thank you in advance!!

[mp3909]

attached is the db

[Welshgasman]

You can directly reference the form control.
You can set a TempVar to the CaseID and use that in the criteria.

I tended to use the TempVar, as I had an aversion in tying queries to forms, mainly so I could use them elsewhere, so wherever I needed that query all I need to do was set a Tempvar(s).

I would probably have those added descriptions in the table for those entries, so QC would appear twice, once for each description.

[xps35]

you can see the latest status (i.e. the one without an EndDate) is Fully Remediated and the next stage after Fully Remediated is Completed.

You know that. How is Access supposed to know that?

[mp3909]

You know that. How is Access supposed to know that?

That's why I have outlined a set of logical operations using if-else will be required or something similar.

[pbaldy]

Code-based logic can certainly work, but I'd prefer something in a table. That way if/when you get new/different steps, you can incorporate them in the table. You don't want to have to mess with code just because you get a new step. I don't know enough about how this process works to offer a specific recommendation on that table's design.

[xps35]

I agree with Paul and that is why I asked the question. These rules are data so need to be stored in tables. You can easily handle changes in rules. Populating your combobox is also easy; write a SQL to extract the statuses that match the rule.

[CJ_London]

1 - change the linkmaster property of the subform to the name of your listbox
2 - I would add another column to your departments to identify the order for 'what comes next'. Based on the information provided it might be

Group Stage	Current CDD Status	Parent
FCO	Not Started
FCO	With Analyst
FCO	With QC	With Analyst
BUSINESS	With RM	With Analyst
SECOND LINE	With FCT
BUSINESS	Pending CIF Closure
FCO	CDD Completed
BUSINESS	Pending System Update
FCO	Fully Remediated
FCO	Descoped

[mp3909]

1 - change the linkmaster property of the subform to the name of your listbox
2 - I would add another column to your departments to identify the order for 'what comes next'. Based on the information provided it might be

Group Stage	Current CDD Status	Parent
FCO	Not Started
FCO	With Analyst
FCO	With QC	With Analyst
BUSINESS	With RM	With Analyst
SECOND LINE	With FCT
BUSINESS	Pending CIF Closure
FCO	CDD Completed
BUSINESS	Pending System Update
FCO	Fully Remediated
FCO	Descoped

Why are you suggesting to change the link master property in subform? The subform is fine. My issue is nothing to do with subform

[CarlettoFed]

See if it works.
Database271.zip

[CJ_London]

Why are you suggesting to change the link master property in subform?

I said ‘of’ the subform. And because you don’t need the other control

[mp3909]

You can directly reference the form control.
You can set a TempVar to the CaseID and use that in the criteria.

I tended to use the TempVar, as I had an aversion in tying queries to forms, mainly so I could use them elsewhere, so wherever I needed that query all I need to do was set a Tempvar(s).

I would probably have those added descriptions in the table for those entries, so QC would appear twice, once for each description.

Hi Welshgasman,

I went with your suggestion in using TempVar and used that in the criteria.
All good, everything working as expected now so thank you!