By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

Problem - Searching Records In The Form

P: 6
Using MS ACCESS 2002

I developed a FORM, and within that FORM, I created and added a SEARCH BUTTON, and then I created a SUB-FORM, so that when an the END USER clicks on the FORM it will POP-UP the SUB-FORM, and in that SUB-FORM I added all the FIELDS of the information I wanted to view. The SUB-FORM was created to do the actual SEARCHING.

However, when I want to search a RECORD and I type in DATA of a specific RECORD into the FIELDS in the FORM I created, it does not let me! I would like to have MS ACCESS pull the RECORD when I type it into the FIELD(s). The way I am retrieving the information when I am searching a RECORD is by scrolling through all the RECORDS in the DATABASE, or if I click on the "FIND" button option in the standard MS ACCESS toolbar.

I want to use the FIELD option that I created in the FORM and type in the information so that MS ACCESS can pull that specific record, can someone help me with this? How am I able to:
1.) Enter Data into the Fields I created
2.) Have MS ACCESS pull the Record by typing information on the FIELDS I created in the FORM.

Thanks so much in advance for your help!
Jan 5 '07 #1
Share this Question
Share on Google+
11 Replies


nico5038
Expert 2.5K+
P: 3,072
There are many ways to search in Access.
Personally I never create searchforms, but instruct my users to use the right-click pop-up menu. This will give a very versatile search facility (inclucing partial matches) and my users really feel in control.

Idea ?

Nic;o)
Jan 6 '07 #2

pks00
Expert 100+
P: 280
What is your main form used for, anything?
What u could do is this

On a form, use the form header, add your search textboxes in there, then a btn

The form is based on a table or query that u want to search on

What u can then on the click event is to use the form's filter event

simple example
form is based on a table called tblMeow, it has the following fields ID, Cat, Name
u have a two textboxes txtCat and txtName in the form header, and a btn called cmdFind

Expand|Select|Wrap|Line Numbers
  1. private sub cmdFind_Click()
  2.  
  3.     Dim sWhere as String
  4.  
  5.     sWhere = ""
  6.     if isnull(Me.txtCat) = False then sWhere = "Cat = '" & Me.txtCat & "'"
  7.     if isnull(Me.txtName) = False then
  8.         if sWhere <> "" then sWhere = sWhere & " AND "
  9.         sWhere = sWhere "Name = '" & Me.txtName & "'"
  10.     end if
  11.  
  12. 'Note if u filter on numeric values then u do not wrap with single quotes
  13.  
  14.     If sWhere <> "" then
  15.         Me.Filter = sWhere
  16.         Me.FilterOn = True
  17.     else
  18.          Me.FilterOn = False
  19.     End if
  20. end sub
Jan 7 '07 #3

P: 6
Hello People, and thanks for your previous help! Now, I would like to be able to search in more than one field on the form that I created. The current problem is I am only able to pull out records by using only ONE search field. For example, let me explain what I would like:

I would like Access to pull records by either typing into the fields FirstName, or LastName, OR Location, OR, STATUS, can I do that in ACCESS? I have about 8 FIELDS in my FORM and I would like to use any FIELD to pull out RECORDS, is that possible? THANKS SO MUCH!
Jan 11 '07 #4

nico5038
Expert 2.5K+
P: 3,072
Use the OR tab of the QBF form. It's located bottomleft.

Nic;o)
Jan 12 '07 #5

P: 6
Can you please be more specific? I am very new to creating databases, as a matter of fact this is my first real MS ACCESS database that I am creating. So some of the terminology might be clueless to me.

So again, I have a search function that I created on the FORM but it only searches one criteria. I have many fields that I would like to search records by, I have never created a MACRO, or any sort of Script, so is it possible you show me in detailed?

Thanks so much in advance!
Jan 12 '07 #6

nico5038
Expert 2.5K+
P: 3,072
At the top of the form you'll find a button "Query by Form".
When you press that you can enter criteria for filtering the records.
This includes a search with wild characters like:
*x*
to find all strings with an "x" or:
>1000
to find all numbers larger as 100.

A bit clearer ?

Nic;o)
Jan 12 '07 #7

P: 6
HELP PLEASE - THIS IS WHAT I AM GETTING:

Option Compare Database

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click

End Sub

Private Sub Command26_Enter()

End Sub

BUT THIS IS WHAT I WOULD LIKE:
private sub cmdFind_Click()

Dim sWhere as String

