Nicholson, Karen
cyx5 at cdc.gov
Fri Mar 17 05:37:10 CST 2006
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