RobK wrote:
I got the same problem. I need a textbox where I can typ a name, and
when I press enter or a command button it should show me the matching
record in the form (so without opening any other tables or queries or
w/e)
San schreef:
If you are trying to just do a search for a form you should:
1. use a combo box instead of a text box that will allow the user to
choose the data not enter the data. this will stop data entry issues.
2. If you are trying to locate a record that is unique you can attach
this to the combo on the after update property after you change it to
match your form:
On Error GoTo xxx
Dim rs As Object
DoCmd.ShowAllRe cords
Set rs = Forms![FormName].Recordset.Clon e
rs.FindFirst "[Title] = '" & Forms![FormName]![ComboName] & "'"
If Not rs.EOF Then Forms![FormName].Bookmark = rs.Bookmark
Forms![Form]![ComboName] = ""
Exit Function
xxx:
MsgBox "There was an error executing the command.", vbCritical
Exit Function
3. If you need to apply a filter so multiple records appear from one
choice:
a. Create the combo that shows the data the user will search by then
attach this statement modified to your form on the after update
property of the combo
On Error GoTo ZZZ
DoCmd.ShowAllRe cords
Dim SrcSQL As String
SrcSQL = "SELECT TableName.* FROM TableName WHERE_
(((TableName.Fi eldName)=[Forms]![FormName]![ComboName]))"
DoCmd.ApplyFilt er SrcSQL
[Forms]![FormName]![ComboName] = ""
Exit Function
ZZZ:
MsgBox "There was an error executing the command.", vbCritical
Exit Function
Note: [Forms]![FormName]![ComboName] = "" is to make the combo become
blank after the search is complete so the user can search again. It
makes a clean search process.
You can also change your error loop message to match an access error
message
MsgBox err.description
4. For both combo search types you need to create a requery for the On
Enter Property to allow the combo to be required each time the user
enters it. This will stop it from droping data that has been added
since the form was opened.
On Error Resume Next
[Forms]![FormName]![ComboName].Requery
Exit Function
