Search KeyWords

[SoxPats83]

I am sure this is a repeating question here on the boards, and i appologize if it has been asked a million times already. i have been working on a form with a search field in it. i have been able to get this search field to work with exact matches, which is great. but what i am looking for is for a keyword search. for example if someone is trying to find an address of something like 123 Main St., instead of having to type 123 Main St., they can type Main, and a whole list of all the Mains will come up. any help would be appreciated, thanks!

 

[PNGBill]

search for *main* or *123*

 

[SoxPats83]

search for *main* or *123*

where would i place this? or how would the command go? is there a way to search for ANY word? like there may be thousands of words in the table. " ' " or something like that?

 

[PNGBill]

You make a query or add the search part to an existing query.

Say you have a query that returns all the records where FirstName is Fred.

Then your query would have in the criteria section of FirstName field Fred and only records with Fred as the first name will be returned.

If you want to input the name, use [Enter first Name] as the criteria and you will be asked "Enter First Name", you type Fred and wala, all the records with Fred as first name are shown.

If still not sure put up some field names and if there is more then one table say what table they are from and I will make an sql for you.

 

[SoxPats83]

You make a query or add the search part to an existing query.

Say you have a query that returns all the records where FirstName is Fred.

Then your query would have in the criteria section of FirstName field Fred and only records with Fred as the first name will be returned.

If you want to input the name, use [Enter first Name] as the criteria and you will be asked "Enter First Name", you type Fred and wala, all the records with Fred as first name are shown.

If still not sure put up some field names and if there is more then one table say what table they are from and I will make an sql for you.

i may be way out of my element here, but what i have is a sub form where the search results will be displayed, that subform's "Name" field is the child link to the master of a field on the main form's field of the same name. the search will be performed out of a tabbed section in the main form. i'm sure this sounds like taking the long route to get somewhere, but for exact words matches, it works. the source for both these "Name" fields is to a table field. what i am thinking right now is there a way to query that table's field and make the search of only keywords a bit easier by making the control dource for each of these "Name" fields that new query?

like i said, i may be way out of my element here.

 

[SoxPats83]

maybe something in the querie's criteria?

 

[John Big Booty]

You could have a look at the sample here, it is a search as you type search tool.

 

[PNGBill]

The subform should use a query as it's data source, even if all of the data is from only one table. Just make a new query with the exact data that your current form uses and replace data source name from TableName to QueryName?? and if the field names were the same then your form now looks to the query for it's data and not the table.

In the query, add the search criteria and when you open the form, you will be asked the question.

May sound like magic but this part is really quite simple.

The form when it opens needs to get data from A. The Data Source you enter in the forms property. and B. each field has a place within that datasource for it's data.

If you tell the form to look at a different datasource and that datasource had the same field names with the same format then the form won't know any different.

When the form opens it will get to the field where you entered a criteria and it won't recognize [Enter Start Date] so it will display this message because it wants you to tell it what to use as the fields value.

When you learn how you can use other fields from even other databases as the field value and the form will look for this and if nothing then it will call out to you for some input. For now, just think about the simpler side of what is happening.

 

[SoxPats83]

You could have a look at the sample here, it is a search as you type search tool.

that DB is great! not what i was asking for, but BETTER! thank you. questions about it though, how were you able to get the headers too appear across the top of the list box, also, how do you set it so if you double click on a highlighted objected, it opens that object's corresponding form?

 

[John Big Booty]

To show the headers in the list box, you will need to open you from in design view and then set the List Box's Column Heads property to Yes.

