searching via user input

[raj chahal]

hi there

In access I've created a table, a query and a form that runs the query.
The query simply searches a table and a form is generated on the result of
that. i.e
SELECT partnumber, qty
FROM qty
WHERE qty < 5

But now I want to add user interactivity.
On a form I want a textfield, where a user can enter a number, and then a
button that will read that textfield and commence a search and display the
results.
I've been told I can't use macros for this, but need to use forms and
command buttons. Is this true and how would I do it ?

thanks in advance
raj

 

[Jessica]

Is the text you are searching for always in the same field, or are your fields changing?

If not changing field then use the code below. I also have some code if you are having the user select the field to search in.

I would suggest creating a report that will display the finished product, when the report is opened with out the form, it will display all fo the records, however when the form is used it will create a filter for the report. In your form create a button and give it a name, I named mine Run Report. Then in the properties for OnClick choose Event Procedure (you will be building code).

Your code would be something like this:

Private Sub Run_Report_Click()
On Error GoTo Err_Run_Report

Dim Text As String
Dim Restrict As String

Text=[Forms]![Form Name]![TextBox Name]
Restrict = "[Table Name]![Field Name]=[Forms]![Form Name]![TextBox Name]"

if Text <> Null then
DoCmd.OpenReport "Report Name", acViewPreview, , Restrict
End If

Exit_Run_Report:
Exit Sub

Err_Run_Report:
MsgBox Err.Description
Resume Exit_Run_Report

End Sub