sWhere = ""
if isnull(Me.txtCat) = False then sWhere = "Cat = '" & Me.txtCat & "'"
if isnull(Me.txtName) = False then
if sWhere <> "" then sWhere = sWhere & " AND "
sWhere = sWhere "Name = '" & Me.txtName & "'"
end if

'Note if u filter on numeric values then u do not wrap with single quotes

If sWhere <> "" then
Me.Filter = sWhere
Me.FilterOn = True
else
Me.FilterOn = False
End if
end sub

The fields on the form are as follows:

OM# Business
File Name Dept
State Note
Purpose Initials
Reviced Date
File Type

Can anybody help me with this? I need to be able to search records using any of the above fields.

Thanks so much!
Jan 12 '07 #8

MSeda
Expert 100+
P: 159
I, Don't mean to complicate the discussion by throwing another idea into the mix but I can't help my self.
I like to set up search forms with a combobox that allows a user to select a the field to search and whether to search anywhere, starts with or exact matches (not unlike access).
the form is pretty simple a textbox to enter the search term, a combobox to choose the fields to search, a combobox to choose how to search, a subform(continuous) to display results and a command button that executes the following code.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command37_Click() 'New Search
  2. Dim FieldRS As ADODB.Recordset
  3. Dim MatchTerm As String
  4. Dim fieldsql As String
  5.  
  6. 'make previous results invisible
  7. Me.Label74.Visible = False
  8. Me.ResultCC.Visible = False
  9.  
  10. 'put correct syntax around the search term
  11. Select Case Me.SMatch
  12. Case "Anywhere"
  13.     MatchTerm = " Like '*" & Me.STerm & "*'"
  14. Case "Exact Match"
  15.     MatchTerm = " Like '" & Me.STerm & "'"
  16. Case "Begins With"
  17.     MatchTerm = " Like '" & Me.STerm & "*'"
  18. End Select
  19.  
  20. 'make recordset of names of fields to search
  21. If Me.SField = "Search All Fields" Then
  22. Set FieldRS = New ADODB.Recordset
  23. FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields] Where [Table Name] = 'Client Table';", CurrentProject.Connection
  24. Else
  25. Set FieldRS = New ADODB.Recordset
  26. 'I actually have a table that lists all of my fields and then assigns a 'Search Name' , a plain english name if you will, it also groups similar fields like phone numbers since there are several fields in my client table where phone numbers are stored the user need only select 'Phone' once to search all of them
  27. FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields] Where [Search Name] = '" & Me.SField & "' AND [Table Name] = 'Client Table';", CurrentProject.Connection
  28. End If
  29.  
  30. 'build sql string searching selected fields
  31. Do Until FieldRS.EOF
  32.     fieldsql = fieldsql & "[" & FieldRS![Field Name] & "]" & MatchTerm
  33.     FieldRS.MoveNext
  34.     If Not FieldRS.EOF Then
  35.     fieldsql = fieldsql & " OR "
  36.     End If
  37. Loop
  38.  
  39. 'create a temporary table of search results and set subforms recordsource
  40. Me.ResultCC.Form.RecordSource = ""
  41. DoCmd.RunSQL "SELECT [Client Table].* INTO [TempRecordSet] FROM [Client Table] WHERE " & fieldsql & ";"
  42. Me.ResultCC.Form.RecordSource = "SELECT TempRecordSet.* From TempRecordSet"
  43.  
  44. 'make 'No Results' label visible
  45. If IsNull(ELookup("[Client Code]", "[TempRecordSet]")) Then
  46. Me.Label74.Visible = True
  47. Else
  48. Me.ResultCC.Visible = True
  49. End If
  50.  
  51. End Sub
if you'd like any further explinations just post back.
Jan 13 '07 #9

nico5038
Expert 2.5K+
P: 3,072
If you want to rebuild the right-click and/or QBF, be my guest :-)

To partially overcome the trouble with filled or empty comboboxes use for the combo:

select "*" as ID, "<all>" as description from Yourtabel
UNION
select ID, description from Yourtabel

Now use:

if len(nz(me.combobox)) > 0 then
strWhere = strWhere & " and ID like '*" & me.combo & "*'"
endif

Thus you get a match or all rows.

Nic;o)
Jan 13 '07 #10

P: 6
Thanks Nico! MSeda stated exaceley what i am trying to do! However, because I am totally new at developing a database, I need it to be explain in the simpliest way as possible....I feel very dumb saying it, and I really thank you and apologise if I do not understand some of you....or having a hard time trying to understand ----but I greatly appreciate all of your help, you all been awesome!

So here is my full understanding of it and what I dont understand:

