I'm creating a Word macro that Updates an Access DB Table. I have to redo this several times, so I thought if I could refer to the field name with a variable, I could just write the update statement once and then call it each time I need to.
I'm getting a run time error, "No value given for one or more of the required parameters." I'm assuming this is referencing the variable being used for the field name.
From what I'm reading, it doesn't sound like this is possible, so I'm appealing for a good soul's help before I abandon this approach. Thanks for any help anyone might be able to provide.
"FieldNm" is the variable I'm using.
Code is as follows:
Dim Cn As ADODB.Connection '* Connection String
Dim oCm As ADODB.Command '* Command Object
Dim MyProjectIDNum As Long
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & MyDataPath & "';Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn
oCm.CommandText = "UPDATE [My Projects] SET '" & FieldNm & "' = ('" & UpdateData & "') WHERE [ID]= '" & MyProjectIDNum & "'"
oCm.Execute
If Cn.State <> adStateClosed Then
Cn.Close
End If
Application.StatusBar = False
If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing