spectrum48k

I have a listbox based on a SELECT query

Each row has 4 fields

The user selects a row in the listbox.

Is there a way to put the contents of a field into a variable ?

eg. if field #3 of the selected row contains "hello", can I put that into a variable using the underlying VB ?

eg.
Dim mystring as string
mystring = forms![myform]![listbox]![field3]

Its ages since I used Access, so I'm just feeling my way again, and I'm pretty sure I've done this before.

bob269

What are you trying to acheive?

I assume you've got some sort of flag or field/text on the form which you want to change depending on the selection?

You'd enter the expression in the "afterupdate" part of the "field3" properties and tell it xxx.value = field3.value or whatever.

spectrum48k

imagine the listbox contains name, addr1, add2, tel in its fields

I want to do this:

1. click on a row in the list box
2. open a form
3. fill in the name box with contents of the name field, from the listbox
4. fill in the addr1 box with contents of the addr1 field, from the listbox
etc...

I know you can do it - just can't remember, and google can't seem to help me.

bob269

If you want to store a variable between forms you need to create a global module

spectrum48k

bob, are you sure ? I'm pretty sure I can reference something on the underlying form without declaring a global.

isn't it something like:

forms![myform]![listbox].field1

or something like that ?

bob269

If you're keeping the form open and opening a form on top then you can reference the other form with

Forms!formname!field3.Value

No need for the []

spectrum48k

yes, the form is open underneath, but your syntax doesn't apply to a listbox

forms!uderlyingform!listbox.field3

that brings up and error, but I know there's a way to reference each individual field in the row (of the listbox)

markr1963

Try forms!uderlyingform!listbox.column(2). IIRC listbox columns start from 0

so, on the OnClick event for the listbox:
docmd.openform "frmSomeOtherForm"

You could probably use that form's OnOpen or OnCurrent event to populate the textboxes

me.txtName = forms!uderlyingform!listbox.column(2) 'assumimg the 3rd column is the name etc,etc

HTH

Mark

spectrum48k

mark, you beauty - I knew it was something like field or column, DOH!!!

.column(2) - thats it!

cheers all