List box question -- code

[Pwyd]

okay. so if this refers to a listbox on a form [Forms]![Batch Status
Page]![List94]
what references the value in the first column of any row on that listbox's
displayed results?


When i try to use that as a source for some code:


Dim strCriteria As String
strCriteria = DLookup("[Batch Number]", "[StatusQueryOne]", "[Batch
Number] = [Forms]![Batch Status Page]![List94]![Value]")
' DoCmd.OpenForm "Earliest Date in Batch", , , strCriteria
MsgBox (strCriteria)

its popping up the position within query, or the first row, depending on
which lookup i use. What i'm really after is the value that is being
displayed in the first column, which happens to be the batch number.


Thanks

 

[Pwyd]

Yeah, a colleague suggested .value also -- i've tried it with the dot
operator and the ! and neither works, it tells me the value i'm trying to
access is null. I can get it to show me the value of first item on the list,
but thats it. I've stumbled over the "itemData" property, and i'm looking at
the short sample of code here --

Sub RowsSelected()
Dim ctlList As Control, varItem As Variant

' Return Control object variable pointing to list box.
Set ctlList = Forms!Employees!EmployeeList
' Enumerate through selected items.
For Each varItem in ctlList.ItemsSelected
' Print value of bound column.
Debug.Print ctlList.ItemData(varItem)
Next varItem
End Sub

How would i use this snippett in a form named Batch Status Page
for non-multiple or simple selected rows, having the date popped up in a
field of another form named Earliest Date Form

I'm having a little trouble visualizing, but the first question to pop in my
mind is if your listbox bound column = 1. Another thing that jumps out at me
(but I don't use DLookup, so this may not be it) is that there is a "!" in "
[List94]![Value]". I would put a "." there. Value is the property,
therefore I'd use the dot.

okay. so if this refers to a listbox on a form [Forms]![Batch Status
Page]![List94]
what references the value in the first column of any row on that listbox's
displayed results?

When i try to use that as a source for some code:

Dim strCriteria As String
strCriteria = DLookup("[Batch Number]", "[StatusQueryOne]", "[Batch
Number] = [Forms]![Batch Status Page]![List94]![Value]")
' DoCmd.OpenForm "Earliest Date in Batch", , , strCriteria
MsgBox (strCriteria)

its popping up the position within query, or the first row, depending on
which lookup i use. What i'm really after is the value that is being
displayed in the first column, which happens to be the batch number.

Thanks

 

[Pwyd]

I was using dlookup because the query run in the listbox doesn't contain the
value i'm going to pop up. I suppose i could put it there.

Do you want to return the value (bound column) of the <b>selected</b> row?
Or are you looking to call the value of a row that you will specify by number,
but not necessarily the selected row?

The code you pasted in is something I've done before, but with multi-select
listboxes. You don't need to use that for a single-select.

If you want the date value of the listboxes selected value to pop up in
another form's field, where your bound column in the listbox is that date ...
in the after update event of your listbox:

Forms![Earliest Date Form]![TargetField] = me.listbox.value

If it's not the bound column:

Forms![Earliest Date Form]![TargetField] = me.listbox.column(x,me.listbox.
column.listindex)

But I may still misunderstand, cuz I'm not sure why you're using the DLookup.

Yeah, a colleague suggested .value also -- i've tried it with the dot
operator and the ! and neither works, it tells me the value i'm trying to
access is null. I can get it to show me the value of first item on the list,
but thats it. I've stumbled over the "itemData" property, and i'm looking at
the short sample of code here --

Sub RowsSelected()
Dim ctlList As Control, varItem As Variant

' Return Control object variable pointing to list box.
Set ctlList = Forms!Employees!EmployeeList
' Enumerate through selected items.
For Each varItem in ctlList.ItemsSelected
' Print value of bound column.
Debug.Print ctlList.ItemData(varItem)
Next varItem
End Sub

How would i use this snippett in a form named Batch Status Page
for non-multiple or simple selected rows, having the date popped up in a
field of another form named Earliest Date Form

I'm having a little trouble visualizing, but the first question to pop in my
mind is if your listbox bound column = 1. Another thing that jumps out at me

[quoted text clipped - 20 lines]

Thanks

 

[Pwyd]

Could you provide example values for the snippett you posted? I'm not
familiar with the methods you're using.

I was using dlookup because the query run in the listbox doesn't contain the
value i'm going to pop up. I suppose i could put it there.

Do you want to return the value (bound column) of the <b>selected</b> row?
Or are you looking to call the value of a row that you will specify by number,
but not necessarily the selected row?

The code you pasted in is something I've done before, but with multi-select
listboxes. You don't need to use that for a single-select.

If you want the date value of the listboxes selected value to pop up in
another form's field, where your bound column in the listbox is that date ...
in the after update event of your listbox:

Forms![Earliest Date Form]![TargetField] = me.listbox.value

If it's not the bound column:

Forms![Earliest Date Form]![TargetField] = me.listbox.column(x,me.listbox.
column.listindex)

But I may still misunderstand, cuz I'm not sure why you're using the DLookup.

Yeah, a colleague suggested .value also -- i've tried it with the dot
operator and the ! and neither works, it tells me the value i'm trying to
access is null. I can get it to show me the value of first item on the list,
but thats it. I've stumbled over the "itemData" property, and i'm looking at
the short sample of code here --

Sub RowsSelected()
Dim ctlList As Control, varItem As Variant

' Return Control object variable pointing to list box.
Set ctlList = Forms!Employees!EmployeeList
' Enumerate through selected items.
For Each varItem in ctlList.ItemsSelected
' Print value of bound column.
Debug.Print ctlList.ItemData(varItem)
Next varItem
End Sub

How would i use this snippett in a form named Batch Status Page
for non-multiple or simple selected rows, having the date popped up in a
field of another form named Earliest Date Form

I'm having a little trouble visualizing, but the first question to pop in my
mind is if your listbox bound column = 1. Another thing that jumps out at me
[quoted text clipped - 20 lines]

Thanks

 

[Pwyd]

All right, so its now part of a query called StatusQueryOne, but not a
visible column. How would i reference that?

If it is readily available to be put into that query, then that would be way
easier. No need to make two trips for info if one can do the trick.

I was using dlookup because the query run in the listbox doesn't contain the
value i'm going to pop up. I suppose i could put it there.

Do you want to return the value (bound column) of the <b>selected</b> row?
Or are you looking to call the value of a row that you will specify by number,

[quoted text clipped - 43 lines]

Thanks

 

[Pwyd]

Excellent, thanks so much. If i have any problems figuring it out from here,
i'll post them. Thanks again.

Forms![Earliest Date Form]![TargetField] = me.listbox.column(x,me.listbox.
column.listindex) I accidentally left the ".column" in that line. It should
be:

Forms![Earliest Date Form]![TargetField] = me.listbox.column(x,me.listbox.
listindex)

Go to Access help and search for the column property, and listindex property.

Listboxes are zero-based, so the first column is 0, the second column is 1,
etc. If your date column is column #6, for example: (counting all columns in
the rowsource - not just visible columns)

Forms![Earliest Date Form]![TargetField] = me.listbox.column(6,me.listbox.
listindex)

If you are using column titles:

Forms![Earliest Date Form]![TargetField] = me.listbox.column(6,me.listbox.
listindex+1)

All right, so its now part of a query called StatusQueryOne, but not a
visible column. How would i reference that?

If it is readily available to be put into that query, then that would be way
easier. No need to make two trips for info if one can do the trick.

[quoted text clipped - 7 lines]

Thanks