CODEWITHSUNDEEP
ms-accessvbams-access-2007
Jabberbyter
Jabberbyter

Reputation: 81

Selectively set combobox values programmatically

User select wells and it automatically displays all the samples that it may have:

Wells combobox

Samples combobox

In the Samples combobox fields 'EC' and 'EC_Undetected' are related so are 'Cl' and 'Cl_Undetected'. If (EC_Undetected = True) then 'EC' would be empty and therefore would be redundant to show both.

Instead of showing both column, I would like to only show one column that either shows the actual value or "Undetected" when If (EC_Undetected = True). They should try to use the same alias 'EC'.

Currently this is the sql query on Well_List_Click:

sql = "SELECT " _
        & "Samplers.ID, Samples.ID, Sampling_Date AS [Date Sampled], Samplers.Last_Name AS [Sampler], EC, EC_Undetected, Cl, Cl_Undetected, Ammonia, Ammonia_Undetected, Nitrate, Nitrate_Undetected, Water_Level_From_Surface AS [Lvl fr Surface], Laboratory_Name AS [Lab], Provincial_Audit AS [Audit], Background, Samples.Comments " _
        & "FROM  Samplers INNER JOIN (Laboratories INNER JOIN Samples ON Laboratories.ID = Samples.Laboratory_ID) ON Samplers.ID = Samples.Sampler_ID " _
        & "WHERE Well_ID=" & wellObj.wellID _
        & " ORDER BY Sampling_Date"

Sample_List.RowSource = sql

What is the best approach to solve this user requirement? I'm not sure where to begin since the sample combobox can have multiple rows

Upvotes: 0

Views: 122

Answers (1)

jhTuppeny
jhTuppeny

Reputation: 970

Use an IIF statement in your query;

IIF([EC_Undetected], 'Undetected', [EC]) AS ECValue

This will give you a column called ECValue which will either show the value or the string "Undetected".

Upvotes: 1

Related Questions