Nested Search???

[Jen0dorf]

Hi

back froms holidays and back on the database.

I now have the tables, relationships set up and (with help) have a query to display all records and I have created a basic form based on the query to search for a record.

But - ok there's always a but!

It's not searching as I want and I'm not sure what to call the search method I want to get help via google etc.

Simply the database contains a list of graves in a cemetery which includes names and years and various other data.

I want a search where if I search for Smith the all the Smiths are displayed in table format ie one per line just showing name, year of death, first name, year of birth

I then want the facility for the user to click on a record/line and have the full details revealed in a popup form or possible in another tab.

Can someone point me in the right direction



thanks

Ian

[orange]

Ian,

Take a look at the Modified Universal Search database I supplied to the Code Repository.
You could make a copy of that database; remove all of the tables; link your tables that you want to search;
use the form and follow the directions.

https://www.accessforums.net/code-re...rch-43055.html

You could just look at the databases event procedure codes and build your own.

[Jen0dorf]

Thanks for that I'll take a look

Ian

[ssanfu]

I've also set up something similar.

I have a search form that returns records that match the criteria. Then I use the double click event that uses Docmd.OpenForm with the Where clause that has the PK field for the selected record. The form (I named it EmpDetails) is bound to a query. The open form Where criteria returns one record.

[Jen0dorf]

HI

thanks for that. I'm on the learning curve so it means nothing but hopefully after some research , trials I'll understand

cheers

Ian

[Homegrownandy]

*copy your original query with all records
* add a criteria for what you want to filter.

So you seem to be filtering by name....

Add this code to the criteria field for name (if that's what its called) in the design view for your query.
Like "*" & [Forms]![YourFormName]![YourTXTBoxName] & "*" Or Is Null


the * will perform a wildcard search based on what you enter... so SM will return SMITH and SMITHY for example.

Have your form based on this query. Then in the after update event of that text box add the code

Me.requery

obviously in between the code that's already there.

Hope this helps.. If not just ask.

Andy

just posting because there's many things I found confusing and had to ask multiple times.

[Jen0dorf]

Hi

thanks for the post really helped.

I finaly ended up using this code to display my list of deceased people and it works well.

Like "*" & [Enter a name]

I do have a couple of questions

when you open up the form a box appears which says at the top "Enter Peramter Value" is it possible to edit/delete this text?

Secondly the code

Me.requery

where exactly does this go? I right clicked on the text box in the form and then went to the properties and under the tab event added it to the line after update. but is does not seem to do anything, your post also implies that there should be other code there, so clearly I'm missing something

thanks

Ian

[Homegrownandy]

Which text box are you putting that code in?

also:

Like "*" & [Enter a name]

I would say that needs to reference a specific text box on the form.

When the form opens it uses that query to generate the results. At that time the box is blank so it uses * and generates all.

Now in the text box you want to use (and need to reference in the query) that's where you need the code:

Private Sub Search_TXT_AfterUpdate()
Me.Requery
End Sub

where mine says Search_TXT that's where you have your text box name. (it should be there automatically.)

[Jen0dorf]

Hi

I feel I may have gone a bit wrong

My form is based on a query.

When creating that query I have a field called Surname in that field I added the criteria

Like "*" & [Enter a Surname]

so that when someone puts in either S or Smith the appropriate records come up and it seems to work.

This seems to work ok

If I open the form in design mode and click in the middle of the Surname Field is says Surname if I delete that and paste the code below

Private Sub Surname_AfterUpdate()
Me.Requery
End Sub

when I run the query I see

[CODE#Name?][/CODE]#Name?

which I think means invalid path or name?

Am I missing the point?

thanks again

Ian

[Homegrownandy]

ok a few things, field is at a table level not form. you need to make sure you call your text boxes a text box on the forum to avoid confusion.

Is the text box called surname? on the form?

[Like "*" & [Forms]![YourFormName]![YourTXTBoxName] & "*" Or Is Null

< try this

YourFormName = the form where the text box is you want to reference
YourTXTBoxName = the text box name

leave the []! symbols and just make the referencing right.

tell me the name of your form and the text box and ill add it for you if you don't understand.

Or upload a sample database.

[Jen0dorf]

HI

really appreciate the advice and I take on board the naming of fields in tables and text boxes in forms. Apologies for confusion.

I've added the code you kindly supplied to the criteria field of the Surname field in the query and it seems to be working perfectly.

However I am still at a loss as to how to use

Private Sub Search_TXT_AfterUpdate()
Me.Requery
End Sub

Do I go into design mode for my form and then just type the code into the Surname text box?

The table is called "tblGraves Query with search" the form is also called "tblGraves Query with search" and on the form the text box is called Surname

thanks again

Ian

[Homegrownandy]

They are bad names for both form and query, but that wont stop them working haha.

Your code that you put in the criteria references a specific text box on a specific form now (assuming you did that right). What that means is... your query looks at that box to see what it should look for.

your query doesn't automatically keep checking that box. You need to tell it when to do so.

So.... when that box is updated is a good time to requery

that same box in design view... click the after update event, then tell it to requery.

Hope this helps.

[Jen0dorf]

Hi thanks for the info.

At the risk of seeming even more stupid why are the names bad?

thanks

Ian

[Homegrownandy]

Some code doesn't like the spaces so use _ instead of space. I'm no expert on these and I'm fairly new to access myself. Anyway, here's my opinion haha.

TblGraves is your table for all the graves. There's no need to name that table anything else.

QryGravesSearch is descriptive, and it states its a query.

then your form: name FrmGraveSearch.

just keep them accurate, short and descriptive. But often renaming can break a lot of things, like when you reference a form and change the name. You need to change that name everywhere.

[ssanfu]

Have you seen this?
The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm

Note #3.

Should use only letters, numbers and/or the underscore. NO spaces, punctuation or special characters in object names. (Tables/fields, Forms, Queries, Reports, Modules, variables, ....., etc)