Solved

search column of listbox

Posted on 2008-06-10
6
426 Views
Last Modified: 2013-11-27
Hi, I have a multicolumn listbox.  It contains 4 columns.

How do I search if a word exists anywhere in column for.

something to the effect of...

if lstbox.columns(4) = "Yes" then msgbox ("There is the word 'Yes' somewhere in this column!")

0
Comment
Question by:NO_CARRIER
  • 4
  • 2
6 Comments
 
LVL 3

Expert Comment

by:bandriese
ID: 21753607
As far as I know you can't seach the contents of a list box. What you can do is create a query the is exactly list the Row Source Property for your list box and add perform your checking on the external query. Since I don't know what you're soing exactly there are several possible ways to get what you're looking for. For example, if you want to see all records with that "Yes" in column 4 then in your query put "Yes" in the criteria box for the coresponding field (presumibly the 5th one since columns start at 0). If you are trying to see if a record exists or not, then I would build a function to do that or put the code in a sub routine in your form. The code might look something like the below. Then in your form.. use somthing to call the function like:

Dim MyValue As String
MyValue = CheckForRecords()

If MyValue = "True" Then
  'Yes there are records, Do something
Else
  'No records, do something else
End If


 
Public Function CheckForRecords()
'Save this function as a module
 
Dim db As DAO.Database, rs As DAO.Recordset
Dim SQL As String
Set db = CurrentDb()
SQL = "SELECT * FROM Table;"  'Put your SQL code here
Set rs = db.OpenRecordset(SQL)
 
If .EOF Then
     CheckForRecords = "False"
  Else 
     CheckForRecords = "True"
End If
 
'If you want to do something with the individual records, you can use a loop like this
'With rs
'   While Not .EOF
'       Do something with the records here
'       .MoveNext
'   Wend
' End With
 
Set rs = Nothing
Set db = Nothing

Open in new window

0
 
LVL 3

Expert Comment

by:bandriese
ID: 21753628
In case you use it. I forgot to say that my code example uses uses DAO rather than the newer ADO. The DAO method will work fine, but DAO Objects 3.6 must be enabled under Tools | References in order for this code to function. If you need ADO, let me know.
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 21753689
Hi Band... I don't think a select query would work in this instance, as the record itself hasn't actually been created at this point.  

I have an unbound form with unbound controls.  One of which is the multi-column listbox.  Basically when I remove an item (or add an item), I need to know if any of the items in the 4th column have a certain property set to True.  If it is true, then it will change the validation rules for eventually adding the record to the database...
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 3

Expert Comment

by:bandriese
ID: 21753775
Okay, so the unbound form and controls is fine. I'm not sure what you're adding or removing from or to. But perhaps you can add code in the On Change or After Update property of the ListBox to check the value. Otherwise, perhaps you can attach a mdb file with just your form in it so I can see what you're doing, if that's possible.
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 21754071
I have a listbox with 4 columns.
I have a series of comboboxes, and controls to add items to the listbox.

When the user clicks [Add Item] it will add items to the listbox.
When the user highlights an item and clicks [Remove Item] the item is removed from the listbox.

When the user has completed filling out the form and wanted to create the record they then click [Create Record], at which point I need to validate the 4th row of the listbox to see if any of the items have "True" listed.  If any of the items have True, then my insert statement will be different.

The problem about checking when adding/removing items is there can be multiple items with True, all, or none.  So I will need to check after the user has complied their list in the listbox.
0
 
LVL 3

Accepted Solution

by:
bandriese earned 50 total points
ID: 21754294
Ah, I see. Well this is something I havn't done below. Try something like the code below. In 'some code put your IF then's to check the fields.

I found this courtesy of DatabaseMX, see the question: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23439431.html

Hope that helps...
Dim iCount as Integer
 
For iCount = 0 to Me.ListBox.ListCount -1
   ' some code
Next iCount

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Store results in vb.net 3 20
Help with adding DLL file in Windows project 20 31
DCount Type Mismatch 2 22
aggregate query? 20 50
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

773 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