Ok first thing is first:
1.) Create a textbox to enter the search term,
2.) Create a combobox to choose the fields to search
3.) Create a combobox to choose how to search
4.) Create a subform (continuous) to display results (just a little confused on this)
5.) Create a command button that executes the following code

The above I understand, ya hoo! However, I do not understand the code below:

Private Sub Command37_Click() 'New Search
Dim FieldRS As ADODB.Recordset <-------IS THIS THE TABLE NAME?
Dim MatchTerm As String
Dim fieldsql As String

'make previous results invisible
Me.Label74.Visible = False <-------WHAT IS THIS, IS THIS THE FIELD NAME?
Me.ResultCC.Visible = False <-------FIELD NAME?

'put correct syntax around the search term
Select Case Me.SMatch
Case "Anywhere" <--------FIELD NAME?
MatchTerm = " Like '*" & Me.STerm & "*'" <--------WHAT IS Me.STerm?
Case "Exact Match"
MatchTerm = " Like '" & Me.STerm & "'"
Case "Begins With"
MatchTerm = " Like '" & Me.STerm & "*'"
End Select

'make recordset of names of fields to search
If Me.SField = "Search All Fields" Then <---------FIELD NAME?
Set FieldRS = New ADODB.Recordset <---------TABLE NAME?
FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields]
ON THE ABOVE CODE, DO I TYPE IN EACH FIELD I WANT ACCESS TO SEARCH?

Where [Table Name] = 'Client Table';", CurrentProject.Connection
Else

End If

'build sql string searching selected fields
Do Until FieldRS.EOF
fieldsql = fieldsql & "[" & FieldRS![Field Name] & "]" & MatchTerm
FieldRS.MoveNext
If Not FieldRS.EOF Then
fieldsql = fieldsql & " OR "
End If
Loop

'create a temporary table of search results and set subforms recordsource
Me.ResultCC.Form.RecordSource = ""
DoCmd.RunSQL "SELECT [Client Table].* INTO [TempRecordSet] FROM [Client Table] WHERE " & fieldsql & ";"
Me.ResultCC.Form.RecordSource = "SELECT TempRecordSet.* From TempRecordSet"

'make 'No Results' label visible
If IsNull(ELookup("[Client Code]", "[TempRecordSet]")) Then
Me.Label74.Visible = True
Else
Me.ResultCC.Visible = True
End If

End Sub
Jan 16 '07 #11

MSeda
Expert 100+
P: 159
As Nico stated this does basically recreate access search method but it allows a greater extent of programmer control and a user friendly interface. (When you control + F in access the find form always has the replace form tabbed right behind it. This scares me, alot. So I always disable all access features in mde’s if users want to do anything they have to use my forms.)
Also I dump the results into a local table. This reduces network traffic if the user decides to search the results and also I have a command button allowing the user to export to excel or print a report (or anything else you may want to allow your users to do) the local table just makes it so you only have to query once.

My form has the following controls.
ResultCC – Continuous Subform to display results
Label74 – ‘No Results’ label
Command37 – the button that executes the code
STerm - a text box where user enters text they want to search for
Smatch – a combo box where user selects Search type from options “Anywhere”, “Exact Match” and “Starts With”. These items are just a value list in the control source.
Sfield – where user selects fields to search(You could use a field list in this box or you could also use a table like I do.)

I have a table called “Data Search Fields” with the fields “FieldName”, “TableName”, and “SearchName”. FieldName lists allof the fields in tables I want to Search, TableName lists the corresponding table that contains the field, and SearchName is the “plain english” name that I want users to see. SearchName also groups fields, for instance my client table has three phone fields, Phone1, Phone2, and Phone3, they all have a SarchName “Phone” so when Phone is selected by the user all phone fields are searched.
If I do not want a field to be searchable by a user, like an auto primary key, I leave the Search Name null and it is excluded from my combobox rowsource query.

My Sfields rowsource query looks like this (note - I have hard coded ‘Client Table’ but you could use an additional combo box on your form if you want users to select a table.)
I use a union query to add “Search all Fields” The sort field makes “Search All Fields” always the first in the list (it has a sort value of 1 all of the table fields are assigned a 2)

SELECT DISTINCT [Data Search Fields].[Search Name], 2 AS Sort FROM [Data Search Fields]
WHERE [Data Search Fields].[Search Name] Is Not Null AND[Data Search Fields].[Table Name] = 'Client Table'
UNION Select "Search All Fields" as [Search Name], 1 as Sort From [Data Search Fields] ORDER BY Sort;


The code does these things

