Creating a search box in a form (Access 2003) HELP

[June7]

I expect your options are:

1. Build a table that has the names of tables and queries you want to allow search on. Make this table the RowSource.

2. A list typed in the RowSource and RowSourceType set to ValueList.



3. Give tables/queries (actually, recommend use just queries so you can limit the fields available) a name with a prefix like S_. Use this prefix as criteria for the RowSource SQL that retrieves the list from the MySysObjects table.
SELECT [MsysObjects].[Name] AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects
WHERE Left([Name],2)="S_"
ORDER BY [MsysObjects].[Name];

[eternalearth]

I guess my real issue is that, I have multiple tables and I would like the search "engine" to be able to search between all of them...the search that was given earlier was helpful, but it is so overcomplicated, and I just want to keep it simple and clean. The people who will be using this will know even less than I do and I want it be straightforward...I just am so lost. I have been searching google and youtube for weeks. This thread is the closest I have to an answer; would anyone be willing to just slow down a bit and go step by step? Don't forget I have my database in the first post, so please take a look at that so you can see the tables and the form... so lost and confused!

[June7]

You have multiple tables with the same structure? Doesn't sound like db is properly designed. Want to provide for analysis? Follow instructions at bottom of my post.

[eternalearth]

searchdatabase.zip

Please look at my database above and you can determine what EXACTLY it is I am doing wrong. I will being to use the link at the bottom of your post.

[June7]

Consider: Software is installed on computer, not employee, maybe just associate it with computer, not employee. Same goes for most of the hardware. CASE and MONITOR being items that could be associated directly with employee, but RAM, NIC Speed?

Otherwise, data structure looks okay.

I don't understand what you mean by 'search between all of them'. How can that 'keep it simple and clean' more than the suggestions already given?

Why is the textbox on Search form labeled 'Delete'? The code for the search form references Form_frmCustomers which doesn't exist.

Attempt some code that has some chance of running and we can help you analyse bugs.

[eternalearth]

By simple and clean I mean, please tell me in the simplest way you know how. I have a hard time understanding things from an expert when I am still just a novice.

I am working on the databases now, I will post them soon!

[eternalearth]

Okay, I have been working on this database. This is what I have come up with so far.

1) I can't get rid of the relationships between Employee ID and Software/Hardware, otherwise the View Report button at the top will not function properly. If you know how to make it work otherwise, please show me/let me know.
2) I created a query of all the tables I wanted to have searchable fields in. I then made a combo box of that query on the MAINFORM along with a text box and command button. But beyond that I am stumped.

Here is the updated database for you :MariaDB2003.zip

[June7]

1) If you are satisfied with the relationships then stick with it, just don't think it was how I would have done it.

2) The query won't work because of INNER JOINS. Consider:
SELECT ....
FROM [Computer (Software)] RIGHT JOIN ([Computer (main)] RIGHT JOIN ([Computer (hardware)] RIGHT JOIN Employee ON [Computer (hardware)].[EMPLOYEE ID] = Employee.[EMPLOYEE ID]) ON [Computer (main)].[EMPLOYEE ID] = Employee.[EMPLOYEE ID]) ON [Computer (Software)].[EMPLOYEE ID] = Employee.[EMPLOYEE ID];

The resulting combobox drop down is the longest I have ever seen. Not practical and not what I think you want to do. Why would you make this query the combobox RowSource? The query should refer to combobox as input parameter. What criteria should be searched - EmployeeID?

[eternalearth]

I don't think IDs would be a criteria because not everyone using the computer will know an Employee ID's or Computer ID's.
I need to be able to make the search in such a way so that a user could type in say, "Dell or HP, and all the Computers/Employees that had a Dell or HP in their name would be filtered. Or a user can type in an employee's name to find them specifically. And sometimes, you need to look up a specific serial/model # or even a type of software/hardware. That's why the combo box is so long.

Would it be best to create four seperate queries (Employee, Computer Main, Computer Hardware, and Computer Software) and have four seperate search boxes?

[June7]

My point is I think your approach is not workable nor what you want.

That query as combobox RowSource does not allow variable search by entering different data into the combobox. All it does is allow a record from the list to be selected and then search can be done on the ID of that record.

Options:
1. The 4 comboboxes you suggest and parameterized query with criteria referencing each combobox as demonstrated in link referenced in earlier post.

2. The combobox could be a list of the fields user can choose to do search on. Then based on the field selected, another combobox could list valid values from the records. This option probably requires more code.

[eternalearth]

Okay. I will work on this and keep you posted! I hope to have a better job done by Friday!