getting list box data from table in access 97

[viveleroi]

I have some VBA which places values from a list box into a single field in tblTracking, seperated by commas:

1,3,8

How can I seperate these values after pulling them out again into an array, so I can loop through that and highlight the appropriate selections in the list box?

 

[WayneRyan]

viveleroi,

You can use the form's OnCurrent event to put this code.

You have a listbox named "YourListBox", which is populated with
whatever you fill it with.

You have a field called "YourField", which is filled with "string,
string, string".

When each record is displayed, the appropriate items in the listbox
will be selected.

Notes:
======

The "i loop" controls how many searches to do in the listbox. It's
set to 100, so the maximum number of selections for a record is 100.
The choice of 100 was arbitrary.

Couldn't remember what error # was thrown when you reference an
element not in varArray. When you reference a cell that wasn't
assigned, it's a subscript error. I just let it assume it was done
by Exiting the sub.

It will only select the first occurrence found, so if the listbox
has duplicates, they'll never be selected.

Dim varArray As Variant
Dim i As Long
Dim ptr As Long

On Error Goto ErrHandler

varArray = Split(Me.YourField, ",", -1)
For i = 0 To 100  <-- Max # of Fields
   For ptr = 0 To Me.YourListBox.ListCount - 1
      If Me.YourListBox.ItemData(ptr) = varArray(i) Then
         Me.YourListBox.Selected(i) = True
      End If
      Next ptr
   Next i

ExitTheSub:
   Exit Sub

ErrHandler:
   Resume ExitTheSub

That should get you started.

Wayne

 

[viveleroi]

That works, except for the fact that Split is not a valid function in Access 97 because it was introduced with vb 6.

All I am trying to do is store multiple list box data in a database.

 

[viveleroi]

I just decided to go with a seperate table for that list instead of a single field. It is not the best logically but its all I can do in Access 97. Thanks!

 

[WayneRyan]

viveleroi,

A seperate table is a good idea. Then you won't need any code to
parse it.

btw,

You can look up "Split" in the Search Facility here and find a few
replacement routines.

Wayne

 

[vengsiva]

How to count occurrence from parsed fields?

I have a problem in dealing with comma separated fields
When a field contains a number of values separated by comma how to use dcount functio.The simple Dcount("MyID","Mycommaseptbl","1 in mycommaseparatedfield") does not seem towork
vengsiva

 

[WayneRyan]

Vengsiva,

You need to use the Like "*" & Me.SomeField & "*"

to get info from a "compound field". But like in the prior posts, if you keep
the data in a seperate table, you don't need code to parse it.

There's not even a simple DLookUp in this case. You can look for something
specific, like "1" in the field, but you still have to dig it out.

The comma-seperated list really should belong in its own table.

Wayne

 

[vengsiva]

Thanks a million!

Thanks a milion for prompt reply
Vengsiva