search Table1 displaying no Table1 data, and returning all poss. matches

[frustrateduser]

form search Table1 (displaying no Table1 data), and returning all poss. matches

hello all!

I'm new here and I have a problem I need to fix

i have (been given) an access database at http://www.recyob.org.uk/dvd_data.mdb.. feel free to have a look, it's only 170k ish.

Now, as you can see, on the main Navigation Form which pops up when the file is opened, there's a textbox search area with a button by it. as you can also see by its label, i'm trying to make it so that a user can search for a DVD based on the EPISODE title.

(bearing in mind one table has most dvd data, like DVDNumber, title, release date... but the second table holds just DVDNumber and Episode title... the unique DVDNumber is how we can tell which DVD holds which episodes).

You've seen my total-beginner way of doing the database; it would help save a lot of time if, when the user types in a word or two words (for example, "here") it either returns a message alter box of "No Match Found" or brings up a list of possible matches.

EXAMPLE:

If a user searches for "here"..

FP1205 || Rocky & Bullwinkle & Friends || Smoething Goes Here
JJ77946288 || The Adventures Of Indiana Jones || Another Thing Could Go Here
JJ77946288 || The Adventures of Indiana Jones || As Well As Here

... would be returned, since all three titles (the last bit of info) hold the word "here" in them.



And, please, no laughing at my database, I've got to do this since the Powers That Be told me so, but I have to point out that up until last week I had no idea how to do much more than the very simplest of things in Access... if I can get this to work, I'll be god's gift, and if i don't i'll no doubt be the scum of the earth, and since I'm overdrawn at the moment I'd rather be the former, as I'm sure you all can understand!!


Thanks to any and all that help me with this...

 

[Rich]

Your link doesn't work

 

[Dgar007]

Your link isn't working, but maybe you could use a filter.

I have used this in the past. Here is an example:

The RecordSource is the table or query you wish to use
The Filter uses the LIKE operator along with the * as a wildcard before and after.
The txtSearch is the name of the textbox that the user enters info into.

Me.RecordSource = "CORE"
Me.Filter = "Software like '*" & txtSearch & "*'"
Me.FilterOn = True

So in this case, let's say txtSearch = SOMETHING

I want to use the CORE table and look for anything in the SOFTWARE field in that table that has "SOMETHING" in it anywhere because the wildcards are on both sides of txtSearch.

Hope that helps you,

 

[RayH]

Remove the two dots from the end of the link.

 

[frustrateduser]

http://www.recyob.org.uk/dvd_data.mdb

was supposed to be the link (not sure where those dots came in!)...

Dgar007..... I've just been on a steep learning curve after not having a clue as to what you're talking about

I have this done now, thanks to your help, the filter works accuratly, it's perfect


but.... just some errors??
I can get the DVDNumber to show up fine, just no other information (it shows #NAME?).

What have I done wrong? The records it's fetching are accurate, I've manually checked what it brings up..... it just doesn't display names etc.

??

i.e. http://www.recyob.org.uk/snapshot.gif

 

[RayH]

In the form replace DVDTitle with EpsiodeTitle as the Recordsource of the field.

 

[RayH]

On second thoughts that's not what you want. sorry.

 

[Tim L]

You've changed the recordsource for the whole form in the OnClick event of the search button 'cmdSearch'.

The new recordsource doesn't have the title field. You could try creating a query which combines the data in both the tables, then make that the recordsource.

HTH

Tim.

 

[RayH]

Heres my attempt at Dgar007's suggestion. It seems to work.