We help IT Professionals succeed at work.

Set value of 2 column combobox

jad0083
jad0083 asked
on
Medium Priority
2,387 Views
Last Modified: 2013-11-28
Good Day Experts!,

I just have a simple question.  I have a 2 column combobox tied to a query rowsource, column withs are 0 and 1 so bound column is basically hidden.  I know you can set a value if its a one column listbox just by using me.comboname.value = "whatever", but i surmise in doing so, only sets a value to the 1st bound column.  Now since the only visible column is the 2nd column, is there any way to set a value to it?  Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
yes you can..

what is the rowsource of your combo?
CERTIFIED EXPERT
Top Expert 2016
Commented:


    With Me.Combo0
        For i = 0 To .ListCount - 1
       
            If .Column(1, i) = "SomeVaue" Then
            .Value = .Column(0, i)
           
            End If
        Next
    End With

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
On your Combo Bound Column put 2 instead of 1. This will still show only the first column but will make the Combo.value bound to the 2nd column.

jppinto

Author

Commented:
This is the rowsource I'm using:
get_type = "SELECT ID, F_Type FROM Fraud_Types WHERE Active = -1 ORDER BY 1 ASC;"

comment # 2 doesn't work for some reason, and I can't move the bound column to the 2nd column since i already built the form around using the value of the 1st column...
CERTIFIED EXPERT
Top Expert 2016

Commented:
did you try the codes i posted?

Author

Commented:
@capricorn1

Yeah, I tried it and the combobox still doesn't show any value you set to it...
CERTIFIED EXPERT
Top Expert 2016

Commented:
post the codes that you used..

Author

Commented:
Attached is the code I used:


Dim get_type As String, get_group As String, get_reasons As String, get_submitted As String
 
get_type = "SELECT ID, F_Type FROM Fraud_Types WHERE Active = -1 ORDER BY 1 ASC;"
get_group = "SELECT ID, Group_Name FROM F_Group WHERE Active = -1 ORDER BY 1 ASC;"
get_reasons = "SELECT ID, Reasons FROM Denial_Reasons WHERE Active = -1 ORDER BY 1 ASC;"
get_submitted = "SELECT Main_Records.ID, Fraud_Types.F_Type, F_Group.Group_Name, Main_Records.Account_Number, " & _
                "Main_Records.Fraud_Amount, Main_Records.Denial_Reason_ID, FORMAT(Main_Records.Denial_Date, 'hh:mm:ss am/pm'), " & _
                "Main_Records.Status FROM ((Main_Records LEFT JOIN Fraud_Types ON Main_Records.Fraud_Type_ID = " & _
                "Fraud_Types.ID) LEFT JOIN F_Group ON Main_Records.Group_ID = F_Group.ID) LEFT JOIN " & _
                "Denial_Reasons ON Main_Records.Denial_Reason_ID = Denial_Reasons.ID WHERE Main_Records.Analyst = " & _
                "'" & fOSUserName() & "' AND FORMAT(Denial_Date, 'MM/DD/YYYY') = FORMAT(NOW(), 'MM/DD/YYYY') " & _
                "ORDER BY Main_Records.Denial_Date DESC;"
 
 
Me.Type_Combo.RowSource = get_type
Me.Group_Combo.RowSource = get_group
 
Me.Denial_list.RowSource = get_reasons
Me.Stats_List.RowSource = get_submitted
Me.Acct_txt.Value = ""
Me.Amount_txt.Value = ""
Me.Denial_list = Null
 
'Me.Type_Combo.Value = "[Select Type]"
'Me.Group_Combo.Value = "[Select Group]"
 
With Me.Type_Combo
        For i = 0 To .ListCount - 1
       
            If .Column(1, i) = "[Select Type]" Then
            .Value = .Column(0, i)
           
            End If
        Next
    End With
 
With Me.Group_Combo
        For i = 0 To .ListCount - 1
       
            If .Column(1, i) = "[Select Group]" Then
            .Value = .Column(0, i)
           
            End If
        Next
    End With

Open in new window

CERTIFIED EXPERT
Top Expert 2016
Commented:
you have to add that value to the row source of your combo

ELECT Fraud_Types.ID, Fraud_Types.F_Type FROM Fraud_Types Where Fraud_Types.Active=-1 UNION SELECT Null, '[Select Type]'  FROM Fraud_Types;

Author

Commented:
Exactly what I needed, i guess i just forgot to add it in the rowsource, thanks for the assistance!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.