Search & Replace Function

[Proaccesspro]

I'm trying to create a search & replace function in Access. Specifically, I want to search for a specific SSN and replace it, if found. Not sure how to code the "guts" of it. Here is what I have so far:

Private Sub CmdUpdateAppeal_Click()
SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")
If SSNreplace = "" Then

MsgBox "No Number Entered. Please Try Again", vbCritical, "Update a SSN"

End
End If

If Not SSNreplace


If MsgBox("Is this the correct client?", vbYesNoCancel) <> vbYes Then
GoTo Flag1
End If
Else
MsgBox "Sorry, SSN NOT Found. Please Try Again", vbCritical, "Update a SSN"

End If

End Sub

The field name is called PersonID

[puppydogbuddy]

I'm trying to create a search & replace function in Access. Specifically, I want to search for a specific SSN and replace it, if found. Not sure how to code the "guts" of it. Here is what I have so far:

Private Sub CmdUpdateAppeal_Click()
SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")
If SSNreplace = "" Then

MsgBox "No Number Entered. Please Try Again", vbCritical, "Update a SSN"

End
End If

If Not SSNreplace


If MsgBox("Is this the correct client?", vbYesNoCancel) <> vbYes Then
GoTo Flag1
End If
Else
MsgBox "Sorry, SSN NOT Found. Please Try Again", vbCritical, "Update a SSN"

End If

End Sub

The field name is called PersonID

You did not elaborate much on the replace function, but the following link contains an excellent step by step and code for creating a search facility.

http://www.databasedev.co.uk/text_search.html

[Proaccesspro]

You did not elaborate much on the replace function, but the following link contains an excellent step by step and code for creating a search facility.

http://www.databasedev.co.uk/text_search.html

Thanks. I can relate to the search part of it, it's the replace part I'm not sure about....Basically, what I will want to do is update the corressponding SSN, if found, with the a corrected version....in other words, the user will enter a SSN into the input box, if found, the user will then have the opportunity to input a new SSN that will over-write the intial one.

[puppydogbuddy]

Thanks. I can relate to the search part of it, it's the replace part I'm not sure about....Basically, what I will want to do is update the corressponding SSN, if found, with the a corrected version....in other words, the user will enter a SSN into the input box, if found, the user will then have the opportunity to input a new SSN that will over-write the intial one.

Ok, what you are describing is not a replace function; you want to "clear" the search control (your input box) for the next entry. This is done by setting focus on the bound control PersonID and setting the input box to an empty string (“”). The code is contained in the link I gave you. I have excerpted the portion of the code that clears the input box (see below) and tried to make it more relevant by using object names similar to the ones you are using. All you need to do is substitute the actual name of your objects

‘PersonID is the control bound to table (strStudentRef in the link).
‘strSearchID is the variable that captures the search string that was entered into the textbox (strSearch in the link)
‘txtSearchID is the textbox used to enter the search string (txtSearch in the link) 
'If matching record found, sets focus in PersonID, shows msgbox
'and clears search control
 
    If PersonID = strSearchID Then
        MsgBox "Match Found For: " & strSearchID, , "Search Result"
        PersonID.SetFocus
        txtSearchID = ""                                     ‘textbox equivalent to your input box
 
 
  End If

[Proaccesspro]

Ok, what you are describing is not a replace function; you want to "clear" the search control (your input box) for the next entry. This is done by setting focus on the bound control PersonID and setting the input box to an empty string (“”). The code is contained in the link I gave you. I have excerpted the portion of the code that clears the input box (see below) and tried to make it more relevant by using object names similar to the ones you are using. All you need to do is substitute the actual name of your objects

‘PersonID is the control bound to table (strStudentRef in the link).
‘strSearchID is the variable that captures the search string that was entered into the textbox (strSearch in the link)
‘txtSearchID is the textbox used to enter the search string (txtSearch in the link) 
'If matching record found, sets focus in PersonID, shows msgbox
'and clears search control
 
    If PersonID = strSearchID Then
        MsgBox "Match Found For: " & strSearchID, , "Search Result"
        PersonID.SetFocus
        txtSearchID = ""                                     ‘textbox equivalent to your input box
 
 
  End If

Not quite getting the results I thought...I tested the code, even when I enter a REAL SSN, it will NOT find a match ...Also, is there a way to format the input box with an input mask of xxx-xx-xxxx?? Here is the code I have:

