Search Command Button

[Richie2837]

I've set up a command button on my form in my Contacts database to carry out a search (I used the built-in Wizard).
The command button invokes the Find & Replace function, which works perfectly well, but is there a way of refining it so that the search dialog box opens with "Any Part of Field" selected in the "Match:" criteria, and the whole form rather being selected on the "Look In:" field? Or is there a better (i.e. more fool-proof) way of carrying out a search rather than using the wizard and built-in Find & Replace function? I'm open to suggestions.

I really need to do this so the non-Access clued-up users will be able to carry out a reliable and accurate search without getting themselves in a panic. So far I've had five duplicate people created in my Contacts database because the Search function didn't find the person they were looking for.

 

[yammoo]

possible answer in another posting

i don't know if you have come across the same posting within this very forum, but when i skimmed your problem and saw that you had no response, i recalled a similar posting. it was just a matter of finding it.

http://www.access-programmers.co.uk/forums/showthread.php?t=108451&highlight=find+replace

if not, try try again via a different forum.

yammoo

 

[Wiz47]

I've set up a command button on my form in my Contacts database to carry out a search (I used the built-in Wizard).
The command button invokes the Find & Replace function, which works perfectly well, but is there a way of refining it so that the search dialog box opens with "Any Part of Field" selected in the "Match:" criteria, and the whole form rather being selected on the "Look In:" field? Or is there a better (i.e. more fool-proof) way of carrying out a search rather than using the wizard and built-in Find & Replace function? I'm open to suggestions.

I really need to do this so the non-Access clued-up users will be able to carry out a reliable and accurate search without getting themselves in a panic. So far I've had five duplicate people created in my Contacts database because the Search function didn't find the person they were looking for.

You might want to have a Command Save button OnClick Event do a search for duplicates. Then, if it finds a dupe it will call a message to ask the user what they want to do.

Something like ... (quasi code because it's early and I might not have this quite right )

Private Sub cmdSaveNewRecord_Click()
Dim db As dao.Database
Dim RS As dao.Recordset
Set db = CurrentDb()

Set RS = db.OpenRecordset ("YourTableName")
Do Until RS.EOF
  If YourTable.YourNameField = Forms!YourFormName!ControlNameField then
  
Select Case MsgBox("A Duplicate exists in the records, Continue?", vbYesNo _ 
Or vbQuestion Or vbDefaultButton1, "Verify Duplicate Information")
    
Case vbNo
   Me.Undo      
    Exit Sub
    
Case vbYes
   'Let them save new contact    
     end if

RS.MoveNext
  Loop
End Sub