[AccessD] Dying on Code Here

Exactly.  I got it yesterday.  I did not know that I could do the
(k).name - it is not intellisensed.  It works great now. 


Karen S. Nicholson
Programmer Analyst
EG&G Technical Services, Inc.
Pittsburgh, PA
Phone: 412-386-6649
Email: cyx5 at cdc.gov


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ken Ismert
Sent: Thursday, March 16, 2006 4:48 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Dying on Code Here


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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com