As to how the form is opened on Double Click check out the List Box's On Dbl Click event. The code is pretty straight forward and looks something like;

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FRM_FormToOpen"
    
    stLinkCriteria = "[LinkingID]=" & Me![ControlOnCurrentFormWithLingkingID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[SoxPats83]

To show the headers in the list box, you will need to open you from in design view and then set the List Box's Column Heads property to Yes.

As to how the form is opened on Double Click check out the List Box's On Dbl Click event. The code is pretty straight forward and looks something like;

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FRM_FormToOpen"
 
    stLinkCriteria = "[LinkingID]=" & Me![ControlOnCurrentFormWithLingkingID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

very good stuff. thanks again. this is an awesome "bells and whistle" for any Database. if you have anything else that serves functionality purposes and is easy to use as this. please let me know.

 

[SoxPats83]

for example, say you double click on one of those options and the forms opens up imbedded somewhere along the bottom of this form? possible?

 

[John Big Booty]

for example, say you double click on one of those options and the forms opens up imbedded somewhere along the bottom of this form? possible?

That could be achieved with a subform linked to the main form via the selection in the list box. There would be no need for a double click unless you wanted to make the subform visible or non visible on the double click event.

 

[SoxPats83]

That could be achieved with a subform linked to the main form via the selection in the list box. There would be no need for a double click unless you wanted to make the subform visible or non visible on the double click event.

That would be amazing! Say you double click on one of the options and the once invisible new form appears at the bottom of the new form. Would it be at all possible for you to incorporate that into your existing DB and attch the end product?

 

[John Big Booty]

Ok I've made some mod's to the DB. As the search form is unbound, it complicated things slightly. I couldn't make the parent child link as I suggested earlier, so what I had to do is set the subform's filter based on the current selection in the List box.

As I'm already using the On Dbl Click event to open a pop up form, I have added a button that shows or hides the sub form dependant on it's current state.

Check the event behind that button and also the additional On Click event that I have added to the list box to refresh the filter on the sub form as different records are selected.

 

[SoxPats83]

Ok I've made some mod's to the DB. As the search form is unbound, it complicated things slightly. I couldn't make the parent child link as I suggested earlier, so what I had to do is set the subform's filter based on the current selection in the List box.

As I'm already using the On Dbl Click event to open a pop up form, I have added a button that shows or hides the sub form dependant on it's current state.

Check the event behind that button and also the additional On Click event that I have added to the list box to refresh the filter on the sub form as different records are selected.

sorry, i am currently at a computer without access on it, so unfortunately am unable to open your attachment. would it be too much to ask if you could post some screenshots? it's alright if you can't. also, another pretty cool idea i thought of would be if you had multiple forms applied to each record that you find in your list box (for example, you have a list of winery's. each winery has multiple forms that can tell you more about it like information on the bottle of wine, information on the year, information in regards to the winery's location, etc), you could perform a single click on the record and this triggers a pop up menu. listing all the multiple forms applied to that specific record. in case i did not explain it clear enough, at the attached link is a sample DB that shows all about the pop up menu http://www.access-programmers.co.uk/forums/showthread.php?t=98229. whatever the selection may be from this pop up menu, the selection still appears (out of no where) on the same form as the list box. i understand if this request is not do-able, as reading back, it looks like a lot to ask. thanks!

 

[John Big Booty]

There is absolutely no reason you couldn't combine the functionality of the two DB's. Pull them both apart and find out what makes them tick. Then start from scratch and implement the principals shown in each of those DB's in your new DB, with your own data, by doing this you will learn a lot about the code that makes them both tick. Post back with any problems you encounter.

Have fun

 

[SoxPats83]

There is absolutely no reason you couldn't combine the functionality of the two DB's. Pull them both apart and find out what makes them tick. Then start from scratch and implement the principals shown in each of those DB's in your new DB, with your own data, by doing this you will learn a lot about the code that makes them both tick. Post back with any problems you encounter.

Have fun

i will have to do that when i get back to a computer with access on it. thanks again for your help thus far!

 

[SoxPats83]

my only concern is how to place a form into another form as a subform which is invisible. basic subform within form is easy stuff. just making the subfiorm invisible and then appearing based on another action is where i would get lost.

 

[John Big Booty]

Simply use;

Me!Subform1.Form.Visible = True

or

Me!Subform1.Form.Visible = False