1. Puts correct comparison syntax around the search terms based on user selected search type and sets value of variable MatchTerm with this string.
2. creates a list of fields to be searched. (FieldRS)
3. creates SQL where statement for each field listed in FieldRS comparing it to MatchTerm.
4. Creates local table with results and sets subforms recordsource to local table


Here’s the Code again. I’ve tried to address each of you questions, if I missed anything or created additional confusion with all the wordiness please post back:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command37_Click()
  2. Dim FieldRS As ADODB.Recordset  <------------ FieldRS is a recordset created below that lists the fields to search.
  3. Dim MatchTerm As String
  4. Dim fieldsql As String
  5.  
  6. Me.Label74.Visible = False <--------- a label that says “No Results”
  7. Me.ResultCC.Visible = False <--------- my subform that displays results
  8.     *At the beginning of the search I make both these invisible (I have a rectangle the same size as my subform so it looks like the empty subform is there because the empty subform has an odd looking null record, I think the empty rectangle looks better.)
  9.  
  10. 'put correct syntax around the search term that was selected from combobox “Smatch”
  11. Select Case Me.Smatch  <-------------- SMatch combobox on my form where user selects one of the the options listed below
  12. <--------- STerm is the user input Search Term this Case select just ‘wraps’ the appropriate syntax around the search term (i.e. if the user enters search term “Main” and selects the “Begins With” option this give the MatchTerm String variable a value of “Like ’Main*’”) MatchTerm is then used in the where statement to generate the result recordset
  13. Case "Anywhere"
  14.     MatchTerm = " Like '*" & Me.STerm & "*'" 
  15. Case "Exact Match"
  16.     MatchTerm = " Like '" & Me.STerm & "'"
  17. Case "Begins With"
  18.     MatchTerm = " Like '" & Me.STerm & "*'"
  19. End Select
  20.  
  21. ‘this block creates the FieldRS recordset
  22.  
  23. If Me.SField = "Search All Fields" Then ‘Create a list of  all Fields given a search name
  24. Set FieldRS = New ADODB.Recordset
  25. FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields] Where [Search Name] Is Not Null AND [Table Name] = 'Client Table';", CurrentProject.Connection ‘(in my previous post I forgot the “[Search Name] Is Not Null AND” that omits fields I don’t want to be searched)
  26. Else
  27. Set FieldRS = New ADODB.Recordset ‘Create a list of selected Fields
  28. FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields] Where [Search Name] = '" & Me.SField & "' AND [Table Name] = 'Client Table';", CurrentProject.Connection
  29. End If
  30.  
  31. ‘This block takes the FieldRS recordset (list of fields) and creates the WHERE part of the sql string used to create the result recordset. 
  32.  
  33. Do Until FieldRS.EOF
  34.     fieldsql = fieldsql & "[" & FieldRS![Field Name] & "]" & MatchTerm
  35.     FieldRS.MoveNext
  36.     If Not FieldRS.EOF Then ‘ if this is not the last item in field list add the word ‘OR’
  37.     fieldsql = fieldsql & " OR "
  38.     End If
  39. Loop
  40.     ‘Each field name is compared to the variable MatchTerm created in the select statement above. So if Phone was the selected field and “Begins With” was selected for search type and ‘803’ was entered for the search Term then this would return a string like “[Phone1] Like ‘803*’ or [Phone2] Like ‘803*’ or [Phone3] Like ‘803*’”
  41.  
  42. Me.ResultCC.Form.RecordSource = "" ‘this just clears the subform record source
  43. ‘This sql string uses the above created where statement to create a local temporary table with the search results.(as I mentioned before I have hard coded ‘Client table’ but you could easily pull a table name from a combo box). I dump the results into a local table so the user can search results (separate command button performs same search on the temporary table) or export the results into an excel spreadsheet without creating additional network traffic. 
  44.  
  45. DoCmd.RunSQL "SELECT [Client Table].* INTO [TempRecordSet] FROM [Client Table] WHERE " & fieldsql & ";"
  46.  
  47. ‘Sets subforms record source to the temporary table
  48. Me.ResultCC.Form.RecordSource = "SELECT TempRecordSet.* From TempRecordSet"
  49.  
  50. ‘if there is nothing in the table then make the ‘’No Results’ label visible else make subform ‘ResultCC’ visible.
  51. If IsNull(ELookup("[Client Code]", "[TempRecordSet]")) Then
  52. Me.Label74.Visible = True
  53. Else
  54. Me.ResultCC.Visible = True
  55. End If
  56. End Sub
like I said I hope this clarifies things if not post back,
Megan
Jan 17 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.