Unbound Search Box for Populating Results in Unbound List Box

[speciman_A]

Good Evening,



I am quite new to VBA and am wondering if someone has an example of the code I would use for a search form. Basically, I have one table I want to search by entering my search criteria into an unbound text field on a search form, and upon clicking the Search button it will compare my search criteria to one table, and then display the results in an unbound list box. I would then have the list box have a double-click event to open the desired result in the record edit form. I have over 4000 records in my table and need a timely way for the user to query for the record they need to edit. I have done a lot of googling and can't seem to get anything to work for me (but again I am a novice). I have 20 or so fields in the table I want to search but only need to display six in my search results list box. Any suggestions will be greatly appreciated.

V/R

Josh

[June7]

The listbox RowSource can be a dynamic parameterized query or SQL statement that references the textbox for criteria. Code in the textbox AfterUpdate event would requery the listbox. This is called dependent or cascading combo/list box.

Then code in listbox event could open form filtered to desired record.

DoCmd.OpenForm "formname", , , "some ID field=" & Me.listbox

Why do you need to search 20 fields for the same criteria?

[speciman_A]

June7,

Thank you for your prompt reply. I do not need to search all twenty fields off from my search box; I really just need to search through four. The user will search based on either name or SSN; however, I want a couple of other details to show in the list box just to help positively identify the person the user is searching for. The list box will only show six of the twenty fields in the table for each result. As for the SQL, I attempted to tailor some VBA I found on another website, but my interpretation of the code resulted in my database crashing. Can you by chance provide an example of the SQL statement for such a task? I assume I will use a SELECT statement to instruct the program to pull all records from my Employee_Data table where any of the fields match the search criteria? Thanks for your help.

I tried this, but (obviously) it doesn't work:

sqlString = "SELECT * FROM Employee_Data WHERE Employee_Data.Fields = SearchArray"

SearchArray is an array that holds the search criteria after spliting it after each comma (for when the user searches: Doe, John, S)

V/R

Josh

[speciman_A]

I tried using the following, but again, no luck:

"INSERT INTO Me.SearchResults (Battalion, Rank, Last_Name, First_Name, Middle_Name, SSN) VALUES WHERE Last_Name OR First_Name OR Middle_Name OR SSN FROM SM_Datatbl LIKE {SearchArray}"

SearchResults is the name of the list box on the form.

V/R

Josh

[rpeare]

June has given you the answer, you just have to cycle through your array to build your WHERE statement.

However, I think you are going to have difficulties unless your users *always* correctly type in the syntax you want (last name, first name, mi)

If you are using unbound controls would it not be easier to have them type their 'search' parameter in the actual field that you're searching, i.e. type 'smith' in the surname field then update the contents of your list box rather than parsing a string that may or may not be following the proper protocol.

[speciman_A]

rpeare,

Yes, if I understood what a dynamic parameterized query is, or that I had much experience with SQL. However, I will attempt your suggestion for now while I labor through Google and the couple of books I bought. Thank you for the suggestion, it should be fine until I get a better grasp of manipulating Access with VBA.

V/R

Josh

[rpeare]

Simple example using a global function module.

Speciman_A.zip

[speciman_A]

rpeare,

Wow, thank you! I really wasn't expecting you (or anyone, for that matter) to actually write the code for me, I was just hoping for a solid example so that I could figure out where I am going wrong. You have no idea how much I appreciate it, as this will help me exponentially with learning VBA.

My project has been exceedingly frustrating without any prior programming or database knowledge.

V/R

Josh

[rpeare]

I do not use bound forms and it became really tedious rewriting code so I wrote a series of global functions to help me build a database more quickly.

Just keep in mind the functions will not work if you do not follow the naming convention it expects

TABLE tblTest must use FORM frmTest and that form's list box must be lstTEST, all three have the same name, just the first three characters differ.

The data entry fields must have a TAG PROPERTY of DE followed by an indicator of what type of data it is (T for text, N for number, D for date)

[June7]

Here is code to cycle through a listbox and build WHERE clause: http://allenbrowne.com/ser-50.html