Clearing a Multi-value List box on a form

[GBalcom]

This is the first code I feel comfortable giving back to the forum....After many days of searching and not finding anything to make this work, have re-purposed some other code for this. I was heading down a crazy road of child recordsets, but I couldn't seem to make it work. This is much simpler....

Private Sub cmdClearBase_Click()
    Dim x As Long
    
'clear Base Choices
    
    For x = 0 To Me.BaseChoices.ListCount - 1
    
        If Me.BaseChoices.Selected(x) = True Then
            Me.BaseChoices.Selected(x) = False
            
         
        End If
        
    Next x

'reset x
x = 0

'clear Option Choices
        For x = 0 To Me.OptionChoices.ListCount - 1
    
            If Me.OptionChoices.Selected(x) = True Then
             Me.OptionChoices.Selected(x) = False
            
         
            End If
        
        Next x
        
End Sub

 

[Isskint]

You could always use Requery

Me.BaseChoices.Requery
Me.OptionChoices.Requery

 

[GBalcom]

I have a button that is clearing the choices made in this bound field. Re query will not work...

 

[pr2-eugin]

Have you considered using Allen Browne's ClearList() function to Clear a list field??

 

[GBalcom]

yeah, I actually was planning on using that, but it didn't seem to work on my multi-value field.....

 

[pr2-eugin]

Hi, I am a bit confused.. What do you mean by,

.....but it didn't seem to work on my multi-value field.....

What is a MultiValued field? Is it one with many columns?

 

[Isskint]

I have a button that is clearing the choices made in this bound field. Re query will not work...

Just second pr2-eugin question re:MultiValued field.

If it is a bound field, then just set ListIndex to -1. Unless I have missed something, you can not store more than 1 value from a list box in a single field (unless you use some VBA to concantenate the selections, in which case it can not be a bound control).

 

[GBalcom]

Hi Gentlemen,
Sorry it took me so long to respond....Things got pretty busy at work.

Multi-Value fields are a new entity in Access 2010 (and possibly 2007). They allow the user to enter multiple values into the same field on a record. Apparently, they are completely normalized in the background.

This is really my first experience with them, and I'm not sold just yet. From what I can read on the web, Many experienced programmers aren't fond of the "behind the scenes" hidden tables that access uses for these.

In my case, the values I'm storing in these are the Row ID's out of read only sql table. They were read out of a dynamic List box that varies with every job, and with it's related folder structure. So, rarely will the same Row ID be used again (though it will happen).

Here is a link to an explanation I found
http://office.microsoft.com/en-us/access-help/store-multiple-values-in-a-lookup-field-HA010341483.aspx

 

[pr2-eugin]

I think you are confusing Lookup fields with something else. However even Lookup fields is NOT the right option.. Access MVP's coin the disadvantages of having a lookup field in a table as "The Evils of Lookup Field", because they are not recommended to be used.

Let me also clear some of your confusions here..

....Many experienced programmers aren't fond of the "behind the scenes" hidden tables that access uses for these.

Programmers are not fond of it because it is explained well in the link above, they are not hidden tables they are just normal tables that is a part of your DB.. You have to create them before you can actually use them.. Lookups can only be a part of the Form they are not supposed to reside in tables..

So think carefully again on what you intend to do and post back we will try to help you out..

 

[GBalcom]

Paul,
Thank you for your response.....Let me try to explain a bit more what I'm doing with this function, and how I'm using it. Then we can determine it it's appropriate or if there is something else I should be doing instead.

On the attached table, the bound field is the RowID that I need....this is the only thing going into the table....I do not have a lookup within the table, but on the attached form. This form's field is completely dynamic based on other controls....

I believe I may have posted an incorrect link to the lookup field....this doesn't appear to be the same thing as what I'm using.
http://www.techrepublic.com/blog/msoffice/how-to-use-the-new-multivalue-field-in-access-20072010/5134