Solved

Create a search form in Access

Posted on 2010-11-25
10
1,408 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 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

16 Experts available now in Live!

Get 1:1 Help Now