ListBox update after record selection in ComboBox

[PedroDC]

Hello again!

Here I am with another '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

 

[nullZero]

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.

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

 

[PedroDC]

It works perfectly.

Thanks a lot!