Dim SSNreplace As String

SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")

If SSNreplace = "" Then

MsgBox "No Number Entered. Please Try Again", vbCritical, "Update a SSN"

GoTo Flag1

End If


If SSNreplace = SSN Then
MsgBox "Match Found For: " & SSNreplace, vbInformation, "Update a SSN"
PersonID.SetFocus
txtSearchID = "" 'textbox equivalent to your input box

Else

MsgBox "Sorry, SSN NOT Found. Please Try Again", vbCritical, "Update a SSN"

End If


Flag1:

End Sub

[puppydogbuddy]

Not quite getting the results I thought...I tested the code, even when I enter a REAL SSN, it will NOT find a match ...Also, is there a way to format the input box with an input mask of xxx-xx-xxxx?? Here is the code I have:

Dim SSNreplace As String

SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")

If SSNreplace = "" Then

MsgBox "No Number Entered. Please Try Again", vbCritical, "Update a SSN"

GoTo Flag1

End If


If SSNreplace = SSN Then
MsgBox "Match Found For: " & SSNreplace, vbInformation, "Update a SSN"
PersonID.SetFocus
txtSearchID = "" 'textbox equivalent to your input box

Else

MsgBox "Sorry, SSN NOT Found. Please Try Again", vbCritical, "Update a SSN"

End If


Flag1:

End Sub

I will look at your code tomorrow. In the meantime, here is my response to your questions re: input mask for input box:

The inputbox doesn't have an input mask feature. You would be better off incorporating the mask in a a textbox control on the search form (like the one in the link I gave you), or on a small popup form by itself. Here’s the steps involved in creating your own form:

1. Create an unbound form with an unbound textbox control.
2. Set the control's input mask property.
3. Add a command button to this form.
4. Code it's click event:
Me.Visible = False
Name this form "SSNMaskForm"

You would then call this form (instead of the InputBox).

DoCmd.OpenForm "SSNMaskForm", , , , , acDialog
If Forms!FormName!ControlName = "SSN" then
' Correct SSN do whatever is wanted here
Else
' Wrong SSN msgbox.
End if
DoCmd.Close acForm, "SSNMaskForm"

[Proaccesspro]

I will look at your code tomorrow. In the meantime, here is my response to your questions re: input mask for input box:

The inputbox doesn't have an input mask feature. You would be better off incorporating the mask in a a textbox control on the search form (like the one in the link I gave you), or on a small popup form by itself. Here’s the steps involved in creating your own form:

1. Create an unbound form with an unbound textbox control.
2. Set the control's input mask property.
3. Add a command button to this form.
4. Code it's click event:
Me.Visible = False
Name this form "SSNMaskForm"

You would then call this form (instead of the InputBox).

DoCmd.OpenForm "SSNMaskForm", , , , , acDialog
If Forms!FormName!ControlName = "SSN" then
' Correct SSN do whatever is wanted here
Else
' Wrong SSN msgbox.
End if
DoCmd.Close acForm, "SSNMaskForm"

OK....I'll wait for your reply..just to be clear...my intent is to allow the user to input a SSN, use the nyumber entered to search the databse for a match, if found it would allow the user via an additional input box to enter a corrected SSN. Once enterd, a message box would indicate the update was successful. I'm thinking that is why I want the input mask so they will be forced to enter the correct number of digits. Thanks for the help!!

[puppydogbuddy]

OK....I'll wait for your reply..just to be clear...my intent is to allow the user to input a SSN, use the nyumber entered to search the databse for a match, if found it would allow the user via an additional input box to enter a corrected SSN. Once enterd, a message box would indicate the update was successful. I'm thinking that is why I want the input mask so they will be forced to enter the correct number of digits. Thanks for the help!!

Try this revised code. You have my comments about the input box.

Private Sub FindSSN_Click()
On Error GoTo Flag2
 
Dim SSNreplace As String
 
StartHere:
 
DoCmd.GoToControl "SSN"           ‘Is your control named SSN or PersonID?
SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")
DoCmd.FindRecord SSNreplace, acAnywhere, , acSearchAll, , acCurrent, True
If SSNreplace = "" Then
    MsgBox "No Number Entered. Please Try Again", vbCritical, "Update a SSN"
