Handling multicolumn listbox row source

TravelingCat

Registered User.
Local time
Today, 16:08
Joined
Feb 22, 2010
Messages
42
Hello, i have a multicolumn listbox, and all the fields in the row source are from one table, but one of them is "assignType', a numeric field. It can be 1, 2 or 3. The thing is, i need the listbox to display text based on this numeric value, i.e. 1=assignment, 2=decision, 3=text. In other places i just put invisible textbox with control source set to assignType, wrote the text values in row source and then defined other comboboxes row source as [assignType].[column](1), but how do i solve this in listbox?
Thanks in advance
 
I believe you could put:
If listbox.value = 1 Then
Textbox.value = assignment
ElseIf listbox.value = 2 Then
Textbox.value = decision
ElseIf listbox.value = 3 Then
 
Textbox.value = text
End If

and then put it in the after update event or change. Sorry it wasn't all together.
 
Is there not a table that already has these values associated to the number? If not create a table for AssignTypes, ID would be the numbers 1, 2, 3 (autonumber will do), the next column will be the Types which corresponds to the numbers. You can then use that table in the row source instead.

OR

Create another Alias field in the Row Source of the listbox and use an IIF() function to interpret the types.

My preference would be choice 1 as that is in line with normalisation.
 
vbaInet, your suggestion sounds about right. For some reason i managed to avoid creating this assignTypes table, there wasn't any need in it. I guess i'll have to create it nonetheless.
LFC, i don't quite understand which textbox are you referring to, i don't have any textboxes, it was just an example of how i solved it before, but now i'm dealing with a listbox.
 
vbaInet, about your second suggestion, can you please write a short pseudocode or something? I don't get where would i put an Iif statement inside the SELECT .... , how is it done
 
TextType: IIF([AssignType]=1, "Assignment", IIF([AssignType]=2, "Decision", "Text"))

Something like that will be an alias field in the query of the list box's row source.
 
This is how my row source looks now that i've inserted your code:
Code:
SELECT assignHasSubassigns, [B]Iif([AssignType]=1, "Assignment", Iif([AssignType]=2, "Decision", "Text"))[/B], assignDueDateNew, assignDueDate, statusName, assignInCharge, assignDescription, assignDateOpen, assignNum, assignRecordId FROM tblAssignments, tblStatusList WHERE tblAssignments.assignStatus = tblStatusList.statusNum AND  ......
but it just created an empty column...
 

Users who are viewing this thread

Back
Top Bottom