Solved

Create a search form in Access

Posted on 2010-11-25
10
1,395 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now