Manipulate Find Replace Dialog Box using Code

  • Thread starter Thread starter jeff001
  • Start date Start date
J

jeff001

Hi Everyone

I am trying to do a search on a field to see if a record already
exists using the code below.

Me.txtSearch.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

This opens the Find/Replace dialog box.

What I would like is to do is:
1. hide the "Replace" tab

2. If no record is found then I would like to display a
MsgBox "This record does not exist! Would you like to add it?
Yes/No and open a form.

Thanks
 
Jeff, you're going to have to provide your own interface to do this. Access
does not let you manipulate the Find/Replace dialog to that extent.

There is a way to hide the replace tab, by making the form read-only (e.g.
set Allow Edits to No.) Access recognises that you can't 'replace' anything
in a read-only form, and so hides the Replace tab. It's messy (as you have
to toggle the AllowEdits state), and buggy (there are cases where Access
doesn't get this right), so you're better of with your own interface.

See if this interface appeals:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
It consists of a combo where you choose the field to search, and a text box
where you type the value to find. You can set it up without having to write
any code.

For a more powerful interface, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one illustrates how to provide several boxes where the user can enter
criteria, and it matches any combination they choose to use. This one does
require you to write code for your specific form, but it is a technique
worth learning.

The remaining part of your question is how to jump to a new record:
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then RunCommand acCmdRecordsGotoNew
 
Hi Everyone

I am trying to do a search on a field to see if a record already
exists using the code below.

Me.txtSearch.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

This opens the Find/Replace dialog box.

What I would like is to do is:
1. hide the "Replace" tab

2. If no record is found then I would like to display a
MsgBox "This record does not exist! Would you like to add it?
Yes/No and open a form.

Thanks
Click to expand...

You could provide a lookup field then add the following to the after update
event of the field

Set rst = Me.RecordsetClone
rst.FindFirst "[Job] = """ & Me![FindJob] & """"
If rst.NoMatch Then
MsgBox "No Match for Job: " & Me![FindJob]
Me![FindJob] = Me![Job] ' Restore the lookup field to current job
number
Else
Me.Bookmark = rst.Bookmark ' Set the current record on the form
to the found job number
End If
Set rst = Nothing

In this case I am looking up a job name.
 
Thanks for the answers guys.

I went back to the client and told him
that a Google type search box is just not practical in Access.
And would you believe it, he took my advice.

So now I am using a Parameter Query and the client is happy.

LIke"*"&[Enter Part of Customer Name] &"*"

Thanks for the helpful suggestions

Jeff
 
Thanks for the answers guys.

I went back to the client and told him
that a Google type search box is just not practical in Access.
And would you believe it, he took my advice.

So now I am using a Parameter Query and the client is happy.

LIke"*"&[Enter Part of Customer Name] &"*"

Thanks for the helpful suggestions

Jeff
 
Back
Top