Discussion:
Find Command Button - It used to work
(too old to reply)
Don Ireland
2006-01-31 18:26:29 UTC
Permalink
Ok. This is odd....

I programmed a button to act as a "Finder". I modified the code that the
form wizard develops for you. Basically, it asks whether or not you want to
search by a number. This "number" field is typically locked, disabled and
otherwise untouchable unless the code makes it available. (In the past,
people have changed these numbers and not realized they did it....)

It used to work without issue. I have changed nothing in the code that does
the find. However, when I push the button now and answer "Yes" - search by
number, I receive an error message "Can't use find replace now". It leaves
this field unlocked and with the focus. I can push CTRL +F and do the search
anyway....... what gives???

Here is the code for the routine:

Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
Dim SearchWhere

SearchWhere = MsgBox("No' searches from Selected Field", vbYesNo +
vbDefaultButton1 + vbQuestion, "Search by Number?")
If SearchWhere = vbYes Then
Me![JobNum].Enabled = True
Me![JobNum].Locked = False
Me![JobNum].SetFocus
Else
Screen.PreviousControl.SetFocus
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindRecord_Click:
Exit Sub

Err_FindRecord_Click:
MsgBox Err.Description
Resume Exit_FindRecord_Click

End Sub

like I said, it used to work without error. Did something else get changed
in the database to generate this error??

