?
Solved

Filter Data Passed into a Combobox based on Column Data in a CSV File

Posted on 2011-05-10
37
Medium Priority
?
559 Views
Last Modified: 2012-08-13
I have the following code populating a combobox from a .CSV file and would ike to know if there is a way to add filtering based on an added column.

Option Explicit
Dim dicTags As Object

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
  
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   Pen2_ComboBox.AddItem strContent(0)
   Pen3_ComboBox.AddItem strContent(0)
   Pen4_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Open in new window

The .CSV file is as follows:
Annapolis Hill Discharge Pressure;[CR]AH_DISCHARGE_PRESSURE;PSI;0;500;[Test];Building 1
Annapolis Hill Suction Pressure;[CR]AH_SUCTION_PRESSURE;PSI;0;500;[Test];Building 1
Annapolis Hill Tank Level;[CR]AH_TANK_LEVEL;FT;0;40;[Test];Building 1
Backwash Flow Control Valve Position;[CR]BKWASH_FCV_POS_IN;Percent;0;100;[Test];Building 2
Backwash Flow;[CR]BKWASH_FLOW;GPM;0;250;[Test];Building 2
Backwash Flow Set Point;[CR]BKWASH_FLOW_SP;GPM;0;250;[Test];Building 2
Chaffee Hill Discharge Pressure;[CR]CH_DISCHARGE_PRESSURE;PSI;0;500;[Test];Building 3
Chaffee Hill Suction Pressure;[CR]CH_SUCTION_PRESSURE;PSI;0;500;[Test];Building 3
Chaffee Hill Tank Level;[CR]CH_TANK_LEVEL;FT;-25;-5;[Test];Building 3
Clear Well Level 1;[CR]CLEARWELL1_LEVEL;FT;0;10;[Test];Building 4
Clear Well Level 2;[CR]CLEARWELL2_LEVEL;FT;0;10;[Test];Building 4
Clear Well Level 3;[CR]CLEARWELL3_LEVEL;FT;0;10;[Test];Building 4

Open in new window

I would like to be able to allow the user to filter the data that populates the comboboxes by selecting the area.....example Building 1, and only have the building 1 data populate the box. If no filter is applied, they would see all data from all areas.


======
Related Prior Question : http://www.experts-exchange.com/Q_27026600.html
0
Comment
Question by:BryanKipp
  • 15
  • 10
  • 4
  • +2
34 Comments
 
LVL 6

Expert Comment

by:judgeking
ID: 35731098
Is this a VB6 app?
0
 

Author Comment

by:BryanKipp
ID: 35731303
It is VBA 6.5
0
 

Author Comment

by:BryanKipp
ID: 35740528
It this an impossibility? Or is there some better way to accomplish what the original question addressed that incorporates this into the final solution?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35743080
<It this an impossibility?>
Very little is truly "Impossible"
;-)

The unorthodox way you are loading the combobox (via a csv disk file), make this "Difficult".

This would be *much* easier if you simply imported this file (possibly as a temp table) int the DB...


JeffCoachman
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35743110
I'm not sure what is going to be the criteria that will dictate that a building subset or all buildings should be displayed from your example, but try something like this modification:

Option Explicit
Dim dicTags As Object

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
[b] Dim bolInclude as boolean        'Boolean to trigger inclusion or not of any given building choice
 Dim strControlString as string   'Your filter string from somewhere else that is either blank 
                                         '(to include ALL), or equal to the building string you want to include
                                         '(i.e. "Building 1", etc.)[/b]

 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
  
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear

'[b]strControlString = ""            'This will include all buildings.
strControlString = "Building 1"   'This will only include the rows that have matching building strings.[/b]


While Not EOF(1)
[b]   strContent = Split(strHeadings, ";", 2)

   bolInclude = True               'Assume to include all buildings
   'Then check to see if there is any control string, and if there is, assign bolInclude
   'to be True or False if your 6th element in the CSV string matches the required Building string.
   IF Len(strControlString)<>0 THEN bolInclude = (strContent(6)=strControlString)

   'Now only add the element if we've determined that the string should be included in the combo.
   IF bolInclude THEN[/b]
       Pen1_ComboBox.AddItem strContent(0)
       Pen2_ComboBox.AddItem strContent(0)
       Pen3_ComboBox.AddItem strContent(0)
       Pen4_ComboBox.AddItem strContent(0)
       dicTags.Add strContent(0), strContent(1)
