Find Button Error

[Niall]

I have set up two command buttons on a Form (Access 2003).
One is a Find Button the other Displays a Report.

I have a simple database - One table. The Find Command Button searches all
fields in the database for the specified string.

The Report Button Displays a list of all Companies in the Database.

The Find Button works great on its own but displays an error if I select it
after I select the Report Button. the error is as follows

"A Macro set to one of the current field's properties failed because of an
error in a FindRecord action argument."

The code behind the Command Buttons is as follows:

***********
Private Sub FormSearch_Click()
On Error GoTo Err_FormSearch_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

DoCmd.FindRecord " ", acAnywhere, , acSearchAll, , acAll, True

Exit_FormSearch_Click:
Exit Sub

Err_FormSearch_Click:
MsgBox Err.Description
Resume Exit_FormSearch_Click

End Sub
Private Sub CompanyListButton_Click()
On Error GoTo Err_CompanyListButton_Click

Dim stDocName As String

stDocName = "Drug Delivery Company List"
DoCmd.OpenReport stDocName, acPreview

Exit_CompanyListButton_Click:
Exit Sub

Err_CompanyListButton_Click:
MsgBox Err.Description
Resume Exit_CompanyListButton_Click

End Sub
************

Any help would be much appreciated.

(I would also like to know how to clear the text entry field for the next
search. It currently retains the string entered for the last search).

Niall

 

[Ken Sheridan]

Niall:

Try changing the error handler to the following to ignore the anticipated
error:

Err_FormSearch_Click:
Select Case Err.Number
Case 2162
'do nothing
Case Else
MsgBox Err.Description
End Select
Resume Exit_FormSearch_Click

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Niall:

In reply to your second point all I can suggest is that you use SendKeys.
This is not something normally recommended but I can’t think of an
alternative here:

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

SendKeys "{DEL}"
DoCmd.FindRecord " ", acAnywhere, , acSearchAll, , acAll, True

Ken Sheridan
Stafford, England

 

[Niall]

Ken,

Thankyou for your help - almost out of the woods - but not quite.

Re. Clearing the 'input text field' each time I do a search - your fix
worked - with one exception. When I do a Find and then select the Command
Button to list the companies in my database and then return to do a Find -
the text from the last find is still in the 'input text field'.

To summarize:

Find followed by Find followed by Find, etc. works great - thank you.
Find followed by Report followed by Find results in text from original Find
remaining in the text box when I select the second find.

I enclose the code as it is now...

*********
Private Sub FormSearch_Click()
On Error GoTo Err_FormSearch_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

DoCmd.FindRecord " ", acAnywhere, , acSearchAll, , acAll, True

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

SendKeys "{DEL}"
DoCmd.FindRecord " ", acAnywhere, , acSearchAll, , acAll, True

Exit_FormSearch_Click:
Exit Sub

Err_FormSearch_Click:
Select Case Err.Number
Case 2162
'do nothing
Case Else
MsgBox Err.Description
End Select
Resume Exit_FormSearch_Click

End Sub
Private Sub CompanyListButton_Click()
On Error GoTo Err_CompanyListButton_Click

Dim stDocName As String

stDocName = "Drug Delivery Company List"
DoCmd.OpenReport stDocName, acPreview

Exit_CompanyListButton_Click:
Exit Sub

Err_CompanyListButton_Click:
MsgBox Err.Description
Resume Exit_CompanyListButton_Click

End Sub
*************

Very much appreciate the help.

Niall