Thanks to the help received in other questions and some good tutorials I could create my access database to hold the books in my library and I have a good form now (with several sub-forms) to enter new books into my database. (It is all based only on access 2003).
Next step is to be able to query the DB and search for books.
I was considering taking the following approach but I am not sure if it is feasible and/or if the effort would be prohibitive. Indeed I already have my own concerns and/or question marks.
Could you please share your opinion on my approach and eventually address some of my concerns? (Links to other questions or resources that address any of my concern is perfectly fine!)
Thanks in advance for any contribution!
Point 1. Create a control form with buttons to perform different kind of queries (by Author, by Title, etc.); I would also like to have a “free text search” option for the whole database. This form will also include fields to enter the search criteria like, for example, Author for the search by author, etc. These fields shall be combo boxes with autocomplete so to facilitate the search to the user.
Point 2. The different queries to be executed shall be “hardcoded” in the vba code of the on-click event of the corresponding button; this is because I find it easier to write the SQL query manually especially when I have the need to pass on parameters from other fields; plus, I do not see the need to multiply forms and store queries separately in the DB when I can have them “stored” in my code.
Point 3. The final result of the queries (a record set?) shall then be displayed in a form. I will always select the same fields to be output, regardless of the query executed so a single form shall be used as output of all different queries. I want a form for this reason and so that I can customize its appearance and behavior once and for all. If possible the form shall be of type Datasheet (but this is not a strict requirement). Given the variance of the search criteria, I believe it is perfectly fine that the query results are stored in volatile tables/record-sets and it is not a problem that they have to be re-populated at each request.
Concerns on point 1. Is a free text search really feasible? How could I do this?
Concerns on point 2. It is easy to build up a string holding the SQL query with all the input parameters, but how do I run the query? My search in tutorials confused me and I am not sure how to do this anymore. As said I do not want to store the query separately and then refer to the specific query in the query collection but rather execute and “SQL string”. How is this done? And how can I store the results in a variable? Is Recordset the right variable type to store a temporarily table?
Concerns on point 3. How do I create the “output” form? Shall I create the fields (in design view) as unbound or shall I bind them to the tables in my database? And how do I populate the form with the data in the variable? Is a Datasheet feasible given that I have several Many-to-Many relationship in my database (like for authors and books). If I have to go again for a forma and sub-forms, how to I connect the sub-forms and the main form given that the “table” does not exist during design but only at “run time”? And I also do not have queries, because, as said these are different and hardcoded in vba code.
P.S. For information, all this is done as a “hobby” and in order to learn something new (access and vba) by doing it...
1 Answers
Point 1. Create a control form with buttons to perform different kind of queries (by Author, by Title, etc.); I would also like to have a “free text search” option for the whole database. This form will also include fields to enter the search criteria like, for example, Author for the search by author, etc. These fields shall be combo boxes with autocomplete so to facilitate the search to the user.
You can certainly use dropdowns to populate a form. There is a wizard for it. You cannot use dropdowns to search anywhere, but there is a handy search button, or you can concatenate and use InStr.
Point 2. The different queries to be executed shall be “hardcoded” in the vba code of the on-click event of the corresponding button; this is because I find it easier to write the SQL query manually especially when I have the need to pass on parameters from other fields; plus, I do not see the need to multiply forms and store queries separately in the DB when I can have them “stored” in my code.
You will see the point of keeping queries out of code when you start making accdes/mdes
Point 3. The final result of the queries (a record set?) shall then be displayed in a form. I will always select the same fields to be output, regardless of the query executed so a single form shall be used as output of all different queries. I want a form for this reason and so that I can customize its appearance and behavior once and for all. If possible the form shall be of type Datasheet (but this is not a strict requirement). Given the variance of the search criteria, I believe it is perfectly fine that the query results are stored in volatile tables/record-sets and it is not a problem that they have to be re-populated at each request.
Repopulating can lead to bloat. Always avoid temporary tables in MS Access if you can.
- 90,370
- 7
- 114
- 152
-
So what would you do instead? – rodedo Sep 07 '12 at 13:52
-
Instead of what? Temporary tables? You can nearly always build a suitable query. It is only when that fails that you need a temporary table. It should never be necessary when all you need is a search. – Fionnuala Sep 07 '12 at 14:06
-
Sorry, maybe I did not examplain my-self. I was describing the whole idea. You commented about the points but did not answer my concerns/questions, so I assumed you where considering the whole idea a failure therefore I am asking how whould you proceed instead. – rodedo Sep 07 '12 at 16:46
-
Your question is very open and my comments are equally open. I probably should not have answered because the question is not really within the rules of SO, IMHO. Yes, your search is feasible, post some details and you will get a more detailed answer. – Fionnuala Sep 07 '12 at 16:49