Solved Combo Box with multiple columns: using respective column in vb code (1 Viewer)

mistyinca1970

Member
Local time
Today, 05:53
Joined
Mar 17, 2021
Messages
117
Sorry if the title is confusing.
What I'm trying to do is use both columns of a combo box in different ways. My combo box (cboSendTo) has two (visible) columns: one's name and then one's email address. Is there a way to specify the column of the combo box to use in code other than whichever column is bound? I would like to use column 2 (name) in one place and column 3 (email address) in another. Is this even possible?
Code:
Private Sub btnEmail_Click()
    
On Error Resume Next
    
    MsgBox "You will need to add any document attachments to the email manually.", vbOKOnly, "Generating Action Item"
    
    DoCmd.SendObject , "", "", cboSendTo, "", "", "Contrax™: Action item from " & TempVars!UserFullName & ". Due By: " & DueBy, "Date Created: " & DateofEntry & vbCrLf & "Due by: " & DueBy & vbCrLf & "Staff Name: " & cboSendTo & vbCrLf & "Contract #: " & cboContract & vbCrLf & "Note: " & Notes, True
    
    DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, ""
End If

End Sub

Thank you,
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:53
Joined
Jul 9, 2003
Messages
16,245
If you look at my nifty tips webpage here:-


There's a tip about extracting information from a combo box into a text box including a video.


Tip:- "Display Data from a Combo Box in a Text Box"

You can use the same piece of Code in your VBA.
 

bob fitz

AWF VIP
Local time
Today, 12:53
Joined
May 23, 2011
Messages
4,717
The column count is zero-based, so the second column is column(1) and the third is Column(2).
So you could use =NameOfCombo.Column(2) as the Control Source setting of a text box to show what's the third column.
 

mistyinca1970

Member
Local time
Today, 05:53
Joined
Mar 17, 2021
Messages
117
The column count is zero-based, so the second column is column(1) and the third is Column(2).
So you could use =NameOfCombo.Column(2) as the Control Source setting of a text box to show what's the third column.
Excellent! Thanks...does this also work if the column is not displayed in the combo box? So if uncheck that column and only the person's name displays in the combo box, can I get the respective email address in the text box?

EDIT
Answering my own question. Discovered I can't do that, but I can achieve the same by changing the column widths in the combo box.

Thanks again for the tip! This should accomplish what I need!
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 12:53
Joined
Sep 21, 2011
Messages
14,052
Yes, it still works, even f the column is hidden, so not sure what you were doing?
 

mistyinca1970

Member
Local time
Today, 05:53
Joined
Mar 17, 2021
Messages
117
Yes, it still works, even f the column is hidden, so not sure what you were doing?
Well, what I did was go to row source and I unchecked the box for Email. When I did so, the combo box no longer even had that column. Is there another way to "hide" that column. Changing the column widths accomplished the same though...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:53
Joined
May 21, 2018
Messages
8,463
Is there another way to "hide" that column.
Your statement is a little confusing. When you unchecked it you "removed" it from the selected columns. If you open the query builder back up it will not be there anymore. It is not part of the select statement. In that case you are not "hiding" anything because it does not exist. You can hide an existing column only be making its width 0 or making the column count less than the number of columns in the rowsource.
 

mistyinca1970

Member
Local time
Today, 05:53
Joined
Mar 17, 2021
Messages
117
Your statement is a little confusing. When you unchecked it you "removed" it from the selected columns. If you open the query builder back up it will not be there anymore. It is not part of the select statement. In that case you are not "hiding" anything because it does not exist. You can hide an existing column only be making its width 0 or making the column count less than the number of columns in the rowsource.
I don't disagree with you. This was in response to Gasman stating that it works if the column is hidden. And then I did in fact adjust the column widths to accomplish it. 🤷‍♀️
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:53
Joined
Sep 21, 2011
Messages
14,052
I don't disagree with you. This was in response to Gasman stating that it works if the column is hidden. And then I did in fact adjust the column widths to accomplish it. 🤷‍♀️
As MajP stated, you were not 'hiding' the field (that is done by setting column width to 0 as you now know), you were 'removing' the field. :)
 

Users who are viewing this thread

Top Bottom