[AccessD] Dying on Code Here

Ken Ismert KIsmert at TexasSystems.com
Thu Mar 16 15:48:16 CST 2006


Karen, 

I'd be tempted to try something like this: 

<code>

    . . .

    Dim rTDef As DAO.TableDef
    Dim rFld As DAO.Field

    On Error GoTo HandleErr

    For i = 0 To CurrentDb.TableDefs.Count - 1
        If (CurrentDb.TableDefs(i).Name Like "*SAF") Then

            Set rTDef = CurrentDb.TableDefs(i)
            Debug.Print "Table: " & rTDef.Name

            ' Init SQL string
            strSQLTest = "" 

            For k = 0 To Me.Controls.Count - 1 
                
                On Error Resume Next 

                ' Try to get matching table field
                Set rFld = rTDef.Fields(Me.Controls(k).Name)
                
                If Err.Number <> 0 Then 
                    ' Table has field with matching name
                    Debug.Print rFld.Name
                    strFieldNameChg = "Forms![frmRequiredFieldReview]!["
& rFld.Name & "chg]"
                    ' Build set list
                    strSQLTest = strSQLTest & IIf(strSQLTest > " ", ",
", "") & rFld.Name & " = " & strFieldNameChg
                    Debug.Print strSQLTest
                Else
                    ' Table has no field with matching name
                    Debug.Print "Missing Field: " & Me.Controls(k).Name
& ";  Table: " & rTDef.Name
                    Err.Clear
                End If

            Next k

            On Error GoTo HandleErr

            ' Add Update statement prefix, suffix
		strSQLTest = "update " & rTDef.Name & " set " &
strSQLTest & ";"
            Debug.Print strSQLTest

            Stop
            ' DoCmd.RunSQL strsqlTest

        End If

    Next i

    Exit Sub

HandleErr:
    MsgBox Err.Number & " - " & Err.Description
    Exit Sub
End Sub

</code>

I put in a simple error handler. I used On Error Resume Next so I could
grab the matching field name using the control name. If no error, I add
the field to the set list. I tack on the Update prefix just before
executing. 

This is air code, so double-check before running.

-Ken




More information about the AccessD mailing list