[b]   END IF[/b]
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Open in new window


There are many ways to achieve the same results, but this is pretty readable.
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35743122
I see the Bold tags didn't work inside the code section, so when you see those, that is the modified code within your code example.  Hope that helps.
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35743127
...except for the "strContent = Split(strHeadings, ";", 2)" line.  (Doh!)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35743199
...Then it would just be a matter of filtering a form based on a unique list of Buildings form the list.

Sample attached


Can you explain why this has to be done via a disk file?
And explain the ultimate function of this system?

In any event, your ultimate goal here should be to create a "Building" Table and load the combobx with that.
Then filter the form based on the value in the combobox.

JeffCoachman

db350.mdb
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35745636
BryanKipp, are you trying to filter which records show in a form or what options are on a combobox list?  boag2000 is making me think I misunderstood the question when he/she mentions filtering a form, but I understood from your original question when you stated "add filtering based on an added column" that you wanted to have a combobox automatically adjust what options/choices are available on that combobox list based on another field on the form (not identified here in the question).  
Can you clarify?
0
 

Author Comment

by:BryanKipp
ID: 35745841
To attempt to answer the questions...

The reason for using  the .csv file is that, in the system I'm working with, generating this file is fairly simple and easy to modify. We won't have any type of DB software, nor Excel to use any other type of file. I'm not certain I understand the comment above about importing this file vice loading it at run time. Once this system is operational, this file will likely not be altered much, but if it was, I would still need to be able to "load" the aterations.

The purpose of the system is to allow a user the ability to select the values (Pens) they wish to do historical trending on in an automation system. The need to see different specific values is based on what events might be occurring in their system.

The idea of adding the ability to filter the list is based on trying to "clean up" the list for the user so they don't have to wade through every possible item to find the "tag" they wish to trend.

I don't know if this helps, or if you need to see the actual complete code I am currently using for any further clarification. I will try the code listed above to see if that gets me where I need to be.

Thanks for your assistance!
0
 

Author Comment

by:BryanKipp
ID: 35745888
Also, for further clarification, this is a VBA 6.5 application. Not sure why the question got moved into the Web Language zone.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35746950
<We won't have any type of DB software,>
<VBA 6.5 application>
VBA really can't work on it's own.
It needs an "Application" (MS Office Application) in order to function...

So then can you state the specific Office App you want this solution to run in?
If you are not using Access or Excel...is this an Word combobox?

Did you test the sample File I posted?
What is it lacking?
It appears to do all that you are requesting...

JeffCoachman
0
 

Author Comment

by:BryanKipp
ID: 35747130
This code is running in a Rockwell Software application, but their VBA behaves the same as an Office application.

I did test the code you sent, it generates an error : "Subscript out of range" on line 34 of the code
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35748800
<I did test the code you sent, it generates an error : "Subscript out of range" on line 34 of the code>
...?
In the file I posted there is only about 6 lines of code?

If you mean you simply took my code and pasted into your app, then I can't tell you what might happen.

Perhaps another Expert can help you further...
0
 

Author Comment

by:BryanKipp
ID: 35753673
The code I tested was what was provided by kbirecki. It generated the error. The .MDB file attached to your post is something that I would try if we had any sort of dataase software on the computers.

Perhaps it would be best if I withdraw this question and repost the entire question to get a complete solution. Thus far, it has been accomplished in two steps with this being the final (third) step. There may be a better way of accomplishing the desired result if I go about the solution a diffrent way.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35754042
@kbirecki

>>...except for the "strContent = Split(strHeadings, ";", 2)" line.
re: http:#35743127 comment
That statement came from the prior question and was a way to store the tag data in a dictionary object, with the first parsed part being the key and the remaining (unparsed) parts being the data.  Later in the application, the user would select a combobox item and the dictionary's hashed lookup provided a string that could be Split() to provide parameter data to a function.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35754059
@BryanKipp

>>Perhaps it would be best if I withdraw this question...
No.  It would be best if you stuck with the experts in this thread.
0
 

Author Comment

