Help passing list box multi-column data!

Hello again!

I'm working on a new challenge and am quite stumped. I've done extensive research but haven't been able to make anything I've found actually work.

I need to target a user-selected record from a multi-column list box (single selection only) and command a separate form (opened elsewhere in the event procedure) to display a record containing a control containing a value matching the value of one column of the user-selected record in the list box. Crucially, I don't want the newly opened form to display ONLY that record, but simply to begin showing that record within its standard available recordset. Thus I can't use a WHERE condition on the OpenForm SQL statement.

I have been messing around with ItemData and the ItemsSelected Array, but am totally lost on how to specify a single column within ItemData. Furthermore, the whole thing was meant for multi-selection in a List Box, so I know it doesn't have to be this complex.

you'd think the answer would be something as simple as
Forms![Form Y].CtrlA.Value = Me.[ListBoxA].Column([#]), but this flatly refuses to work. "Column" doesn't even come up as a possible extension of Me.[ListBoxA] in the AutoComplete area, despite VB Help claiming you can use it for this purpose.

My List Box isn't bound to a table field. Logically, it shouldn't have to be - this is only a selection dialog bog and the currently selected item is always the only one that matters.

So, can someone show me an example of how this should work? "This" meaning specifying a single column of a selected record in a list box, and then passing that data to another form? Note that my openargs property is being used by something else and therefore this is not an option (unless you want to show me how to concatenate, and, more to the point, unconcatenate, which never works for me).

I'd post code, but I have nothing but lemons. I could post the lemons if it was really helpful.

Hi there,

this may be more lemons but...
I've attached an example in Excel (don't have Access at home) which passes a selected listbox item to a (public) array then another form opens and displays the first item in the array
Is that what you mean?

i appreciate the generous help in not just making a general suggestion, but trying to create an example soultion.

However, I don't know what this is or how to use it. When I open your .zip file, I get only an open excel worksheet with a data array. The array is roughly like what I guess would be the equivalent of an Access List Box in Excel, but I don't know what it is. It's not the end result I want, because I want only one value - the eqiuvalent of one of those cells, from the selected row - to be displayed in the second form. There aren't two forms (worksheets?) here, so I can't see that process occur, and like I said, the result would be only one cell of data.
Furthermore, and this may be a result of my ignorance in Excel, but I don't see any VB code here at all. If it's here, I don't know how to see or find it.
Last but not least, I'm not sure if this access concept can be reprsented in excel, or that the excel solution incorporates specfiic commands and syntax that would be neccesary for Access.

So, sorry, I can't get much from this. But, thanks for the effort.

Hi

If you just want to view the data in a column of the list box on your original form in an unbound textbox on a newly opened form, the syntax is (from whatever event you are opening your new form)

Docmd.OpenForm "frmMyNewFormName"

Forms!frmMyNewFormName!txtControlName = Me.MyListBoxName.Column(x)

Remember that the column numbering starts at 0, so to reference the second column of your list box you'd use

Me.MyListBoxName.Column(1)

HTH

Alan