Replace add or delete

[MvP14]

Hello,

I will be forever greatful to anyone who can help me with this. Here's my problem:

In a form based on a query, I want to modify al the records by using a command button.

Here is what I have so far: When one clicks the command button, a form with three controls opens: Field (combobox), Old and New (textboxes).

In the combo box, I select the field name to be edited. In the textbox old, I put the text that has to be the deleted (if I leave New blank) or replaced with the text in New. In the textbox New, I put the text that has to replace the text in Old, or that has to be added to the field if Old is left empty.

I've been struggling over this for hours (I hardly know anything about VBA), and this is what I have so far:

Private Sub Change_Click()

Dim strSpace As String
Dim strField As String
Dim strOld As String
Dim strNew As String

strField = Me.Field.Value

If IsNull(Me.Old.Value) Then

strSpace = " "
strNew = Me.New.Value

DoCmd.RunSQL "UPDATE Query1 SET strField = strNew & strSpatie & strField ;"

Else

If IsNull (Me.New.Value) Then

Else

End If

End If


DoCmd.Close

f.Refresh

End Sub

But Even the update query doesn't work, for it asks to enter the parameter values. Can anyone be my light at the end of the tunnel? Thanks in advance!

 

[Mile-O]

DoCmd.RunSQL "UPDATE Query1 SET strField = strNew & strSpatie & strField ;"

You have included your variables within a string so therefore the SQL statement actually reads as you have written it. I'm sure you don't actually have fields in your database called strField.

"UPDATE Query1 SET [" & strField & "] = " & strNew & strSpatie & strField & ";"

 

[MvP14]

It is getting better but the problem now is that my computer thinks it has to add the value of strField and not the value which is already entered in the [strField] of the record to be changed.

What is going wrong?

As far as the other part of the If Else statement, I have been trying to experiment with the replace function and with the select case method, but none seems to work.

Any suggestions?

Thank you!

 

[MvP14]

Is what I'trying to do even possible?

 

[Mile-O]

Try this:

Private Sub Change_Click()

    If IsNull(Me.Field) Then
        MsgBox "No field selected.", vbExclamation, "Example"
        Exit Sub
    End If

    If IsNull(Me.Old) And IsNull(Me.NewRecord) Then
        MsgBox "No information entered.", vbExclamation, "Example"
        Exit Sub
    End If

    If IsNull(Me.Old) Then
        DoCmd.RunSQL "DELETE * FROM Query1 WHERE " & Me.Field & " = """ & Me.Old & """;"
    ElseIf IsNull(Me.New) Then
        ' you'll probably have to use DAO or ADO t insert a record unless an append query can take this data from another table.
    Else
        DoCmd.RunSQL "UPDATE Query1 SET " & Me.Field & " = " & Me.New & " WHERE " & Me.Field & " = """ & Me.Old & """;"
    End If


    DoCmd.Close

    f.Refresh

End Sub

 

[MvP14]

Thank you for the suggestion.

The message boxes work fine. I have changed the code to the following:

Private Sub Change_Click()

If IsNull(Me.Field) Then
MsgBox "Er is geen veld geselecteerd.", vbExclamation, "Example"
Exit Sub
End If

If IsNull(Me.Old) And IsNull(Me.New) Then
MsgBox "Er is geen informatie ingegeven.", vbExclamation, "Example"
Exit Sub
End If

If IsNull(Me.New) Then
'DoCmd.RunSQL "DELETE * FROM Query1 WHERE " & Me.Field & " = """ & Me.Old & """;"
Else

If IsNull(Me.Old) Then
DoCmd.RunSQL "UPDATE Query1 SET " & Me.Field & " = " & Me.New & " & " & Me.Field & " ;"

Else
DoCmd.RunSQL "UPDATE Query1 SET " & Me.Field & " = " & Me.New & " WHERE " & Me.Field & " = """ & Me.Old & """;"
End If

End If


DoCmd.Close


End Sub

I have 'disabled' the delete code, because I'm afraid it will delete the entire record and not just the entered text from the selected field. The update query on Me.Old is null and Me.New isn't kind of works. Problem is that a prompt appears to reenter the parameter value. The final update query (where the text in Old should be replaced by the text in new) seems to be error free, but it doesn't do what it should, namely replace the text in the field (e.g. field is Keywords, query contains all records with inter alia the keyword 'America'. The keyword America should be replaced by the keyword 'USA' in all records, however the other keywords remain the same. Thus in one record the keywords were America/ Government/ Shield, and now should be USA/ Government/ Shield).

Thanks in advance for any more comments!

 

[MvP14]

I was able to solve the problem with the update query for the situation where the textbox Old is blank by using the following code:

DoCmd.RunSQL "UPDATE Query1 SET " & Me.Field & " = '" & Me.New & "' & '" & " & Me.Field & " ;"

So one of the three options is working now, the situations where part of the field has to be deleted or replaced.

I will inform you if I figure it out. Of course all help is still very much appreciated!

 

[MvP14]

I've solved my problem by no longer trying to do it all with one pop up form. When one clicks the button to edit the selected records, he gets to choose between adding text to a field of the records and removing/replacing text from it. If he chooses the first option, a little form pops up with one combobox and one textbox. If he chooses the second option, the built in replace dialog box opens.

The solution is less efficient than what I had hoped, but it does what I wanted it to do.

Thanks for the various replies!