by:BryanKipp
ID: 35754243
My comment wasn't intended as a slight to anyone in this thread. My apologies if it came across that way. It just appears this will be difficult to solve, and wasn't sure if the entire approach would be better some other way.

A brief synopsis of the issue.
VBA 6.5
No Office or DB software available.

1: I need the ability to populate a combobox with data from a single column in a .csv file. Currently loading that file when a display is opened. If it would be better to have that file imported rahter than loaded, it wouldn't be an issue.

2. Need to parse data from a row in the .csv file to pass to an argument that adds trend pens to a historical trending screen.

3. Need the ability to filter the data that populates the original combobox so that a user can choose to look through all data, or only certain area data. This could be done either with another combobox, or with buttons.

Numbers 1 and 2 are functioning very well. The combobox is being currently populated when the screen is opened. This is also something that could be changed to require a button push after the filter is applied. Currently, the filter is the 6th column of data in the .csv. This could be moved into its own file, or the column could be moved within the .csv if that makes it easire to accomplish the desired result.

Thank you all for being patient with a relative newbie. Your expertise is incredible!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35754357
@BryanKipp

This should look something like this:
Dim strFilter As String
Dim vItem As Variant
strFilter = txtFilter.Text

For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem)  Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
  End If

Next

Open in new window

0
 

Author Comment

by:BryanKipp
ID: 35754575
Please forgive what is likely an obvious question...

Were would in insert this. I wrapped the IF/ENDIF around my ComboBox.AddITem strContent(0) and it appeared to get stuck in an endless loop

Code is below:
strFilter = txtFilter.Text

For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
  
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   Pen2_ComboBox.AddItem strContent(0)
   Pen3_ComboBox.AddItem strContent(0)
   Pen4_ComboBox.AddItem strContent(0)
   Pen5_ComboBox.AddItem strContent(0)
   Pen6_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
End If
Next
Wend

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35755986
The loading of the dicTags must come before the filtering.  It is most likely that you would load the dicTags data when the program initializes or first form loads.  It is only when the user wants to filter the data that you would clear a listbox/combobox and then use the code snippet I posted to fill it with items that match the filter, via dicTags iteration.
0
 

Author Comment

by:BryanKipp
ID: 35756428
I'm getting a "Type Mismatch" in line 5 of your code snippet.

I did find a cumbersome way around all this. I'm manually inputting the Filter areas into a combobox, then have a piece of code that looks for the existence of a filter and loads different .csv files based on the filter criteria.

It has become painfully obvious to me that I need to do more learning of how some of these functions work! I thought I had a grasp on the dictionary object, but have figured out that my understanding is flawed.

I don't want to waste any more of your time on this, but if someone has the time and could post a working piece of code that accomplishes the entire goal, it would be great!! I will go ahead and post the working code and the .CSV file for reference. ArrayTags.CSV
Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
  
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear
Pen5_ComboBox.Clear
Pen6_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   Pen2_ComboBox.AddItem strContent(0)
   Pen3_ComboBox.AddItem strContent(0)
   Pen4_ComboBox.AddItem strContent(0)
   Pen5_ComboBox.AddItem strContent(0)
   Pen6_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Function Add_Trend_Pens() As Boolean
Dim strParsed() As String


On Error GoTo ErrorHandler

If Pen1_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen1_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen1_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen2_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen2_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen2_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen3_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen3_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen3_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen4_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen4_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen4_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen5_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen5_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen5_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen6_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen6_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen6_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If


Add_Trend_Pens = True

Exit Function

ErrorHandler:

    Add_Trend_Pens = False
    MsgBox "Error adding trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function
Function Remove_Trend_Pens() As Boolean

On Error GoTo ErrorHandler

While Example_Trend.Pens.Count > 0
    Example_Trend.Pens.Remove (1)
Wend

Remove_Trend_Pens = True

Exit Function

ErrorHandler:

    Remove_Trend_Pens = False
    MsgBox "Error removing trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function

Private Sub RefreshTrend_Button_Released()

On Error GoTo ErrorHandler

' Uncomment out the if statement below to remove the pens

If Remove_Trend_Pens() = False Then
    Exit Sub
End If

' Uncomment out the if statement below to add the selected pens

If Add_Trend_Pens() = False Then
    Exit Sub
End If

'Add the Trend command to make the trend refresh the chart with the new pens
'Here is a hint.  Type example_trend. below and then select the method from this list

