Adding a FIND Button to a Data Access Sheet

[Niall]

Dear one and all,

I NEED step-by-step help to Add a Find Button to a Data Access Sheet I have
set up for others to review information in a 500+ customer Database.

I am using Access 2003 and believe that "Add a Find button to a data access
page" http://office.microsoft.com/en-us/assistance/HA010345491033.aspx
represents the closest I have gotten to my answer?

Step 4. States "On the data access page, click the command button in the
header section that corresponds to the recordset that you want to search."
As I am only searching one database - I presumed this to mean put the
Command button wherever you want on the Data Access Sheet.

"Step 5. Set the properties of the command button to make it look the way
you want."
OK

"Step 6. In the OnClick event of the Find button, add code to clone the
recordset, to prompt and accept input from the user, and to search the cloned
recordset for the corresponding record."
Completely lost! Searched the properties of the Button but could no
reference to "OnClick" or an "Event".

Please help. Specifically what I am trying to achieve is to have the user
click on the FIND BUTTON and be brought to a pop-up screen with one field
where they could enter a text string, hit Enter or press a "go button" and
have the program search all fields for the text string that was entered and
return "hits" - one record at a time - with the option to "Find Next" until
all hits are exhausted. (The same way that the Find Function works when in
Form View).

Have been at this for a while - "step-by-step" help would be greatly
appreciated.

Thanks in advance.

 

[Chris Burnette]

Step 4. States "On the data access page, click the command button in the

header section that corresponds to the recordset that you want to search."
As I am only searching one database - I presumed this to mean put the
Command button wherever you want on the Data Access Sheet.

Technically, I think it's telling you to put it in the header section of the
data access sheet, although this may or may not make a difference.

"Step 6. In the OnClick event of the Find button, add code to clone the
recordset, to prompt and accept input from the user, and to search the cloned
recordset for the corresponding record."
Completely lost! Searched the properties of the Button but could no
reference to "OnClick" or an "Event".

The "OnClick" event is what happens when a user clicks the button. To code
this, you will need to right-click the button in design view and click Build
(at least this is how it's done in Access forms). That should open up the
Visual Basic window, and from there you can code your Find button.

In my Access form, the code for my Find button looks like this:

Private Sub Find_Click()
On Error GoTo Err_Find_Click

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

Exit_Find_Click:
Exit Sub

Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click

End Sub

As for cloning the recordset, you will probably need to use RecordsetClone.
Try something like this:

Dim rst As Recordset
Set rst = Me.RecordsetClone

To prompt the user for input you can use an InputBox, and to search for the
corresponding record you would use the FindRecord method.

I would suggest checking the Help in VBA for RecordsetClone, InputBox, and
FindRecord. Creating a Find button in an Access form is remarkably easy
(there is actually a wizard to do it), however it sounds like it may be
considerably more difficult for a data access page.

Sorry I couldn't give you something more step-by-step, but hopefully I've at
least given you some useful information.

-Chris

 

[Niall]

Chris,

Appreciate all of your efforts but this does not work for a Data Access Page.

My understanding is that I will need to produce a Data Access Page to ensure
that users do not corrupt the data in the database (which can readily happen
if I give them access to the Forms). Perhaps I am wrong?

 

[Chris Burnette]

No, that's not exactly correct. You have users use forms rather than giving
them access to your tables. The only reason you would need a data access
sheet is if people needed to access your data via the web.

If you're really worried about data getting corrupted, you may want to use
things like input masks and data validation to make sure that the data is
entered in the correct format. In any case, the code I gave you should work
for a form, which is generally how you want to have users interact with your
data.

-Chris

 

[Niall]

Chris,

Thanks for the help. I will no longer try and make it happen on a datasheet.

I have set up the Find function using the Wizard. I would now like to be
able to edit the code to get rid of the "Replace" option that also comes up
an to set the default search to Look in all fields in the Form (which are all
of the fields in my database) and to Match any Part of Field.

It does not appear to be such a big deal - but I have been unable to manage
it.

Niall