Entering a Search Function on a Form

[jajahnke]

Hello,

I just posted awhile ago a database to help me with a search function. I have rethought my entire database but I still need help.

The attached database is a place where teachers can go to search for book collections that I have for them to borrow. I want them to be able to search for books by author, title, genre, or lexile level and then have that information displayed on the form. If they want to do a new search they could then just select new criteria and enter in their new search.

Could anyone help me with this? I am new to macros in Access.

Thanks,
Jajahnke

 

[Alansidman]

I've taken a little different approach. Look at the Search Form I created and run it. If you have questions or wish to modify by adding combo boxes, etc. then post back. You can search on one, two, three or four criteria.

Alan

 

[jajahnke]

This is wonderful. Thank you for your help. I was wondering, could I get the genre's in the book search form to be a drop down so the user would just have to choose one? Also, when I open the database it gives me errors saying that are some form's that have the wrong name. Should I be worried about that?

Thanks for all your help. You are a lifesaver!
jajahnke

 

[jajahnke]

Sorry, one more thing. Could I have the user search for a lexile within a range? i.e. 300-500

Thanks again.

 

[Alansidman]

Ok. Gave you a drop down for genre. For Lexile, you can enter a specific number or if you want a range from 200-299, enter 2, if you want all 410-419 then enter 41. Can't quite figure how to expand as you requested.

For title and Author you do not need to enter complete title or author, ie. Enter Laura in Author and you will get all authors with Laura in their name.

For titles if you enter Little, you will get all titles with Little in the title.

I fixed the issue of an unknown form not found. It was set as the default in the properties. Did you use a template to make this?

I hope this is what you are looking for. You should reverse engineer the search form and query to make sure you understand what I did.

 

[jajahnke]

Thank you, Thank you , Thank you! This is wonderful.

I appreciate all your time on this. Have a great rest of the week.

jajahnke

 

[Alansidman]

You are welcome.

 

[jajahnke]

Hi Alansidaman,

Are you still there? I have a little problem. When I add new books to my table and then I search for them they don't appear. I couldn't figure out why. Could you help me?

Thanks
Jajahnke

 

[Alansidman]

Some of your fields are blank. Is this just an input issue or will it be an ongoing issue. Currently, because the criteria is looking for populated fields within records, it doesn't know what to do with those that are null.

 

[Dairy Farmer]

This may not work for your application, but others searching this forum looking for a simple search function may find it usefull.

A very simple way to do searches is to use combo boxes. These have "seach as you type".

Type "D" and you go down the list to the start of the "D's".
Next type "A" and you go down the list to the start of the "Da's". etc
The user still sees the whole list, so they can expand the list once they get to a spot they want. "Is that book called 'Colors of Paint' or 'Colours of Paint'?"

You could have multiple combo boxes: Author, Title, Genre, Edition, ISBN No,.......

 

[YNWA]

I use this, allows me to see the filtered results as I type.

http://www.access-programmers.co.uk/forums/archive/index.php/t-188663.html

 

[jajahnke]

Alansidman - It's not a mistake on the null fields. Some of the books don't have information for those fields. Would it work if I just put null in those fields?

 

[Alansidman]

Are you entering your data directly into the table manually? I see that you have a lookup in your table. This is a no-no and I think that this is probably causing the issue. Look here

http://access.mvps.org/access/lookupfields.htm

You should change your method of data entry to a form. Set your lookups in your forms and delete them from the table.

Basic relational database principles say you should enter your data in a form and not directly into tables. Users should not have access to tables. They are for stoting data only.

Once this has been resolved, then the searches should work as designed. This should be a quick fix.

I think that this is why the data is not appearing in the queries. If I remove the lookup table from the query then I can get all the data, but then you are limited to running parameter queries and not using the search form which is not suitable for users. Make the changes and I am confident of positive results.

Alan

 

[Alansidman]

Had some time, so I fixed the issues discussed above. Gave you an input form and took the look up out of the table and put it in the form where it belongs. Made all null fields "na." Made the form, single, but you can change it datasheet view if that makes you more comfortable. Solved the issues with the nulls. The attached should work for you. Good luck

Alan.

 

[jajahnke]

Thank you so much Alansidman! You saved me so much time. My only question back to you is, should I be entering data in the "Bookroom Search Form" or the 'Search Form"? Does it matter?

Thanks again for all your help with this.

 

[Alansidman]

I have to run out for awhile, but it looks like I posted an earlier version -- not the correct one. It is on my other machine. I will repost this afternoon with the correct db. It has an input form that is different from the one's shown. My apologies.

Alan

 

[Alansidman]

Ok. Back and found the right db to upload. Note that I replaced all Null records/fields with na. This is important to have something in those places. If you prefer to put something else there, then you can modify those records with an update query.

Attached is the correct version of the db. The input form is called "frmBookroom."

Best of luck using this.

Alan

 

[FoxRocks]

Wow! Alansidman, I stumbled across this thread after doing a search and I just wanted to tell you how awesome you are! You were so much help to this person and really went above and beyond, that is so great. It's people like you that make online forums the true treasures of the internet. Thanks so much!