ListBox update after record selection in ComboBox

PedroDC

Board Regular
Joined
Feb 15, 2005
Messages
116
Hello again!

Here I am with another :eek: 'ing enigma.

I have a form; in that form I have a ComboBox and a ComboList:

cmdCombo_CodigoIT and cmdList_ITCode&DocNo.

Both are linked to a query: Find Null Corporate (with fields ITCode and Document No).

What I'm trying to do is, after selecting one Find Null Corporate.ITCode in the ComboBox, display all fields of Find Null Corporate query in the ListBox where Find Null Corporate.ITCode = "to the field selected in the ComboBox" (using VBA code in ComboBox's "After Update" Event).

With the code I'm using now, instead of a ListBox update, I get the message:


(please imagine Dialog Box: )

Enter Parameter Value X?

cmdCombo_CodigoIT.acDisplayedValue
________________________________
|________________________________|
_________
|OK Cancel|


Here's the cursed code:


Private Sub cmdCombo_CodigoIT_AfterUpdate()

[cmdList_ITCode&DocNo].RowSource = " SELECT [Find Null Corporate].[IT Code], [Find Null Corporate].[Document No] FROM [Find Null Corporate] WHERE [Find Null Corporate].[IT Code] = cmdCombo_CodigoIT.acDisplayedValue ORDER BY [IT Code] DESC, [Document No] DESC;"

End Sub


I believe there's an obvious solution, however I do not know VBA enough... Please heeeelp!

Thnks
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

nullZero

Active Member
Joined
Nov 14, 2005
Messages
497
Hey Pedro!

make the following changes to your cursed code (hehe) and hopefully it will work for you. I'm not making any promises I just cleaned up a couple issues I saw.

Code:
Private Sub cmdCombo_CodigoIT_AfterUpdate() 

[cmdList_ITCode&DocNo].RowSource = " SELECT [Find Null Corporate].[IT Code], [Find Null Corporate].[Document No] FROM [Find Null Corporate] WHERE [Find Null Corporate].[IT Code] = '" & cmdCombo_CodigoIT.text & "' Order by [Document No] DESC;"

End Sub

The changes are
1. you were including "cmdCombo_CodigoIT.acDisplayedValue" in your sql statement instead of the value of it. So you need to break up the string and concatenate in the value. hope that makes sense
2. I'm not familiar with acDisplayedValue, I always use .text
3. You were including IT code in your order by which was not needed as you were only ever going to have one IT code.

Hope that helps,
nZ
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top