Example_Trend.RefreshChart

Exit Sub

ErrorHandler:
    
    MsgBox "Error refreshing trend.  Error:  " & Error, vbOKOnly, "Trend Error"

End Sub
Private Sub RemovePens_Button_Released()

Do
    Example_Trend.Pens.Remove (1)
Loop Until Example_Trend.Pens.Count = 0

End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35757261
@BryanKipp

What kind of user interface do you present for the user to specify a selection criterion?

What do the six Pen combobox controls represent?
0
 

Author Comment

by:BryanKipp
ID: 35757310
This application buids an operator interface in an automation platform for a water treatment plant. This particular screen creates a historical trend of actual floating point data coming from the various processors in the plant. The software package is Rockwell Software FactoryTalk ViewSE. It comes with VBA 6.5 as an included package.

What I am allowing the user to do is to select which values they want to see trended. They may want to see different data based on the current plant status. The interface has the actual trend, the buttons to refresh the trend and remove pens, and the six comboboxes.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35757465
what about the relationship between these controls and the filtering?
0
 

Author Comment

by:BryanKipp
ID: 35766437
There are several major areas within the total system. There is a possibility within the system that we could have as many as 200 tags we could select from to trend. The filtering is designed to be able to break the number of tags down to a more manageable number to search through when setting up the trend.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35778540
>>I'm getting a "Type Mismatch" in line 5 of your code snippet.

which code snippet? (which comment?)
0
 

Author Comment

by:BryanKipp
ID: 35780442
It was in Answer ID: http:#35754357

I'm of the belief there is likely a better way to accomplish this if I start from the beginning. Rahter than adding the filtering at the end, it should probably be incorporated at the beginning with the filters applied before I ever load the comboboxes. I will need a better understanding of the dictionary object and will research that this evening.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35780627
do you still have dicTags defined?
Did you place this code snippet in a different form?
0
 

Author Comment

by:BryanKipp
ID: 35785796
I started with the code that was currently working, so yes the dicTags are still defined.

If you look at answer ID: http:#35754575 , you will see where I modified that code.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35786178
>>If you look at answer ID: http:#35754575 ...
1. If this is the place where you are filtering the comboboxes, it is assumed that  dicTags has already been populated.  Therefore, you shouldn't have and file I/O or population of dicTags statements.

strFilter = txtFilter.Text
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear
Pen5_ComboBox.Clear
Pen6_ComboBox.Clear

For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
  
    strContent = Split(strHeadings, ";", 2)
    Pen1_ComboBox.AddItem strContent(0)
    Pen2_ComboBox.AddItem strContent(0)
    Pen3_ComboBox.AddItem strContent(0)
    Pen4_ComboBox.AddItem strContent(0)
    Pen5_ComboBox.AddItem strContent(0)
    Pen6_ComboBox.AddItem strContent(0)
  End If
Next

Open in new window


2. The comboboxes would be populated after dicTags has been populated.  However, there would not be any filtering done, since we're probably in the Form_Load stage of UI creation.  All the items would be added to all six combobox controls.
0
 

Author Comment

by:BryanKipp
ID: 35787357
What I have posted below seems to try to work, but it only populates the box with the last line of the .CSV file.
Option Explicit
Dim dicTags As Object
Dim strHeadings, strContent() As String
Dim iCtr As Integer
Dim vItem As Variant



Private Sub Display_AnimationStart()
Set dicTags = CreateObject("Scripting.Dictionary")

 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings



Pen1_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

End Sub

Private Sub btnLoad_Click()
Dim strFilter As String

strFilter = txtFilter.Text
For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
    Pen1_ComboBox.AddItem strContent(0)
  End If
Next

End Sub

Open in new window

0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 35787470
You need to use the dicTags keys as the combobox population values.  Don't forget that you should clear the combobox before you populate it with filtered values.

Example:
Private Sub btnLoad_Click()
Dim strFilter As String

strFilter = txtFilter.Text
For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
    Pen1_ComboBox.AddItem vItem
  End If
Next

End Sub

Open in new window

0
 

Author Closing Comment

by:BryanKipp
ID: 35787640
Thanks so much for your patience. This is now fully finctioning and will make the boss and the customer very happy!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question