filtering

[nashr1928]

Could some explain to me how to use a text field on a form so that can type in any word that matches any data and then have a button next to the field to execute the search? As I could type in the text field maintenance and then select the button and all data would be displayed that has the word maintenance in it.

[maximus]

Dim strCriteria as string
strCriteria="EmployeeName='" & Me.Text21 & "'"
DoCmd.ApplyFilter , strCriteria

This is a simple example:

I attach this code to the onClick Event of a command button on a form.
Text21 is where I type the name of the employee. This is used as a criteria to filter the form.

This is a simple example.

[jmanis]

Maximus,

I am trying to do something similar and I keep getting an error when I used the code you provided.

Dim strCriteria as string
strCriteria="EmployeeName='" & Me.Text21 & "'"
DoCmd.ApplyFilter , strCriteria

The code reads as follows:
Run-time error '3075':
Syntax error (missing operator) in query expression 'Last Name='___"

The ___ is whatever last name I try and search for.

I am not sure what I am doing wrong. I am pretty familiar with the basics of access but this is proving to be beyond my scope. Thanks if anyone can help.

[nashr1928]

This is what I ended up using in the code for the command button.
strCriteria = "Customer='" & Me.Text21 & "'"
DoCmd.ApplyFilter , strCriteria

Where customer is the data being searched.

[jmanis]

I am still getting the same error message. I made a rookie mistake and put spaces when naming all of my objects, do you think that is the problem?

This is how the code looks for me:

strCriteria = "Last Name='" & Me.txtSearch & "'"
DoCmd.ApplyFilter , strCriteria

[nashr1928]

The purpose for my search is tp find all the records of a certain customer by typing their name in a text field on a form and then pressing a form filter button next to it.
For the properties of the text field I used Customer for the control source and the name happened to be text21.

Here is exactly what I typed in to the code section for the command button which happened to be called command 197.

Private Sub Command197_Click()
On Error GoTo Err_Command197_Click
Dim strCriteria As String
strCriteria = "Customer='" & Me.Text21 & "'"
DoCmd.ApplyFilter , strCriteria

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
Exit_Command197_Click:
Exit Sub
Err_Command197_Click:
MsgBox Err.Description
Resume Exit_Command197_Click

End Sub

You could just copy this code, paiste in place and change the command button number instead of 197 to what yuors already is and change the text number to whatever yours is instead of 21.

[jmanis]

Thanks for your help but for some reason I am still getting the same error.

[nashr1928]

Sorry I cant help further. This looks like a mis spelling or incorrect name of data source or like that. I know u dont want to hear this but I would take a days break from it and approach with a fresh start. Sorrry .....

[jmanis]

No worries nash. I appreciate your help. I am actually going to do just that and then probably restructure the entire database from square 1. It seems that bounded forms are easier to use so I am going to research them and see what I can do. Thanks again.

[jmanis]

Nash, I figured it out. I was missing brackets around my field name since there was a space. Someone told me about this in my other post so it worked out good. Here is the code I used in case you need it for anything in the future or whatever.

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click
Dim strCriteria As String
strCriteria = "[Last Name]='" & Me.txtSearch & "'"
DoCmd.ApplyFilter , strCriteria
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
Exit_cmdFilter_Click:
Exit Sub
Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click
End Sub

[maximus]

well atlast .................................................. ...........................

[maximus]

[] backets are required when you have spaces in fieldnames.

[maximus]

any ways nash mark the thread solved if your problem is solved.