Consulting

Results 1 to 3 of 3

Thread: Updating a DB Record with Word Macro

  1. #1
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    120
    Location

    Updating a DB Record with Word Macro

    Not sure if this is an Access or Word question, but I'm trying to updated a record in Access from a Word macro. The Update Command statement uses a variable for the field name, but I think I'm not doing some right or maybe you can't use a variable for a field name. I'm getting the "No value given for one or more required parameters" error. I've been over this several times to make sure the Table Name and Field Names are spelled correctly, etc., and I can't figure it out:

    strUpdateText = "UPDATE [My Preferences] SET ['" & Pref_Field_Nm & "] = ('" & PrefData & "') WHERE ([User Co Nm] = '" & UserCoNm & "')"
    Is there a syntax problem? Can you not use a variable for a Field Name?

    Thanks.

  2. #2
    VBAX Contributor
    Joined
    Aug 2012
    Posts
    120
    Location
    This is the Database execution part:

        Dim Cn As ADODB.Connection '* Connection String
        Dim oCm As ADODB.Command '* Command Object
        Dim NumID 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 = strUpdateText
        oCm.Execute
        If Cn.State <> adStateClosed Then
        Cn.Close
        End If
    

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Mavila,

    I write to Excel or Access using as statement like this:

    UPDATE [Sheet1$] SET [FullNameX]='Joe',[AgeX]='23',[GenderX]='Male' WHERE [SSNX]='1234'

    And develop that string using:

    Function fcnGetFFDataUpdateStrSQL(oDoc As Document, strTableName As String, bExtractFileName As Boolean, strKeyField As String, strKeyFieldVal As String, _
                                Optional bExcel As Boolean = False) As String
    Dim oFF_FormData As FormField
    Dim strSet As String
    Dim lngFFIndex As Long
    Dim strFF_Data As String
      'Initialize SQL statement variable values.
      strSet = vbNullString
      'Extract the document FormField control data.
      For lngFFIndex = 1 To oDoc.FormFields.Count
        Set oFF_FormData = oDoc.FormFields(lngFFIndex)
        If Not oFF_FormData.Name = strKeyField Then
          strFF_Data = oFF_FormData.Result
          strFF_Data = Replace(strFF_Data, Chr(39), Chr(39) + Chr(39))
          strSet = strSet & "[" & oFF_FormData.Name & "]='" & strFF_Data & "',"
        End If
      Next lngFFIndex
      If bExtractFileName Then
        strSet = strSet & "[Record Name]='" & oDoc.Name & "'"
      Else
        strSet = Left(strSet, Len(strSet) - 1)
      End If
      fcnGetFFDataUpdateStrSQL = "UPDATE [" & strTableName & "$] SET " & strSet & " WHERE [" & strKeyField & "]='" & strKeyFieldVal & "'"
    CleanUp:
      Set oFF_FormData = Nothing
    lbl_Exit:
      Exit Function
    End Function
    Could it be the parens e.g. "(" and ")"
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •