CODEWITHSUNDEEP
ms-access
sikas
sikas

Reputation: 5523

Search form in Access

I want to implement a search form to operate on a table on access.

But when I choose the table then create a From, I get all the table data in the form instead of search field. And when I change any value, the table values change. I want to have a text field where user can enter search criteria and hit a search btn and the search result appears as a table in the form or as a message box.

Upvotes: 1

Views: 683

Answers (3)

user2119980
user2119980

Reputation: 509

I have a search form created that I use that is very handy. In order to setup a form to search you need to create a search query. For example my form has the fields: Keywords and Source. So you need to link the query to the table that houses the data. The search query I have is 

SELECT KWTable.KW AS Expr1, KWTable.Code, KWTable.Source
FROM KWTable
WHERE (((KWTable.KW) Like "*" & [Forms]![Search_Form]![KW_Text] & "*") AND ((KWTable.Source)     Like "*" & [Forms]![Search_Form]![Source_Text] & "*"));

I type the words into the boxes and click a button to execute. The button code looks like

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Dim stDocName As String

stDocName = "Search_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

I hope this helps.

Upvotes: -1

iDevlop
iDevlop

Reputation: 25252

You can use something like this to put your form in Search mode when opened: 

Private Sub Form_Open(Cancel As Integer)
    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acFilterByForm, , acMenuVer70
     DoCmd.RunCommand acCmdFilterByForm
End Sub

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91316

You could add the data to the form after the search, but to keep things tidy, you might like to consider an unbound form with search box(es) and a subform for results. Let us say you have two boxes, txtName and txtDate and a search button, then a very rough idea would run:

strSQL = "SELECT aName, aDate FROM aTable WHERE 1=1 "

If Not  IsNull(txtName) Then
    strWHERE = " AND aName Like '*" & Replace(txtName,"'","''") & "*'"
End If

If Not  IsNull(txtDate) Then
    strWHERE = strWhere " AND aDate =#" & Format(txtdate,"yyyy/mm/dd") & "#"
End If

Me.SubformControlName.Form.RecordSource = strSQL & strWhere

You should, of course, make sure that the data in the controls in sensible and clean and that records are returned.

Upvotes: 3

Related Questions