Solved Combo Box with multiple columns: using respective column in vb code

[mistyinca1970]

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?

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]

If you look at my nifty tips webpage here:-

Nifty Tips - Nifty Access

Nifty Video Tips Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access

www.niftyaccess.com

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]

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]

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!

 

[Gasman]

Yes, it still works, even f the column is hidden, so not sure what you were doing?

 

[mistyinca1970]

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]

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]

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]

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.