Solved

Create a search form in Access

Posted on 2010-11-25
10
1,428 Views
Last Modified: 2012-08-13
I have a table which contains approx 10 fields and has a form to input data into the table. There are some fields that I would like to be searchable from a separate form, to bring up all records that fit the criteria (for a mail merge).

The fields that will be searchable are Month, Year, Surname, Registration number, Make and Model so for example, I would like to retrieve all records for a single criteria (e.g. a Make) or a combined criteria (e.g. Month and Year).

I know that I can use a combo box in the input form to bring up individual records, but I need something that will bring up all relevant records and unfortunately I'm not exactly an expert in Access coding!

Help would be very much appreciated as research on the Net assumes knowledge of coding.
0
Comment
Question by:CSHTech
10 Comments
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 250 total points
ID: 34211507
CSHTech,

My approach on this situation is to create a query and add a field concatenating the fields you want to be included in your search. E.g. Search:[Month] & ", " & [Year] & ", " & [Surname] & ", " & [Registration number] & ", " & [Make] & ", " & [Model]

And in the criteria field of Search column put Like "*" & Forms!YourSearchFormName!txtSearch

txtSearch is an unbound text in your Search Form.

Sincerely,
Ed
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34211521
I forgot to inculde:

Beside your txtSearch textbox put a command button with Search caption on it and put this code after the click event.

Me.Requery
and for a subform
Me.subFormName.Requery

Sincerely,
Ed
0
 

Author Comment

by:CSHTech
ID: 34211659
Thanks for the quick reply.

Excuse my lack of knowledge, but how do I present this query on the search form?

The person I am doing this for is not very computer literate and I would like to present them with a search form that contains the fields that they want to search for.......
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34211736
See attach file.

Open SearchForm to display all records. Now, try to type a text from FirstName in the text box then click Search button. Do try record from LastName and for address.

Hope the sample db helps.

Sincerely,
Ed
SampleDBSearchForm.mdb
0
 

Author Comment

by:CSHTech
ID: 34211792
Thank you - that's very helpful.

How would someone enter multiple criteria, e.g. Month & Year (bearing in mind the person using this will not be database literate)?

Thanks.
0
 
LVL 6

Assisted Solution

by:AHMKC1
AHMKC1 earned 250 total points
ID: 34211877
Try following

place combox or textbox as required and a command button  and a listbox control

here i made it  by four combobox (you can use more or less as you required)
the combobox name is as filedname on which searching (it is not necessry)
write code under command button

Private Sub CmdSearch_Click()
Dim S As String
S = ""
If Not IsNull(Month) Or Month <> "" Or Month <> "ALL" Then
S = S & " and Month=" & Month.Value & ""
End If

If Not IsNull(Year) Or Year <> "" Or Year <> "ALL" Then
S = S & " and Year=" & Year.Value & ""
End If

If Not IsNull(SurName) Or SurName <> "" Or SurName <> "ALL" Then
S = S & " and SurName='" & Me.SurName.Value & "'"
End If

If Not IsNull(RegitrationNo) Or RegitrationNo <> "" Or RegitrationNo <> "ALL" Then
S = S & " and RegitrationNo='" & Me.RegitrationNo.Value & "'"
End If

S = Mid(S, 5)

S = "Select TableName.* from TableName Where" & S  

List1.ColumnCount = 10
List1.RowSource = S
List1.Requery
End Sub

'** TableName is the table name on which you perform search operation
Have you any confusion let me know

Thanks
0
 

Author Comment

by:CSHTech
ID: 34212318
Thanks.

A quick question to MINDSUPERB - I understand Table1, Query1 and Searchform, but what purpose does Form1 have?
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34213886
You can't place a search box control directly into Form1 which is defaulted to a datasheet view when open. To address that limitation a main form is created for you to place the search box and make Form1 as the subform.

Ed
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34216564
The only thing I'd mention would be about the choice of using a query.
The option presented by AHMKC1 (building up SQL in code) has certain advantages - and subtle potential disadvantages too.
The query as shown uses criteria as
Like "*" & [Forms]![SearchForm]![txtSearch] & "*"

That's fine if you really want it.
But if possible, try to offer either no wildcard or trailing only wildcards.
You're giving the engine a better chance to perform optimised requests that way.
The user could always enter the wilcard manually if they want to perform partial matches.

For a few similar examples perhaps check out the "Example Search Methods" demo in the page linked to in my profile.

Cheers.
0
 

Author Closing Comment

by:CSHTech
ID: 34301430
Many thanks for your help. Points split equally.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question