Any helps and clues would be greatly appreciated
Allen Browne
2006-02-02 14:33:08 UTC
Permalink
Don, there are several possible issues, such as whether the form might
contain an entry that cannot be saved (and so cannot move), or whether the
DoMenuItem will work if the form is a popup (where the menus don't apply.)

You might be able to force the save with:
If Me.Dirty Then Me.Dirty = False
and bypass the menus with:
RunCommand acCmdFind

Ultimately, a better solution might be to add an unbound text box to the
form header where the user can just type in the number to match instead of
having to handle the Find dialog. You can just use the AfterUpdate event of
that box to move record. And you never have to enable the number box.

Add a text box with these properties:
Name txtFindJobNum
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the AfterUpdate property, and set
up the code something like this:

Private Sub txtFindJobNum_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
If Not IsNull(Me.txtFindJobNum) Then
If Me.Dirty Then Me.Dirty = False 'Save First
Set rs = Me.RecordsetClone
strWhere = "[JobNum] = " & Me.FindJobNum
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub

Note: If JobNum is actually a Text field (not a Number field), you need
extra quotes:
strWhere = "[JobNum] = """ & Me.FindJobNum & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok. This is odd....
I programmed a button to act as a "Finder". I modified the code that the
form wizard develops for you. Basically, it asks whether or not you want to
search by a number. This "number" field is typically locked, disabled and
otherwise untouchable unless the code makes it available. (In the past,
people have changed these numbers and not realized they did it....)
It used to work without issue. I have changed nothing in the code that does
the find. However, when I push the button now and answer "Yes" - search by
number, I receive an error message "Can't use find replace now". It leaves
this field unlocked and with the focus. I can push CTRL +F and do the search
anyway....... what gives???
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
Dim SearchWhere
SearchWhere = MsgBox("No' searches from Selected Field", vbYesNo +
vbDefaultButton1 + vbQuestion, "Search by Number?")
If SearchWhere = vbYes Then
Me![JobNum].Enabled = True
Me![JobNum].Locked = False
Me![JobNum].SetFocus
Else
Screen.PreviousControl.SetFocus
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_FindRecord_Click
End Sub
like I said, it used to work without error. Did something else get changed
in the database to generate this error??
Any helps and clues would be greatly appreciated
Don Ireland
2006-02-06 14:18:30 UTC
Permalink
Thanks Allen.

I checked the form - it is not a pop-up form. Nor are the other forms that
are experiencing the same issue.

I tried the resetting the dirty flag and using the runcommand accmdfind and
that also did not work. I will try the other suggestion in a few and see
what happens. Interestingly enough, I seem to have this issue when searching
from a text or number. the few times that I tried it on a date field, it
worked. I don't know if this offers any other clues but if it does.....

Thanks again and I'll let you know if it works.
Post by Allen Browne
Don, there are several possible issues, such as whether the form might
contain an entry that cannot be saved (and so cannot move), or whether the
DoMenuItem will work if the form is a popup (where the menus don't apply.)
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdFind
Ultimately, a better solution might be to add an unbound text box to the
form header where the user can just type in the number to match instead of
having to handle the Find dialog. You can just use the AfterUpdate event of
that box to move record. And you never have to enable the number box.
Name txtFindJobNum
Format General Number
After Update [Event Procedure]
Then click the Build button (...) beside the AfterUpdate property, and set
Private Sub txtFindJobNum_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
If Not IsNull(Me.txtFindJobNum) Then
If Me.Dirty Then Me.Dirty = False 'Save First
Set rs = Me.RecordsetClone
strWhere = "[JobNum] = " & Me.FindJobNum
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub
Note: If JobNum is actually a Text field (not a Number field), you need
strWhere = "[JobNum] = """ & Me.FindJobNum & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok. This is odd....
I programmed a button to act as a "Finder". I modified the code that the
form wizard develops for you. Basically, it asks whether or not you want to
search by a number. This "number" field is typically locked, disabled and
otherwise untouchable unless the code makes it available. (In the past,
people have changed these numbers and not realized they did it....)
It used to work without issue. I have changed nothing in the code that does
the find. However, when I push the button now and answer "Yes" - search by
number, I receive an error message "Can't use find replace now". It leaves
this field unlocked and with the focus. I can push CTRL +F and do the search
anyway....... what gives???
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
Dim SearchWhere
SearchWhere = MsgBox("No' searches from Selected Field", vbYesNo +
vbDefaultButton1 + vbQuestion, "Search by Number?")
If SearchWhere = vbYes Then
Me![JobNum].Enabled = True
Me![JobNum].Locked = False
Me![JobNum].SetFocus
Else
Screen.PreviousControl.SetFocus
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_FindRecord_Click
End Sub
like I said, it used to work without error. Did something else get changed
in the database to generate this error??
Any helps and clues would be greatly appreciated
Don Ireland
2006-02-06 21:28:40 UTC
Permalink
Ok,

I tried the "better solution" and received more error message than I knew
what to do with. For whatever reason, the Dim rs as DAO.recordset was not
valid. I received a "User defined type" error. So, I took out the DAO part.
The declaration worked, but nothing else did. The rs.findfirst strwhere
caused an error even after I adjusted the strwhere for being a text field.....

ANy other ideas? What would have allowed the code I wrote to function for
over a year and then stop working? It sounds like a setting was changed
somewhere, but i have no clue where. I am still open for suggestions.

Thanks
Post by Don Ireland
Thanks Allen.
I checked the form - it is not a pop-up form. Nor are the other forms that
are experiencing the same issue.
I tried the resetting the dirty flag and using the runcommand accmdfind and
that also did not work. I will try the other suggestion in a few and see
what happens. Interestingly enough, I seem to have this issue when searching
from a text or number. the few times that I tried it on a date field, it
worked. I don't know if this offers any other clues but if it does.....
Thanks again and I'll let you know if it works.
Post by Allen Browne
Don, there are several possible issues, such as whether the form might
contain an entry that cannot be saved (and so cannot move), or whether the
DoMenuItem will work if the form is a popup (where the menus don't apply.)
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdFind
Ultimately, a better solution might be to add an unbound text box to the
form header where the user can just type in the number to match instead of
having to handle the Find dialog. You can just use the AfterUpdate event of
that box to move record. And you never have to enable the number box.
Name txtFindJobNum
Format General Number
After Update [Event Procedure]
Then click the Build button (...) beside the AfterUpdate property, and set
Private Sub txtFindJobNum_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
If Not IsNull(Me.txtFindJobNum) Then
If Me.Dirty Then Me.Dirty = False 'Save First
Set rs = Me.RecordsetClone
strWhere = "[JobNum] = " & Me.FindJobNum
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub
Note: If JobNum is actually a Text field (not a Number field), you need
strWhere = "[JobNum] = """ & Me.FindJobNum & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok. This is odd....
I programmed a button to act as a "Finder". I modified the code that the
form wizard develops for you. Basically, it asks whether or not you want to
search by a number. This "number" field is typically locked, disabled and
otherwise untouchable unless the code makes it available. (In the past,
people have changed these numbers and not realized they did it....)
It used to work without issue. I have changed nothing in the code that does
the find. However, when I push the button now and answer "Yes" - search by
number, I receive an error message "Can't use find replace now". It leaves
this field unlocked and with the focus. I can push CTRL +F and do the search
anyway....... what gives???
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
Dim SearchWhere
SearchWhere = MsgBox("No' searches from Selected Field", vbYesNo +
vbDefaultButton1 + vbQuestion, "Search by Number?")
If SearchWhere = vbYes Then
Me![JobNum].Enabled = True
Me![JobNum].Locked = False
Me![JobNum].SetFocus
Else
Screen.PreviousControl.SetFocus
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_FindRecord_Click
End Sub
like I said, it used to work without error. Did something else get changed
in the database to generate this error??
Any helps and clues would be greatly appreciated
Allen Browne
2006-02-07 00:50:17 UTC
Permalink
From the code window, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library

More info on solving library reference problems:
http://allenbrowne.com/ser-38.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok,
I tried the "better solution" and received more error message than I knew
what to do with. For whatever reason, the Dim rs as DAO.recordset was not
valid. I received a "User defined type" error. So, I took out the DAO part.
The declaration worked, but nothing else did. The rs.findfirst strwhere
caused an error even after I adjusted the strwhere for being a text field.....
ANy other ideas? What would have allowed the code I wrote to function for
over a year and then stop working? It sounds like a setting was changed
somewhere, but i have no clue where. I am still open for suggestions.
Thanks
Post by Don Ireland
Thanks Allen.
I checked the form - it is not a pop-up form. Nor are the other forms that
are experiencing the same issue.
I tried the resetting the dirty flag and using the runcommand accmdfind and
that also did not work. I will try the other suggestion in a few and see
what happens. Interestingly enough, I seem to have this issue when searching
from a text or number. the few times that I tried it on a date field, it
worked. I don't know if this offers any other clues but if it does.....
Thanks again and I'll let you know if it works.
Post by Allen Browne
Don, there are several possible issues, such as whether the form might
contain an entry that cannot be saved (and so cannot move), or whether the
DoMenuItem will work if the form is a popup (where the menus don't apply.)
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdFind
Ultimately, a better solution might be to add an unbound text box to the
form header where the user can just type in the number to match instead of
having to handle the Find dialog. You can just use the AfterUpdate event of
that box to move record. And you never have to enable the number box.
Name txtFindJobNum
Format General Number
After Update [Event Procedure]
Then click the Build button (...) beside the AfterUpdate property, and set
Private Sub txtFindJobNum_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
If Not IsNull(Me.txtFindJobNum) Then
If Me.Dirty Then Me.Dirty = False 'Save First
Set rs = Me.RecordsetClone
strWhere = "[JobNum] = " & Me.FindJobNum
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub
Note: If JobNum is actually a Text field (not a Number field), you need
strWhere = "[JobNum] = """ & Me.FindJobNum & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok. This is odd....
I programmed a button to act as a "Finder". I modified the code that the
form wizard develops for you. Basically, it asks whether or not you
want
to
search by a number. This "number" field is typically locked, disabled and
otherwise untouchable unless the code makes it available. (In the past,
people have changed these numbers and not realized they did it....)
It used to work without issue. I have changed nothing in the code
that
does
the find. However, when I push the button now and answer "Yes" -
search
by
number, I receive an error message "Can't use find replace now". It leaves
this field unlocked and with the focus. I can push CTRL +F and do
the
search
anyway....... what gives???
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
Dim SearchWhere
SearchWhere = MsgBox("No' searches from Selected Field", vbYesNo +
vbDefaultButton1 + vbQuestion, "Search by Number?")
If SearchWhere = vbYes Then
Me![JobNum].Enabled = True
Me![JobNum].Locked = False
Me![JobNum].SetFocus
Else
Screen.PreviousControl.SetFocus
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_FindRecord_Click
End Sub
like I said, it used to work without error. Did something else get changed
in the database to generate this error??
Any helps and clues would be greatly appreciated
Don Ireland
2006-02-09 14:00:18 UTC
Permalink
Allen,

This worked perfect. I had three seperate tables and associated forms that
I needed to be able to search through. I setup a public subroutine and
handed it whatever table I needed to search through. It works great. Thanks
for the help.

Here is another question on this topic. Multiple users access the database
(No it is not split between front end and back-end... I will get that as soon
as I can....). I tried doing the "search by Number", it crashed on another
PC. Do I need to go around and make sure all PC's reference that same
Library or can the database force it to load when it does?

Thanks again for the help and advice. I have already bookmarked your
website to search through as the need arises.

Don
Post by Allen Browne
From the code window, choose References on the Tools menu.
Microsoft DAO 3.6 Library
http://allenbrowne.com/ser-38.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok,
I tried the "better solution" and received more error message than I knew
what to do with. For whatever reason, the Dim rs as DAO.recordset was not
valid. I received a "User defined type" error. So, I took out the DAO part.
The declaration worked, but nothing else did. The rs.findfirst strwhere
caused an error even after I adjusted the strwhere for being a text field.....
ANy other ideas? What would have allowed the code I wrote to function for
over a year and then stop working? It sounds like a setting was changed
somewhere, but i have no clue where. I am still open for suggestions.
Thanks
Post by Don Ireland
Thanks Allen.
I checked the form - it is not a pop-up form. Nor are the other forms that
are experiencing the same issue.
I tried the resetting the dirty flag and using the runcommand accmdfind and
that also did not work. I will try the other suggestion in a few and see
what happens. Interestingly enough, I seem to have this issue when searching
from a text or number. the few times that I tried it on a date field, it
worked. I don't know if this offers any other clues but if it does.....
Thanks again and I'll let you know if it works.
Post by Allen Browne
Don, there are several possible issues, such as whether the form might
contain an entry that cannot be saved (and so cannot move), or whether the
DoMenuItem will work if the form is a popup (where the menus don't apply.)
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdFind
Ultimately, a better solution might be to add an unbound text box to the
form header where the user can just type in the number to match instead of
having to handle the Find dialog. You can just use the AfterUpdate event of
that box to move record. And you never have to enable the number box.
Name txtFindJobNum
Format General Number
After Update [Event Procedure]
Then click the Build button (...) beside the AfterUpdate property, and set
Private Sub txtFindJobNum_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
If Not IsNull(Me.txtFindJobNum) Then
If Me.Dirty Then Me.Dirty = False 'Save First
Set rs = Me.RecordsetClone
strWhere = "[JobNum] = " & Me.FindJobNum
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub
Note: If JobNum is actually a Text field (not a Number field), you need
strWhere = "[JobNum] = """ & Me.FindJobNum & """"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Ok. This is odd....
I programmed a button to act as a "Finder". I modified the code that the
form wizard develops for you. Basically, it asks whether or not you
want
to
search by a number. This "number" field is typically locked, disabled and
otherwise untouchable unless the code makes it available. (In the past,
people have changed these numbers and not realized they did it....)
It used to work without issue. I have changed nothing in the code
that
does
the find. However, when I push the button now and answer "Yes" -
search
by
number, I receive an error message "Can't use find replace now". It leaves
this field unlocked and with the focus. I can push CTRL +F and do
the
search
anyway....... what gives???
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click
Dim SearchWhere
SearchWhere = MsgBox("No' searches from Selected Field", vbYesNo +
vbDefaultButton1 + vbQuestion, "Search by Number?")
If SearchWhere = vbYes Then
Me![JobNum].Enabled = True
Me![JobNum].Locked = False
Me![JobNum].SetFocus
Else
Screen.PreviousControl.SetFocus
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_FindRecord_Click
End Sub
like I said, it used to work without error. Did something else get changed
in the database to generate this error??
Any helps and clues would be greatly appreciated
Allen Browne
2006-02-09 14:12:37 UTC
Permalink
That could be anything.

Splitting will help.

Ensure all PCs have service packs applied for Office and also for JET 4.

Make sure that Name AutoCorrupt is turned off:
http://allenbrowne.com/bug-03.html

Try a compact, then decompile, then compact.

More ideas:
http://allenbrowne.com/ser-25.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Post by Don Ireland
Here is another question on this topic. Multiple users access the database
(No it is not split between front end and back-end... I will get that as soon
as I can....). I tried doing the "search by Number", it crashed on another
PC. Do I need to go around and make sure all PC's reference that same
Library or can the database force it to load when it does?
Continue reading on narkive:
Loading...