Find record function

Topic

New Reply

Hi,

We have a database which has a ‘Find record’ button which basically brings up the same prompt as Ctrl+F. The code for this button is shown below:

Private Sub FindRecordButton_Click()
On Error GoTo Err_FindRecordButton_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindRecordButton_Click:
Exit Sub

Err_FindRecordButton_Click:
MsgBox Err.Description
Resume Exit_FindRecordButton_Click

End Sub

There are a few issues with this – one is that it will only search ‘forward’ in the database (ie, if you’ve already gone past the record it won’t find it), two is that it defaults to matching the “Whole field” rather then “Part of field” and three is that you have to be clicked in the field you want to search.

Is there a better way to create a ‘Find Record’ button which will allow the user to:

[*]Search both forwards and backwards in the database, essentially looping through the records until they find the one they want.
[*]Default to searching any part of the field instead of the whole field
[*]Being able to select the field you want to search on

Cheers,
Jason

Reply | Quote

Replies

1) The Find dialog does cycle. It stops when all records have been searched once.

2) To make Access search any part of the field, replace the line

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

with

SendKeys “%ha%n”
RunCommand acCmdFind

3) You could place a combo box next to the command button from which the user can select a field. The code would then set focus to the corresponding control instead of to Screen.PreviousControl.

Reply | Quote

1) The Find dialog does cycle. It stops when all records have been searched once.

2) To make Access search any part of the field, replace the line

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

with

SendKeys “%ha%n”
RunCommand acCmdFind

3) You could place a combo box next to the command button from which the user can select a field. The code would then set focus to the corresponding control instead of to Screen.PreviousControl.

Thanks Hans, that has resolved my issue.

Cheers,
Jason

Reply | Quote