Referencing a value in a list box?

[aziz rasul]

How do I a reference a single highlighted value in a list box so that I can place this value in a query?

I have tried:-

[Forms]![formname]![lstLOPID].Column(0, 1)

but it doesn't work.

I know how to place the value in a text box and refer to it that way. However I'm curious as to how to solve the problem as outlined above.

 

[Rich]

You can only reference one column, why do you have two?

 

[aziz rasul]

I assumed that we had

Forms!...Column(column number, row number)?

 

[Joseph Yeo]

Try
[Forms]![formname]![lstLOPID].Column(0)
and play around with that.

 

[aziz rasul]

Tried this earlier. But it doesn't work.

 

[simongallop]

No doubt this is not the best way .. but it works!!

Place and amend this code in the On Dbl Click event of the list box

Private Sub List0_DblClick(Cancel As Integer)
Dim MyQD As QueryDef
Dim MyStr As String
Set MyQD = CurrentDb.QueryDefs("Test1")
MyStr = "SELECT Test.Num, Test.Name FROM Test WHERE (Test.Num)=" & Me.List0.Column(0) & ";"

MyQD.SQL = MyStr

MyQD.Close
End Sub


Any probs with translating the code then repost

HTH

 

[Rich]

Which is your bound column, if it's 0 then you don't need to reference it.

 

[aziz rasul]

Thanks both. My Bound Column value is 0.

Harry the code u gave was the original method that I employed. However I'm not good at SQL, hence left it. However your code will be usefule in future.

This is the code that I'm trying to resolve:-

Set ctl = Me!lstLOPID
intCount = ctl.ListCount

For x = 0 To intCount - 1
ctl.Selected(x) = True
LOPID = ctl.Column(0, x)
Me!Text93 = LOPID
DoCmd.OpenQuery "qryQueryName"
ctl.Selected(x) = False
Next

Using the value in the text box as the query criteria works OK. However, as an exercise, I'm trying to get the highlighted value in the list box to be directly transferred into the query criteria and NOT use the text box at all.

I have also tried:-

[Forms]![frmExtract Advanced Orders]![lstLOPID].ItemData(0)

in the query. No luck.

 

[Rich]

Doesn't [Forms]![frmExtract Advanced Orders]![lstLOPID] work?

 

[aziz rasul]

I'm afraid not.