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.
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.
Last Comment
ASKER
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
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
Private Sub FindButton_Click()
On Error GoTo Err_FindButton_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_FindButton_Click:
Exit Sub
Err_FindButton_Click:
MsgBox Err.Description
Resume Exit_FindButton_Click
End Sub
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.SetFo
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.SetFo
ASKER
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.SetFo
Thanks for the help so far.
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.SetFo
Thanks for the help so far.
ASKER
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.
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.
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.
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.
ASKER
Hi
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.
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.
ASKER
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!
I have upped the points on this to 500 if anybody has a solution!
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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