[AccessD] Dying on Code Here

Karen,

It looks like the problem might be that if you are using the exact code
below that you are using a 'set' statement on each field.  So your SQL might
look something like:

UPDATE sometable
   SET somefield="somevalue"

You want to have something like:
UPDATE sometable
   SET somefield1='somevalue1' 
      somefield2='somevalue2'
      somefield3='somevalue3'
	   ...	   
	somefieldn='somevaluen';

So, if I understand the issue correctly, you want to build the 

	UPDATE sometable
	   SET

part first.  Then loop through all of the fields and append the

	somefield='somevalue' 	(assuming text values here)

strings to the end of the SQL string.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nicholson, Karen
Sent: Wednesday, March 15, 2006 2:47 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Dying on Code Here


Help Me!
I can not get this stupid code to work.

Private Sub cmdRunImport_Click()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strTableName As String
Dim strFieldName As String
Dim srfFieldNameChg As String
Dim ctl As Control
Dim ctlName As String
I am trying to capture the field names and values of over 30 fields on a
form, pass those names and values into a procedure to then *run around
my database* and update every table that contains the field name with
the captured value.  This database is a total un-normalized mess and the
option of normalizing does not happen to be an option at this time.  I
got the code to work for just one field.  It is fine.  I then tried to
put a value on the "tag" for each required field on the form, but I
can't seem to call it from within this code.  Any ideas on how I can
store the required fields and make this thing pump through the records?

Aughghghg!!!!!



Private Sub cmdRunImport_Click()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strTableName As String
Dim strFieldName As String
Dim srfFieldNameChg As String
Dim ctl As Control
Dim ctlName As String
Dim i As Integer
Dim j As Integer

    For i = 0 To CurrentDb.TableDefs.Count - 1
        For j = 0 To CurrentDb.TableDefs(i).Fields.Count - 1
               If (CurrentDb.TableDefs(i).Name Like "*SAF") Then
               If (CurrentDb.TableDefs(i).Fields(j).Name =
("ManufacturerCode")) Then
               ===>> 'Or (CurrentDb.TableDefs(i).Fields(j).Name =
("sitecode")) Then
               Debug.Print "Field: " &
CurrentDb.TableDefs(i).Fields(j).Name
               Debug.Print "Table: " & CurrentDb.TableDefs(i).Name
               End If
               End If
               
        Next
    Next

                            strTableName = CurrentDb.TableDefs(i).Name
                            strFieldName =
CurrentDb.TableDefs(i).Fields(j).Name
                            strFieldNameChg =
"Forms![frmRequiredFieldsReview]![" & strFieldName & "chg]"
		    Debug.Print "    Field: " &
CurrentDb.TableDefs(i).Fields(j).Name
                            Debug.Print strTableName
                            Debug.Print strFieldName
                            Dim sqlTest As String
                                            sqlTest = "update " &
strTableName & " set " & strFieldName & " = " & strFieldNameChg
                                            DoCmd.RunSQL sqlTest
Set cnn = Nothing
End Sub



This works just fine.  But I have 26 fields to go through.  It will not
honor that *tag* value.  I need it to loop through my 26 or so field
names and print out each one above, line by line so I can insert it into
SQL code for my update statement.  Any ideas?



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


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