Find Command Button - It used to work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Reply to group, rather than allenbrowne at mvps dot org.

Don Ireland said:
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
Click to expand...
 
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.

Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Don Ireland said:
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
Click to expand...
Click to expand...
 
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

Don Ireland said:
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.

Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Don Ireland said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Don Ireland said:
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

Don Ireland said:
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.

Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
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


Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Don Ireland said:
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

Don Ireland said:
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.

:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top