Find Funtion not working

Posted on 2011-10-09
Last Modified: 2012-05-12
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.

Question by:MikeDTE
    LVL 84
    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


    Author Comment

    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

    LVL 84
    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).


    Author Comment

    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.

    Author Comment

    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.
    LVL 84
    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.


    Author Comment


    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.

    Author Comment

    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!
    LVL 84

    Accepted Solution

    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 ...


    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now