Is Me different in coding events in comboboxes than list boxes?

[Hagridore]

I have a bit of code in the OnCurrent event in a form which brings the value from a third column in a table into a text box when a value is selected in a combobox. The code might be written:

Me.txtDisplayColumn3Value=Me.cboInput(2)

I put this into the OnCurrent event because I need this information to display in this box whenever the form opens, no matter whether the value in cboInput has been changed or not.

This works exactly the way I want it to (of course, specific field names changed to bow to my company's para...um...high level of concern about privacy).

However, I have another database I'm creating specifically to use as an example for another set of questions I need to ask. In this database, because I'm using multiple values (I know...some don't like them. Not interested in that feedback.), I have a list box to obtain the information from the input which I want to use to populate two textboxes. I'm going to attach this database here, but here's the code (in the OnCurrent event of the form):

CODE:
Private Sub Form_Current()
Me.txtOffice = Me.lstDepartment.Column(1)


Me.txtBuilding = Me.lstDepartment.Column(2)
End Sub

The table that lstDepartment is pulling from has three columns. What's happening is that the first column information shows up in both textboxes, completely ignoring, it appears, the "Column" part of the code. Any ideas?

[June7]

Syntax should be same for combobox or listbox. Works for me. But I've never used multi-select (never used multi-value fields).

Why VBA? Why not expression in textbox ControlSource? Although doesn't solve the issue.

I did a test and UNBOUND the listbox. Expressions work. I have to say problem is the multi-value field. Sorry, know you didn't want to hear that.

[Missinglinq]

If the Listbox has its Multi-Select Property set to anything other than None, the problem isn't with Me....it's that the Listbox really has no Value Property, and since

Me.txtOffice = Me.lstDepartment.Column(1)
Me.txtBuilding = Me.lstDepartment.Column(2)

is trying to assign Values to txtOffice and txtBuilding, from lstDepartment, the code is going to fail. I'm guessing that since lstDepartment has no Value, the Access Gnomes, when executing the code, are simply using the data from Column(1) for both assignments.

Everything involving MVFs has to be done differently than it is done for traditional Fields, which is one reason most experienced developers shun their use.

Linq ;0)>

[Hagridore]

Thank you for your replies. Missinglinq, being a cat lover, I prefer "There's always more than one way to top a dessert!" I guess I'm a dessert-lover, too. :-)

[ssanfu]

However, I have another database I'm creating specifically to use as an example for another set of questions I need to ask. In this database, because I'm using multiple values (I know...some don't like them. Not interested in that feedback.), I have a list box to obtain the information from the input which I want to use to populate two textboxes.

MVF's are very hard to work with; I googled "access multivalued fields vba" and found these 2 (of many) sites:
http://www.utteraccess.com/forum/Acc...-t1944468.html
http://www.tek-tips.com/viewthread.cfm?qid=1657058

Maybe the code there will help you.

[CJ_London]

multivalue fields are just a way of easily creating a many to many relationship, but because they only work with list or comboboxes (which are designed to manage a one to many relationship) you are very restricted in what you can do with them on a form. This generally does not matter where you have a single or pair of fields (e.g. ID and description) but as you have discovered, accessing further columns is not possible.

The way you have described what you want to do indicates that you are actually creating a one to many relationship - Me.txtOffice = Me.lstDepartment.Column(1) implies a single selection - for a multiple selection you would also need to specify row numbers as well.

Listboxes have a multiselect option which provides similar functionality to multivalue fields - have a read of these two links, they may give you some ideas

https://msdn.microsoft.com/en-us/lib...or=-2147217396
https://support.microsoft.com/en-us/kb/827423

[Missinglinq]

...being a cat lover, I prefer "There's always more than one way to top a dessert!"...

Actually, I'm a cat lover, too, spending about $50 a month to help feed feral cats!

In The South, the term 'cat' is short for the ever popular 'catfish,' a fish that is skinned, rather than scaled, which gives the saying much more sense! It was also a term my late father, a North Carolinian who frequently fished for catfish, in his youth, was fond of!

Linq ;0)>

[Hagridore]

Ajax, thanks for your insight. I'll head over to these links now.

I had no idea about the "cat". I lived in Virginia until I was 15 but had almost nothing to do with catfish or fishing, so I don't think I ever heard this. I am that much closer to enlightenment, and can now go home since I've learned my one thing for the day! :-) Thank you, Missinglinq!

[Missinglinq]

...I lived in Virginia until I was 15 but had almost nothing to do with catfish or fishing...

Sadly, someone was very remiss in your education!

Have a great weekend!

Linq ;0)>

[Hagridore]

Here's wishing Access Forums'd incorporate a little "thumbs-up" button like in text messages. Thanks again, . (Note blank space long enough to include your abbreviated name as you signed above, but since you're missing... :-) You have a great weekend, too!

[ssanfu]

Now that is funny...

Took me a couple of seconds to get it.......