silly query but i can't solve it... vlookup equivilent

[splreece]

hi all,

sorry its a really simple one but am at a loss.

I have 1 table and 2 columns (with a code and then corresponding description).

"tbl_ccode"

"QC_cmb_ccode" and "QC_cmb_ccodedesc"

On a new form these are linked on a combo box where u can see both columns but when the user selects the right code, only the "Code" shows in the cell and table etc.... is there a way I can get the code and code description to stay in the combo box (likely not as the cell must only hold 1 entry I suppose)......

(or as an alternative)

If the user selects the code in one cmb box, i could use an unbound text box to lookup the code and show the corresponding description (like a vlookup in excel).

I've tried dlookup but am getting an error.
=DLookUp("[QC_cmb_ccode]","[tbl_ccode]","[QC_cmb_ccodedesc]=""")

any thoughts

 

[apr pillai]

Add the following Code in the Combobox_Click() Event Procedure with appropriate changes:

Private Sub Combo_Click()
     Me![Unbound TextBox] = Me!Combo.Column(1).Value
 End Sub

Column(1) refers to the second column of the combobox. Column(0) is the first column with Code.

 

[splreece]

Thanks very much,

I never thought of VBA and me! commands.

I'm a vba newbee so this is what I've changed in the response.

Private Sub Combo192_Click()
Me!Text261 = Me!Combo192.Column(1).Value
End Sub


this is causing a runtime 424 object is required issue.

any idea?

 

[Minty]

Try replacing the Me! with Me. if you are on the form and I personally would put this into the afterupdate event.

 

[splreece]

I must be doing something wrong as its still giving the runtime 424 and referring to the me.txt261 line.




Private Sub Combo192_AfterUpdate()
Me.Text261 = Me.Combo192.Column(1).Value
End Sub

 

[splreece]

if useful. the Text261 box is a newly created unbound text box. no other alterations or changes at all.

 

[splreece]

sorted... it wasn't liking the .value part. I removed it and alls well.

 

[Minty]

Do you have Has Module enabled ? See this link https://www.techonthenet.com/access/questions/obj_required.php

 

[Minty]

I'm not sure where the obsession with adding .value has come from in a lot of code I see on posted here.
Value is the default property and adding it only seems to cause added typing, and in some circumstances failures in code. e.g this post.

Glad you have sorted it.

 

[apr pillai]

Private Sub Combo192_AfterUpdate()
Me.Text261 = Me.Combo192.Column(1).Value
End Sub

Sorry, there is an error in the code. Please remove .Value from the middle line.

Private Sub Combo192_AfterUpdate()
Me!Text261 = Me!Combo192.Column(1)
End Sub

 

[CJ_London]

the Text261 box is a newly created unbound text box

no vba code required.

In this textbox put the following in the controlsource

=Combo.columns(1)

assuming Combo is the name of your combobox (a bad name by the way)