ElseIf SSNreplace = SSN Then
    MsgBox "Match Found For: " & SSNreplace, vbInformation, "Update a SSN"
    GoTo Flag1
Else
    MsgBox "Sorry, SSN NOT Found. Please Try Again", vbCritical, "Update a SSN"
    GoTo Flag1
End If
 
 
Flag1:
    SSNReplace = “”    ‘reset (clear) variable for reuse
    Exit Sub
 
Flag2:
    MsgBox Err.Description
    Resume Flag1:
 
End Sub
 

[Proaccesspro]

Try this revised code. You have my comments about the input box.

Private Sub FindSSN_Click()
On Error GoTo Flag2
 
Dim SSNreplace As String
 
StartHere:
 
DoCmd.GoToControl "SSN"           ‘Is your control named SSN or PersonID?
SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")
DoCmd.FindRecord SSNreplace, acAnywhere, , acSearchAll, , acCurrent, True
If SSNreplace = "" Then
    MsgBox "No Number Entered. Please Try Again", vbCritical, "Update a SSN"
ElseIf SSNreplace = SSN Then
    MsgBox "Match Found For: " & SSNreplace, vbInformation, "Update a SSN"
    GoTo Flag1
Else
    MsgBox "Sorry, SSN NOT Found. Please Try Again", vbCritical, "Update a SSN"
    GoTo Flag1
End If
 
 
Flag1:
    SSNReplace = “”    ‘reset (clear) variable for reuse
    Exit Sub
 
Flag2:
    MsgBox Err.Description
    Resume Flag1:
 
End Sub
 

OK, I input and tested the code. It's not quite right. Here is what happens:

