469,312 Members | 2,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

ms access best way to create a search form

13
hi iam quite new to access and i am now at a stage where i have created my database tables and various forms to input data on them but i need a way of displaying data based on some search criteria

for this i have created a form with various unbound fields but i do now how to proceed.

i also have create a report based on the table i want to display results from that is now just displaying all records

how should i proceed?
can the search find similar data except for identical data in the fields
Sep 16 '10 #1
11 11569
Mr Key
132 100+
Hi Madsma, Welcome to the BYTES Forum, this forum is for developers and ordinary users, Be free to ask in details, Speak exclusively what exactly you want to do. You may not receive enough response simple because your question is not clear. I suppose you ask your question again, this time include tables, search criteria, and all hints that should you think mandatory to solve your problem. For this question type I can tell you go to the web pages below that may help you.
If you give further details, I can assure you will receive amazing answers that you might not even thinking of.
http://bytes.com/topic/access/answer...-form-database
Sep 17 '10 #2
MadSma
13
hi Mr Key thank you for replying and sorry for that post i wasnt sure i could asked using so many details.
ok here i go again

i have a table containing my data called dataSource
it contains the field ID(primary key),name,age,date,team
i also have a form called dataEntry using the form wizard that enable me to enter data into my table(datasource)

i also created a report called report1 using the report wizard that displays all fields of each record line by line

What i want to do is to create some sort of search form that when the users enters some values of his choosing the report report1 will open containing only the hits.
About the Hits: I need to be able to enter data in any or all the fields in the search form and get matching results in the report but i need to search for similar matches beside exact matches. So if i only enter Andrew in the name field and hit the search button the report1 should open displaying all the records that contain in the name the text Andrew
like: Andrew Johnson
Andrew.O.Grimes
or even Sir Andrew Loyd

For the search form i have created a form with unbounded fields called searchForm that contains the fields:name,age,date and team(all of which are data existing in my table)
and a button called search but i do not know how to proceed

ps:I need for some more functionality but I want to deal with each task at a time
Sep 19 '10 #3
Mr Key
132 100+
Well Madsma, Try to use this findrecord form, import it to your database, you will be able to search from all queries and Tables in your database and create a new query from which you may create a report.
To search for the records that you are not sure, you may write in the criteria as follows
1. *xxx*? if you meant for the records that might contain ...xxx...
2. *xxx if it contain ...xxx
3. xxx* if it contains xxx...
4. xxx if you mean the whole word
5. *? for all records in that particular table/query
Open the Attached Search form to see if it can help you.
For further information about this form, open the link below!
Open this link to view the source code of this SEARCH FORM
Attached Files
File Type: zip findrecord2k.zip (69.0 KB, 998 views)
Sep 19 '10 #4
MadSma
13
thanks alot this is very helpful as to create the search but i think i still have implementation problems

Here is what i did
i exported the form to my db and i then used it to create an sql statement
Select * from [Table1] Where [age] Like "*sage*?" OR [name] Like "*sname*?" OR [team] Like "*steam*?"

where sage,sname and steam are the filed on my searchform

and i used it to create a query but pasting Select * from [Table1] Where [age] Like "*sage*?" OR [name] Like "*sname*?" OR [team] Like "*steam*?" in the sql view of the query build but is not working
how can I create this query to take the values of my searchForm??
If I do that the I think the next step is to create a report based on that report and the use that to display my results when the serach button is presses… am I right?
Sep 19 '10 #5
MadSma
13
i just saw that i used Table1 instead of [datasource] but it is not working even when i use [datasource]
Sep 19 '10 #6
Hi, ok an easy to follow example.
I have a table called client. Fields are as follows
fname : first name
sname : surname
age: age
country : country

I then make a form (frmclient)using wizard or not for table client.
MAKE SURE that the text boxes are unbound (use the properties box).
Now using properties box make the default value of each textbox * (this gives us a wildcard in our search).

Ok, use the wizard to create a query of client and select option to modify query in wizard.

in the criteria box for fname enter "Like([forms]![table1].[controls]![fname].[value])" WITHOUT the quotes.
do this for each field replacing fname with the field name sname, age and country.
Save the query as qryclient.
create a form with wizard based on qryclient and save as frmresult.

Now load frmcountry in design view, add a command button and set to open form, select frmresult as the form to open.
Now run the form and enter any combination of data in any textbox but, be sure to leave the * in the fields that are for wildcard.
Sep 20 '10 #7
MadSma
13
just few clarification
when creating the query and entering Like([forms]![table1].[controls]![fname].[value])in the criteria
table1 is the name of my table and in the examble cases should it be clien?
also what should i write in the place of [value]

and a thrid question what is frmcountry did you mean the frmclient?
Dec 2 '10 #8
Hi, sorry got a bit messed up with rable name between yours and mine. The word value including square brackets ( [value]) is what you type in, it means to look at the value entered in control fname on form table1. I have attached a demo for you so you can see exactly what I mean.
Attached Files
File Type: zip search form.zip (66.2 KB, 670 views)
Dec 2 '10 #9
MadSma
13
thanks for all the help i feel im getting closer to the finish line
i have used malcomk way and while trying to use the query to search within a table based on a form input i cant get it to work

in the criteria of the query i write select.... from....

WHERE (((tblregistry.name) Like [Forms]![searchForm]![txttitle]+'*'));

this works for searches abcXXX but how would i include the XXXabcXXX form

i tried
WHERE (((tblregistry.name) Like [Forms]![searchForm]!'*'+[txttitle]+'*'));
but it is not working any thoughts???

also could anyone tell me what is best to use
Like ([forms]![searchForm].[controls]![txttitle].[value]+'*')
or
Like [Forms]![searchForm]![txttitle]+'*'));

ie the actual text field name or the [value] field
Dec 9 '10 #10
try WHERE (((tblregistry.name) Like '*'+[Forms]![searchForm]![txttitle]+'*'));
Place a wild card in front of as well as behind your search text.
Dec 9 '10 #11
MadSma
13
thank you malcolml that did the trick
Dec 9 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Guest | last post: by
f430
9 posts views Thread by f430 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.