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 & strSpace & strField & ";"
Else
If IsNull (Me.New.Value) Then
Else
End If
End If
DoCmd.Close
[Forms]![Selection].Refresh
End Sub
But Even the update query doesn't work (operator missing error + existing value of record should be added at the end and not the value of the string strField), for it asks to enter the parameter values. Can anyone be my light at the end of the tunnel? Thanks in advance!
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 & strSpace & strField & ";"
Else
If IsNull (Me.New.Value) Then
Else
End If
End If
DoCmd.Close
[Forms]![Selection].Refresh
End Sub
But Even the update query doesn't work (operator missing error + existing value of record should be added at the end and not the value of the string strField), for it asks to enter the parameter values. Can anyone be my light at the end of the tunnel? Thanks in advance!