How to Use a Form to Search a Table and Display Results in a Report in Microsoft Access 2010

Question

I am fairly new to Access and I am having some trouble figuring out how to perform a search. I have a table that contains records with multiple fields and one primary key field called "Serial".

I have created a form that is linked to this table and all it contains is a text box and a button where the user can type in any word or serial number and anything and the table will be searched for records that have a field that matches the criteria entered.

I have gotten to the point where I can search through the table and find any record with a match but I cannot figure out how to post this record (and any other records that match) to a newly created report so that the user can see all of the results that matched his criteria.

The code I have is as such and the output gives only a blank report.

The msgbox line always outputs the correct Serial number for every search.

I believe that the issue is related to the DoCmd.OpenReport line.

Do While Not rs.EOF 'iterate through table and check all fields
For Each Field In rs.Fields
    If Field = SearchBar.Value Then
        found = True
        MsgBox (rs.Fields("Serial")) 'debugging
        **DoCmd.OpenReport "Asset Inv", acViewReport, , "[Serial] = '" & rs.Fields("Serial") & "'"**
        Exit For
    End If
Next Field

If found Then
    Exit Do            
Else
    rs.MoveNext
End If
Loop

Thanks for the help!

Serial is a string? Is there an error handler? Asked for a parameter (typo)? You should consider using a filter instead of looping through the rs. Loop through the fields of the query and combine them in filter. `strFilter = strFiler & "[" & rs.Fields.Name & "] = '" & SearchBar.Value & "' OR "' — BitAccesser

M.Nabavi M.Nabavi · Accepted Answer · 2016-06-29T11:37:32

If you Want To search in Multiple Fields Based On one Keyword Do the Following Steps :

1- Suppose we have the following table That Want to search using a keyword in multiple fields

2- Open The Query Design And Choose You Table That You Want Search It

3 - Add Fields To The Query By Dbl Clicking On Desired Fields

4-Now Click On a Blank Column And Go To Design Tab

5-Save You Query

5-Click On Builder

6-Select Your Saved Query From The List

7-Select Fields You Want To Search in And Append Them

concatinate Fields That You Want Search in

8-Now You Have The New Column That Contained All Desired Fields

9-Now In Criteria Section Type Like ""+[]+""

10-Run The Query

Write "Like" Sql Command And Run Query

Enjoy It ...

How to Use a Form to Search a Table and Display Results in a Report in Microsoft Access 2010

1 Answers