Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1469
  • Last Modified:

Create a search form in Access

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
CSHTech
Asked:
CSHTech
2 Solutions
 
MINDSUPERBCommented:
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
 
MINDSUPERBCommented:
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
 
CSHTechAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MINDSUPERBCommented:
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
 
CSHTechAuthor Commented:
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
 
AHMKC1Commented:
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
 
CSHTechAuthor Commented:
Thanks.

A quick question to MINDSUPERB - I understand Table1, Query1 and Searchform, but what purpose does Form1 have?
0
 
MINDSUPERBCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
CSHTechAuthor Commented:
Many thanks for your help. Points split equally.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now