Search function for library database

[mike55]

Last year someone posted a question asking for help creating a search function for a library database here: https://www.pcreview.co.uk/forums/thread-1182888.php. The idea was to enter some words in the book title, be presented with a list of hits, and be able to find the book category by clicking on one of the books.

Then, someone else posted an answer as I have typed in below (I cannot find the previous post).

Assume the table named TblBook has the following categories:
BookID
BookTitle
Category

Create a query named QryBookSearch based on TblBook including all three fields in the order shown above.

Set Sort for BookTitle in QryBookSearch to ascending.

Put the following expression in the Criteria of BookTitle in QryBookSearch:
Like "*" & Forms!FrmSearch!SearchForTitle & "*"

Make a form named FrmSearch and:
1. Add a textbox named SearchForTitle
2. Put the following code in the AfterUpdate event of SearchForTitle:
Me!MyBookList.Requery

3. Add a textbox named BookCategory

4. Add a listbox named MyBookList, and do the following:
a. Set Row Source to QryBookSearch
b. Set Bound Column to 1
c. Set Column Count to 3
d. Set Column Width to 0;1.5;0
e. Put the following code in the AfterUpdate event:
Me!BookCategory = Me!MyBookList.Column(2)

Enter the title of the book to be searched for in SearchForTitle and press enter. The titles of all books that contain the words entered in the textbox appear in the listbox. If one of the books is selected, its category appears in the BookCategory textbox.

I am using Access 97. I have followed the instructions above several times (each in a new copy of the original database). By doing it several times, I hope I have eliminated the possibility of bad syntax. I have also carefully checked everything each time.

If I open the QryBookSearch query, it presents me with a window into which I can type a string and it gives me a list of matching books. However, when I open the FrmSearch form and enter a string into the SearchForTitle textbox, it gives me the following message:

Microsoft Access can't find the macro 'Me!MyBookList.'
The macro (or its macro group) doesn't exist, or the macro is new and hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.

Any help would be greatly appreciated.

Mike

 

[mike55]

I have discovered what I was doing wrong. The solution was posted by PC Datasheet at the end of the thread I pointed to above, but I did not understand it for quite a while. In an attempt to close this out and because I always feel that I have an obligation to a forum when I datamine it to find an answer to my question, I offer the following.

I have expanded steps 2 and 4. I do not know why they do not work as shown above in the previous post.

2. Click on the SearchForTitle textbox, and:
a. Click on the Properties icon in the toolbar
b. Click on the Events tab
c. Click on the white box where it says After Update (if any code is in there, delete it)
d. Click on the three dots that appear at the right
e. Select Code Builder in the dialog box that appears and click OK
f. The code window will appear and there will be two lines that look like:
Private Sub SearchForTitle_AfterUpdate()

End Sub
g. Enter the following code on the blank line: Me!MyBookList.Requery
h. Close the code window
i. It should now say [Event Procedure] in the After Update property of SearchForTitle

4. Add a listbox named MyBookList, and do the following after bringing up the Properties for the listbox:
a. Set Row Source to QryBookSearch
b. Set Bound Column to 1
c. Set Column Count to 3
d. Set Column Width to 0;1.5;0
e. Click on the Events tab
d. Click on the white box where it says After Update (if any code is in there, delete it)
g. Click on the three dots that appear at the right
h. Select Code Builder in the dialog box that appears and click OK
i. The code window will appear and there will be two lines that look like:
Private Sub MyBookList_AfterUpdate()

End Sub
j. Enter the following code on the blank line: Me!BookCategory = Me!MyBookList.Column(2)
k. Close the code window
l. It should now say [Event Procedure] in the After Update property of MyBookList