docmd.open expresion with multivalues field

[Belimisimus]

Hi,

I'm having difficulty with following code in dblclick event:

DoCmd.OpenForm "Kontakti", , , "ContactID = " & Me.cbo_AssignedFrom, , acDialog

This expression is working only for singlevalued columns, does someone know how to do it with multivalued column? Is it even possible?

[NeoPa]

This question is fundamentally about :

1. Accessing the various different columns of a ComboBox control.
2. Creating a string in the correct format for a SQL filter

For A check out the .Column() property of the ComboBox class.
B is a little more general. It depends on exactly what you're trying to achieve. The following snippet may help though.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[Belimisimus]

This question is fundamentally about :

1. Accessing the various different columns of a ComboBox control.
2. Creating a string in the correct format for a SQL filter

For A check out the .Column() property of the ComboBox class.
B is a little more general. It depends on exactly what you're trying to achieve. The following snippet may help though.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

First of all thanks again for helping me. I'll take a look on your suggested link as soon as I find time.

I believe that we misunderstood each other. I'm not talking about accessing the various different columns of a ComboBox control. It's about ComboBox field with "Allow Multiple Values" option set to yes.

Anyway, i did find a solution, I have modify code to:

DoCmd.OpenForm "Kontakti", , , "ContactID = " & AssignedFrom.value, , acDialog

and now is working but only for fields with one selected value. For now this will be sufficient!

[NeoPa]

There is no such property of the ComboBox I'm afraid. ListBoxes have a MultiSelect property. Is this what you're referring to?

[Belimisimus]

There is no such property of the ComboBox I'm afraid. ListBoxes have a MultiSelect property. Is this what you're referring to?

display control = combo box
allow multiple values = yes

This are field properties in table design view!
MS 2007

[OldBirdman]

I think this is an Access2007 property. Tables may have fields with "multi-values". This is achieved by having hidden related tables (one-to-many).
Using VBA on such fields may be the problem here. I don't have ac2007.

[NeoPa]

I'm afraid my experience only goes as far as Access 2003. You're probably right in what you say, but I can't confirm, or even help with it.

[Belimisimus]

Well to me usin VBA on any field is a problem :)

Out of curiosity, wonder how come that you don't work with Access2007? As I see many of more experience users use 2003 version. Is there any particular reeason?

[missinglinq]

Access 2007 was so god-awful buggy when it first came out that very few experienced developers upgraded when it came out. Also it is so totally different than previous versions that it's almost like learning a new language. One of the most respected Access gurus in the world, Allen Browne, once suggested that it would take an experienced Access developer two to three times longer to master version 2007 than it would to for a total Access novice, because of the former's having to un-learn then re-learn so many things.

And yes, Access 2007 does indeed have a new, multi-value field, as Birdman stated, and from what I've seen it's very problematical and being avoided like the plague by most developers.

And yes, with version 2007, Microsoft also added the Multi-Select capability to Comboboxes as well as Listboxes!

Linq ;0)>

BTW, I don't run version 2007, either!

[NeoPa]

Putting it from my perspective, which is not a million miles from Linq's I give you, I just got the strong impression that most of the changes introduced were for the benefit of Microsoft rather than their users.

Access 2007 seems to me to be a dumbing down of the package, with the assumption presumably that MS feel that more people will buy it and feel that, because the interace is sexy and easy to start with, therefore they are capable of designing databases. Fundamentally, there are basic understandings, without which people just cannot. This fact is (to me at least) deliberately obscured simply to get more sales of the product.

Where are MS when all the new Access users are hitting that wall that they pretended wasn't there? I don't see them offering much support. Not at that level. It is left to volunteers like us.

I have no problem being a volunteer in this area. In fact, I love it. I've always had a big problem with people or enterprises that take advantage of such human generosity though. I feel it reflects cynicism of the highest degree. The long-term effect is that it will tend to put people off from offering help. Thus the whole community suffers. This just for the temporary benefit of selling a few extra packages or gaining a little more market-share.