MS access Search help!!!!

[elliotth123]

Trying to make a search engine type of query.
Looking for coding
I have a table of books
I want to be able to open a form with seperate txt boxes Title, ISBN, Author etc.. (already created)
and have it search the table
Then generate a report/table/query that shows a list of books that are related to that word or keyword typed.
The list should allow you to click a title and get full details.
Let me know. I know this is possible and not to hard to do, but beyond my expertise
Thanks

 

[KenHigg]

When you press a button to run the report, have the code check each of the txt boxes for values and then use if statements, etc., to build a custom sql string and have the report's record source equal to this string.

???
kh

 

[elliotth123]

SOunds good,..... but how!

Sorry I am new at this! Can you help me do this anyone?

 

[KenHigg]

Have you ever done any programming?

kh

 

[Jon K]

I have attached a sample database. You can open the form, enter some words to search and click on the command button to run the query.

If you want, you can build a report based off the query.
.

 

[elliotth123]

Thank you!

Wow thank you so much for that example. I have been looking for ever for just that type of sample! Now I just have to get the querey results to open up a record if i select it. Think it's possibe? Thanks!!

 

[Jon K]

I have added a list box and two command buttons on the Search Form in the database.

You can select some books on the list box and click on the command buttons to open a report or an edit form.


Note these properties of the edit form:-
Modal = Yes (so that the form keeps the focus until it is closed)
Allow Deletions = No
Allow Additions = No

.

 

[elliotth123]

Jon

Jon you are absolutly the best! I owe you! You have saved me so much time. Thank you for being patient and actually going the extra mile to show an example!

 

[elliotth123]

Can you help some more?

Jon, Thanks again for your help, I finally got around to adapting that file you sent me to my database and it works great except I can;t get the filter to work right. It doesn;t seem to pass the selected book on to the new form. Instead it passes the first book on the list. ANy help would be great!!

 

[elliotth123]

Anyone?

Anyone able to help??? PLEASE!!!

 

[EMP]

In his second sample database, Jon K has provided a very good example to open a form using a multi-selection list box as a filter:-

   Dim vItem As Variant
   Dim sCriteria As String
     
   ' note the selected BookIDs.
   For Each vItem In Me.lstBookTitles.ItemsSelected
      sCriteria = sCriteria & "," & Me.lstBookTitles.ItemData(vItem)
   Next
   sCriteria = "[BookID] in (" & Mid(sCriteria, 2) & ")"
   
   ' open Edit Records form for editing selected records.
   [b]DoCmd.OpenForm "Edit Records", acNormal, , [color=red]sCriteria[/color][/b]

What is the code that you are using?

 

[elliotth123]

Coding

My coding is exactly the same. Different Objects but everything else is the same. I do notcie on mine compared to The one Jon K made. A filter is not being applied to the results of mine. WHat am I doing wrong.

 

[elliotth123]

filter

Any one know how to reapply this filter?

 

[elliotth123]

Help!

Anyone know what I am doing wrong?
Jon K you made the file originally what am I doing wrong here?

 

[Jon K]

DoCmd.OpenForm "Form Name", acNormal, , FilterString

is universal code. Without seeing your database, it's impossible to say what's wrong.


In my sample database, the Edit Records form was created using the form wizard and the Record Source of the form is the whole query "qrySearch". If you add a command button on my Search Form to open the Edit Records form without applying the filter, that is

Private Sub Command0_Click()

   DoCmd.OpenForm "Edit Records"

End Sub

every record in the query will be passed to the form.


You have to identity whether the problem is with the filter string or with the form itself.

When you open the form without using any filter, how many records are passed to it?
.

 

[elliotth123]

Access Search

Well, I went through everything step by step. The Coding is the same within VB.
After adjusting some things and confirming others, I get an error when opening the form saying the Open Form action was canceled.

Run time error 2501

I went through it all again. It all wroks great except the edit record function...

I just get the error stated above. Any ideas?

I also took the filter string part out of the code just to test and it does exactly what you said it passes the whole database.

If you want I can put the database up here for you to look at. Thanks again!

 

[EMP]

Since your form can open with all the records, maybe it is the filter string that causes the problem.

You can compact your database. Zip and post it here for us to have a look at it.

 

[elliotth123]

Database

Here is the database.
I know it's not perfect but hey it's a work in progress. Any help / suggestions would be appreciated!
I am very new to most of this again.....

I had to remove most of the functions of the rest of it to fit under 100kb so basically it is just the search part... but i think it will do.

If you figure it out please exmplain it to me if you don;t mind just so I can try and understand, Thanks!!

~Elliott

 

[EMP]

You can change the data type of the Number field from Text to Number in table desgin.


Or, if you want to keep the Number field as Text, delimit the selected numbers by single-quotes in the code, that it, change the line in the filter string from:-

sCriteria = sCriteria & "," & Me.lstBookTitles.ItemData(vItem)

to this:-

sCriteria = sCriteria & ",'" & Me.lstBookTitles.ItemData(vItem) & "'"


Text values need to be surrounded by quotes.

 

[elliotth123]

WOw that was it! Thanks. I can't believe it was something so simple!
I was going to change it to number But in the end I need it to be text as it is going to be a combo of text and numbers. Thanks for your help. I think that is it for now!