• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Find Funtion not working

I am using the ‘Find’ function in my project.  This function works well in my master form but falls over in any child form where it the child form is opened with the following:
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

The issue, I have found from research on the web, is with the ‘acDialog’ argument.

The error I am getting is as follows:

“The command or action ‘Find’ isn’t available now”

It is rumoured that the problem is the code which opens the recordset which needs changing it to use dbOpenDynaset which is necessary on linked Access tables.

Indeed my project does use linked Access tables and it is in forms bound to these linked tables that I have the issue.  

When the forms were just popup and before they were changed to ‘dialog’ the find function worked.  So I suppose it’s a combination of ‘dialog’ forms and linked Access tables?

Whatever – does anybody know how to fix the issue with the Find function please and preferably without reverting to popup forms which are more difficult to position on screen in relation to other forms.

  • 6
  • 4
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Exactly where/how are you using the Find function? If this is a dialog form, then I'm guessing you're doing this in the Load event? If so, how are you doing this?

Also, is this the Data Entry form you were working with in your earlier questions (i.e. the form where you've set DataEntry = Yes in the form properties)? If so, then I don't believe you can use any search methods in that instance, but I could be wrong.

If you are, you can always use the RecordsetClone to find your record, and then Bookmark the form. For example, if I pass in a value for a CustomerID in my OpenArgs, I could do this:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "CustomerID=" & Me.Openargs

If Not rst.NoMatch Then
  Me.Bookmark = rst.Bookmark
End If

MikeDTEAuthor Commented:
Hi LSMConsulting

The Find function is called from a button on the Form using the code below.

No it is not the same form as earlier this weekend but it is the same project.  BTW: the DataEntry that was set to 'Yes' is now set to 'No'.

The Find function is a string search - it's the standard control provided in Access/VBA - see image

Regards FandR.jpg
Private Sub FindButton_Click()
On Error GoTo Err_FindButton_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit Sub

    MsgBox Err.Description
    Resume Exit_FindButton_Click
End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
And the FindButton in the code above - where is this located? On the dialog form you're opening, or somewhere else?

FWIW, you really should replace that VERY outdated code with the new version, which is:

DoCmd.RunCommand acCmdFind

You also might try using Screen.ActiveControl.SetFocus ... some have reported better luck with that than with PreviousControl (especially when dealing with Dialog forms).

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

MikeDTEAuthor Commented:
Hi LSConsulting

The code is behind the FindButton in the form that the search works within

Form fJDWContacts has many Procs but if it helps I'll send all the code for the Form.

I will have a look at DoCmd.RunCommand acCmdFind and I'll try using Screen.ActiveControl.SetFocus as you suggest.  I'll post later.

Thanks for the help so far.
MikeDTEAuthor Commented:
Hi LSMConsulting

I adopted the changes you suggested and they do not work individually or collectively.

I have proven that the issue is with the acdialog arguement on the form open.  Without that arguement to form allows the Find function to work.  There are advantaved in opening the child form in 'dialog' mode because it stops users from going back to the parent form and leaving the child form open somewhere.

A popup form is the only way forward now - but that causes problems as I like to open forms in specific places and popup forms reference to screen co-ordinates whereas non-popup forms reference to the Access workspace.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Could you upload your database here? If so, please do this:

1) If your tables are Linked, create a "local" copy of the database. To do that, open the backend tables and import everything from the Frontend to the Backend (make a copy of your backend first, of course). Use this database to upload.

2) Compact and repair the database.

3) If it contains a password, remove the password

4) Provide exact instructions on recreating the issue. Don't just say "add a new record" - tell us exactly what form to open, what data we should try to enter, etc etc. Be as precise as possible.

5) Be SURE to remove an sensitive information like Phone numbers, emails, SSN's etc etc. EE is a public website, and anything you put up here is available to the harvest-bots.

MikeDTEAuthor Commented:

The database contains a lot of legacy data including 3000 contacts.  Also the bookings and payment tables contain sensitive data.  So can't send the database I'm afraid.

The issue is that the standard Access Find & Replace function will not work if the form that 'F&R' being hosted by is in 'dialog' mode.  If the host form is not opened with the 'acdialog' then 'F&R' works.

If I have to settle for a pop-up form then that's what I will have to do.
MikeDTEAuthor Commented:
I have reverted my code back to using a pop-up form and now the 'Find & Replace' function now works.  I do not like the pop-up so much because it doesn't fully lock the underlying forms and it is more difficult to position the form on screen if the Access window is not opened full-screen.

I have upped the points on this to 500 if anybody has a solution!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't think you'll find a way around this using the builtin Find function.

According to MSDN, when you open a form in Dialog mode, you're actually just setting the form's Modal and Popup properties (temporarily) to true. Apparently when this happens, the builtin Find function would work on the "active" form, which would be the form that called the Dialog form.

However, if you set those properties manually in the form that you had been opening as Dialog (i.e. set Popup=True and Modal=True) then you can run the builtin Find function successfully, so apparently something else is going on when you open a form in Dialog mode.

In fact, if you do this, and then set a breakpoint in your code BEFORE you call the Find function, you'll find that the ActiveForm is the form that called that dialog form, and the ActiveControl is the button you used to open that dialog form - even if you explicitly set the focus to the Textbox where you wish to search!.

I have no idea why this is - there are apparently other properties involved that are set when you call the form in Dialog mode, but there doesn't seem to be any documentation regarding those.

"I do not like the pop-up so much because it doesn't fully lock the underlying forms and it is more difficult to position the form on screen if the Access window is not opened full-screen."

You can also set the form to Modal = Yes, which forces the user to work with that form exclusively. As to moving it, then you're correct, it would take a bit more effort. That's one of the reasons many developers get away from the built in functionality like using acCmdFind instead of building their own Find function - you're often hobbled by those builtin functions. You can always use DoCmd.Move in the dialog form's open or load event to position (and size) it ...

MikeDTEAuthor Commented:
Thanks LSMConsulting

This is what I expected but your explanation does help me to see the reasons.  I do already set the co-ords of the form when opening the form but Pop-up form Down setting is from the top of the Windows Desktop screen whereas with a non-popup form the Down setting is from the top of any window that Accessed is opened in.  Thus unless Access is loaded full screen the pop-up window location is unpredictable. I will try the modal setting to keep the use in the current winow as you suggest.  Thanks - teh points are yours

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now