Problem - Searching Records In The Form

[TheDataGuy]

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!

[nico5038]

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)

[pks00]

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

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

[TheDataGuy]

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!

[nico5038]

Use the OR tab of the QBF form. It's located bottomleft.

Nic;o)

[TheDataGuy]

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!

[nico5038]

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)

[TheDataGuy]

HELP PLEASE - THIS IS WHAT I AM GETTING:

Option Compare Database

Private Sub Command26_Click ()
On Error GoTo Err_Command26_C lick


Screen.Previous Control.SetFocu s
DoCmd.DoMenuIte m acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command26_ Click:
Exit Sub

Err_Command26_C lick:
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.txtCa t) = False then sWhere = "Cat = '" & Me.txtCat & "'"
if isnull(Me.txtNa me) = 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!

[MSeda]

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(continu ous) to display results and a command button that executes the following code.

Private Sub Command37_Click() 'New Search
Dim FieldRS As ADODB.Recordset
Dim MatchTerm As String
Dim fieldsql As String
 
'make previous results invisible
Me.Label74.Visible = False
Me.ResultCC.Visible = False
 
'put correct syntax around the search term
Select Case Me.SMatch
Case "Anywhere"
    MatchTerm = " Like '*" & 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
Set FieldRS = New ADODB.Recordset
FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields] Where [Table Name] = 'Client Table';", CurrentProject.Connection
Else
Set FieldRS = New ADODB.Recordset
'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
FieldRS.Open "SELECT [Data Search Fields].* FROM [Data Search Fields] Where [Search Name] = '" & Me.SField & "' AND [Table Name] = 'Client Table';", CurrentProject.Connection
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

if you'd like any further explinations just post back.

[nico5038]

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.combo box)) > 0 then
strWhere = strWhere & " and ID like '*" & me.combo & "*'"
endif

Thus you get a match or all rows.

Nic;o)