Hi everybody,

First of all, a word of warning, my knowledge of MS Access is severely limited, please bear with me.

I’m working for a company during my university summer break. I have been tasked with creating a database (thank god my boss doesn’t want anything fancy) for storing the minutes of management meetings, which should also include a comfortable search engine.

So far, I have created two tables:

The first is the master table (tblSitzung) which contains the following columns with information regarding the meetings itself (I’ll write down the original German titles of the columns and provide a translation in brackets)

sitzung_id (the meeting-ID, which is the primary key)
sitzung_datum (the date of the meeting)
sitzung_art (management level involved)
sitzung_teilnehmer (participants)


The second table (tblThemen) contains the minutes and has the following columns

themen_id (the ID of the respective item on the agenda, the primary key)
sitzung_id_f (the key with which the table “tblThemen” is linked to the master table)
themen_fragestellung (the question raised in this particular item on the agenda)
themen_input (well, the input from the participants :-)
themen_ergebnis (the course of action that has been agreed upon)

Now, the tricky part is creating a search form. Since I lack any understanding for VBA code or queries, I have spent days looking for a solution in various forums. I do realize that there are plenty of examples of how to create a search form out there, but none of them seems to be suitable for my particular requirements. Well, maybe they are, but since I don’t really understand VBA, I haven’t really been able to figure out how to customize sample codes so that they fit my database.

What I want to do is create a form that basically consists of a simple text field and a “search” button. The database user should be able to enter a word into the text field and then click on the search button. The search should be performed within the columns sitzung_fragestellung, sitzung_input and sitzung_ergebnis in the table “tblThemen”. Then, a new form should pop up which includes the respective item (or items) of the agenda that include the word entered in the search field plus the date, the participants and the kind of the meeting. So, if a keyword is found in the column “themen_fragestellung”, the search result form should also include the respective fields of “themen_input”, “themen_ergebnis” plus the “sitzung_datum”, “sitzung_art” and “sitzung_teilnehmer” that belong to it.

I don’t know if what I have envisioned is easy to accomplish or not. I do hope that it is easy, so that anybody of you who is willing to help me out won’t be busy for too long.

I have uploaded a sample database filled with some junk data, which consists of the tables I have explained above.

I would be greatly indebted to anybody who has a look at it and could perhaps include the search form as outlined above. Just coming up with a VBA code for the search form would be great as well (I guess I could take it from there once I have the code :-)

Any other input, hints or ideas would be greatly appreciated as well!

I apologize in advance if I have misjudged the scope of the problem and asked something of you that would require too much input on your part.

I hope to receive feedback soon! If I need to clarify something, please let me know!

Best regards,

Ian

Here's one example of what can be done. Open the Form Frm_Search in the attached file to see if it fits your request.

The algorithm I used is based on brute force and is not very efficient. Depending on the performances level of the system where the program is installed it can become very slow if there are many rows in the tables. It should be all right on any average modern system for a range of 1 to 5000 records.

Do not hesitate to come back if you need explanations on how it's implemented.

Thank you so much!!! I can't express how grateful I am for your help!!

Your solution is absolutely beautiful and works great! I guess it will take years till the database comes anywhere near 5000 records, so a crude mechanism (which looks pretty damn sophisticated to me anyway :-) which gets the job done is perfectly fine.

I guess should my boss require any more functions when he returns from his vacation in two weeks, I should be able to figure it our for myself somehow. The only thing I could think of right now is a "print" button (on the result form that pops up after a search is conducted) which would enable the user to print a report of the results. How could that be implemented? But please feel free to tell me to go figure it out by myself or go pester someone else :-) you have already been so helpful I wouldn't mind at all :-)

Here's a solution (again quick and dirty).

Absolutely brilliant!! Thank you so much!

You're welcome!