druddle

Hello

I have created a simple database (hahaha no such thing...) in Access for my CD collection. The table has 4 columns, MusicCollectionID, Title, Artist and Albumname. I created a form so that I can enter the information.

What I want to do is create some sort of a front end so that I can search the database for artist/title and get a list out. Dont want to have to create a report every time, as I have played with the report wizard and its a bit clunky for what I want.

Does anyone know how to do this ?

Thanks alot !!!

Dave (the insomniac)

bob269

Create a query including all of your fields, then in the fields you wish to search on enter this

Like [Enter Title To Search For] & "*"

The text can contain what ever you like, this is what it says on the screen when you run it. If you want to search on multiple fields simply enter the same code above in any of the fields in the query you wish to search on.

Next thing is to create a button on your form, right click the button and edit the properties and on the "on click" event enter this code.

Private Sub Command109_Click()
On Error GoTo Err_Command109_Click
Dim stDocName As String
stDocName = "Database Search"
DoCmd.OpenQuery stDocName, acNormal, acReadOnly

Exit_Command109_Click:
Exit Sub

Err_Command109_Click:
MsgBox Err.Description
Resume Exit_Command109_Click

End Sub

Command109 will be the name of your button, probably command1 in your case. "stDocName = Database Search" is the name of the query you created above. Call it what ever you like.

Hope this helps (and makes sense)

druddle

Hi Bob

So I have gone to Queries and selected Create by using wizard. I selected all fields and clicked next. Then It gives me the options to open the query or modify the design. So i click open and I get a table of information, or modify and I get the query design. Is this where I put in the "Like" statement (in the criteria field) ?

I have created the button as you said and when I enter text to search for, whatever I put it comes back with nothing found. I also dont want to explicitly match the text I put in for the search, e.g. if I put "jackson" I want it to find Michael Jackson, Janet Jackson, Miss Jackson by Oukast, etc

Dave

bob269

edit the query in design mode and on the line where it says criteria paste the Like command against the fields you want to include on the search.

use this instead

Like "*" & [Enter Title To Search For] & "*"

the * eitherside will tell it to check anypart of the name so entering Jack will find Michael Jackson.

bob269

if you want to search on album artist or title it may be worth creating 3 queries and putting the Like command against the appropriate field.

Then create 3 buttons on your form, and change the docname as required

ie stDocName = "Query1"

then button 2 would saystDocName = "Query2" and so on

or insert whatever names you give the queries.

If you choose to go for the one click option then it will simply prompt you 3 times for the criteria, you can simply leave the boxes blank that you dont wish to search on, so Artist you would enter "Jackson", then the next 2 boxes that pop up asking you for title and albumname you can leave blank if you wish.

druddle

Hi Bob

Am getting in a right pickle with this !!! I have created the 3 search buttons and when you click each one it comes up with the same ambiguous pattern error......will keep trying with it !!

Dave

druddle

Right fixed that problem (typo !!)

I have got the 3 searches working, and if i search on artist for "Jackson" I get a load of results in a form But the form only has the artist column in it. Is there a way I can search on one column (like artist) but display all columns, so I can tell which titles and albums all the tracks by "Jackson" are ?

Dave

bob269

It's the way I tell em

Upload it to some webspace or PM me and i'll take a look at it for you if you like

bob269

Yup, make sure in your query you have all 4 fields selected with a tick.