Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create a search form in Access

Posted on 2010-11-25
10
Medium Priority
?
1,467 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 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 1000 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

971 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