When I first click the command button , I get the input box asking for a SSN. If I enter NO data and click the OK or Cancel button, I get this message:
FindRecord action requires a Find what argument. (I'm guessing that is coming from Flag 2) Also, is there a way for the systerm to just exit if someone were to click the CANCEL button???

Secondly, I closed the databse and then opened it again and started from scratch. I input a SSN and clicked OK. When I do, I get this message:
You can't use find or replace now. (Flag 2 again, I assume)

[puppydogbuddy]

OK, I input and tested the code. It's not quite right. Here is what happens:

When I first click the command button , I get the input box asking for a SSN. If I enter NO data and click the OK or Cancel button, I get this message:
FindRecord action requires a Find what argument. (I'm guessing that is coming from Flag 2) Also, is there a way for the systerm to just exit if someone were to click the CANCEL button???

Secondly, I closed the databse and then opened it again and started from scratch. I input a SSN and clicked OK. When I do, I get this message:
You can't use find or replace now. (Flag 2 again, I assume)

No, the main problem was that the code had no responses to the message box. Although I could not test it, this version should work much better. Try it and let me know.

Private Sub FindSSN_Click()
On Error GoTo Error_Routine
 
Dim SSNreplace As String
Dim iReturn As Integer            'captures response from the message box
 
 
 
DoCmd.GoToControl "SSN"           'Assumes your control bound to the SSN field in the table is named SSN
SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")
DoCmd.FindRecord SSNreplace, acAnywhere, , acSearchAll, , acCurrent, True
If SSNreplace = "" Then
    iReturn = MsgBox("No Number Entered. Please Try Again", vbCritical Or vbOKCancel, "Update a SSN")
ElseIf SSNreplace = SSN Then
    iReturn = MsgBox("Match Found For: " & SSNreplace, vbInformation Or vbOKCancel, "Update a SSN")
Else
    iReturn = MsgBox("Sorry, SSN NOT Found. Please Try Again", vbCritical Or vbOKCancel, "Update a SSN")
End If
If iReturn = 1 Then
    Exit Sub
Else
   GoTo Exit_Continue
End If
 
Exit_Continue:
        SSNreplace = "" 'reset (clear) variable for reuse
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
 
End Sub
 

[Proaccesspro]

No, the main problem was that the code had no responses to the message box. Although I could not test it, this version should work much better. Try it and let me know.

Private Sub FindSSN_Click()
On Error GoTo Error_Routine
 
Dim SSNreplace As String
Dim iReturn As Integer            'captures response from the message box
 
 
 
DoCmd.GoToControl "SSN"           'Assumes your control bound to the SSN field in the table is named SSN
SSNreplace = InputBox("Please enter the Member's SSN", "Update a SSN")
DoCmd.FindRecord SSNreplace, acAnywhere, , acSearchAll, , acCurrent, True
If SSNreplace = "" Then
    iReturn = MsgBox("No Number Entered. Please Try Again", vbCritical Or vbOKCancel, "Update a SSN")
ElseIf SSNreplace = SSN Then
    iReturn = MsgBox("Match Found For: " & SSNreplace, vbInformation Or vbOKCancel, "Update a SSN")
Else
    iReturn = MsgBox("Sorry, SSN NOT Found. Please Try Again", vbCritical Or vbOKCancel, "Update a SSN")
End If
If iReturn = 1 Then
    Exit Sub
Else
   GoTo Exit_Continue
End If
 
Exit_Continue:
        SSNreplace = "" 'reset (clear) variable for reuse
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
 
End Sub
 

Same results except the error messages now have a number: 2142 & 2137.

How does iReturn get set to 1???

[Proaccesspro]

Same results except the error messages now have a number: 2142 & 2137.

How does iReturn get set to 1???

When I set thedebugger, the code jumps from this line:

DoCmd.GoToControl "SSN" 'Assumes

to the Error routine. Although I still get the input box.

[puppydogbuddy]

When I set thedebugger, the code jumps from this line:

DoCmd.GoToControl "SSN" 'Assumes

to the Error routine. Although I still get the input box.

1. In your posts in the beginning you indicated that the name of the field for the social security number in your table was PersonID, the later on SSN was used, I am assuming that SSN is the name of the field where the valid social security nos are stored. Is this correct?
2.. the comment: 'Assumes your control bound to the SSN field in the table is named SSN is supposed to be a non-executable comment, but apparently is being commingled with the executable part of the line.

Change this:
DoCmd.GoToControl "SSN" 'Assumes your control bound to the SSN field in the table is named SSN

To this (place comment on a separate line from the executable code):

'Assumes your control bound to the SSN field in the table is named SSN
DoCmd.GoToControl "SSN"

3. the value of 1 for the reponse to the button response is the constant value for the vbOkay button in Access. Each button has a constant value associated to it. This should be explained in more detail in Access on-line help.

Let me know what happens after you make the change discussed in #2 above and recompile.

[Proaccesspro]

1. In your posts in the beginning you indicated that the name of the field for the social security number in your table was PersonID, the later on SSN was used, I am assuming that SSN is the name of the field where the valid social security nos are stored. Is this correct?
2.. the comment: 'Assumes your control bound to the SSN field in the table is named SSN is supposed to be a non-executable comment, but apparently is being commingled with the executable part of the line.

Change this:
DoCmd.GoToControl "SSN" 'Assumes your control bound to the SSN field in the table is named SSN

To this (place comment on a separate line from the executable code):

'Assumes your control bound to the SSN field in the table is named SSN
DoCmd.GoToControl "SSN"

3. the value of 1 for the reponse to the button response is the constant value for the vbOkay button in Access. Each button has a constant value associated to it. This should be explained in more detail in Access on-line help.

Let me know what happens after you make the change discussed in #2 above and recompile.

1. Yes, the field name is SSN (PersonID is label on the form)

2. I deleted the comment

3. It did not work at first....then suddenly it partially worked..(it would tell you if it found a match or not, if you failed to input any data, it would give the error "find action requires argument......" Now NOTHING works..just get the same 2 errors as before!!

[puppydogbuddy]

1. Yes, the field name is SSN (PersonID is label on the form)

2. I deleted the comment

3. It did not work at first....then suddenly it partially worked..(it would tell you if it found a match or not, if you failed to input any data, it would give the error "find action requires argument......" Now NOTHING works..just get the same 2 errors as before!!

Could you zip and email your mdb to me? If you download my vCard from my scripts profile, you will see my email address. Remove any sensitive info before sending. My version of Access is Access 2000. I will be able to fix it right away once I have it in front of me.

[shortyt303]

I am trying to replace text in a field with a blank space and need code to put in a macro to get that done. Any suggestions??

[Proaccesspro]

Could you zip and email your mdb to me? If you download my vCard from my scripts profile, you will see my email address. Remove any sensitive info before sending. My version of Access is Access 2000. I will be able to fix it right away once I have it in front of me.

I will email the DB today....Thanks!