Filter Multi Value Field by selected only

[accote]

Hello All and thanks for any help you can provide



I have a form that has a MVF in it, originally I had the MVF set as text in the table, but had to convert it to a number field to make it web compatible.
Before I converted to a number field I've been able to only show the selected Comments within the MVF for the current record.
I still can do that (see picture link) but I can't for the life of me figure out how to not only show the CommentID but also the
actual Comment next to it. Setting Column Count to 2 does not work, it only shows the ID again in the second column.

The goal is to have the top List box only show the selected for the current record with the "comment name", and have the bottom list box for adding or deleting any comments. The reason for this is so the user has a simple view of the comments related to the current record, and not have to scroll through the long list on the bottom.

Any help you can provide with this would be great. Thank You.

[rpeare]

I don't think you're using a mutli value field unless you're using a newer version of access than your profile says. True multi value fields came into existence with Access 2010 I think. (p.s. true multi value fields are the devil and you should not use them) If you are actually doing something different than a multi value field can you provide a sample database a screen shot is not much help without the underlying code/SQL setup.

[accote]

Hi, thanks for the response. Yes I'm using Access 2010 (sorry about the wrong version),
and yes the MVF is a Devil, but the Employer wanted it all pretty and there are close
to 6000 Contact records in this DB with multiple Groups for each.
It was all fine before the conversion to the number field, and there is just no way that
I can find to show the Comment (text) in a numbered MVF.
I was even thinking about somehow adding some Expression or VB code to change the ID to Text in the
form, but wouldn't know how to go about that.
Something like If ID=1 show Text "1996 Xmas Mailing "

Well, been messing with this for 2 days, I think I'll give up, and the boss will just have to deal with it.

Thanks for trying to help.

CC

[rpeare]

Sorry I do not have access to 2010 so I can't help you very much without an example but if that middle portion is in fact a list box you might be able to get the value you want by using a dlookup column.

I have never/will never use multi value fields so I don't know what the record source of that list box looks like but it's just a suggestion.

your dlookup statement would be something like

IDComment: dlookup("[ItemComment]","[CommentTable", "[CommentID] = " & commentID )

One word of caution - domain functions are useful but incredibly resource heavy. If you use this particularly if this is a frequently used form you will want to compact/repair your database often and be prepared for it to be slow if you're dealing with tables that have a lot of data.

[accote]

Hi

Thanks for the tip, I will try it. Your help is appreciated.

CC

[accote]

Well I figured it out with one glitch that I can't figure out. When I switch through contacts it nicely updates in the text field I created,
except for the first record, when I go back to the first record it shows the value from the record I switched from. So basically it only shows
the correct value in the first record after opening the form, but not when I go to another record and back to the first one.
This only happens with the first record which by default is open. Any Idea, not a big deal, but still bugs me :-)

I added the code below to my Form_Current and Form_AfterUpdate:

Private Sub Form_Current()
Dim i As Long, msg As String, Check As String
On Error GoTo cmdGo_Click_Error
msg = ""
Me!txtsum = ""
With listbox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .Column(2, i)
Me!txtsum = Nz(Me!txtsum) & .Column(2, i) & vbNewLine
'msg = msg & .Column(2, i) & " " & .Column(2, i) & vbNewLine
End If
Next i
End With
On Error GoTo 0
Exit Sub

cmdGo_Click_Error:
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Contact Details"
Resume Next

End Sub

[rpeare]

Any time you want to update the display of existing information after a change of record use the REQUERY function. For instance if you change records and you want your list box to update you would have:

lst_001.requery

If you want a display to update after the addition of NEW information you will likely have to perform a REFRESH function

me.refresh
lst_001.requery