[AccessD] Dying on Code Here

I worked some more on this last night.  This is the database from he**.
There are over 60 tables, all flat, a mess.  The first thing I do is
search the database and find those tables that end in SAF.  That narrows
my problem down to about 19 tables I need to be concerned with.

Then, I extract the field names from those 19 tables and print those.
But I really only want to get the field names of about 20 fields, not
the gazillions that are in each table.

So, I guess I need to build an array, a collection, something, to hold
only 20 field names to update.  I have the code working, but when I call
the field names from the tables, I get every field name.  I just want to
get the field names that may be in the above 20 field names, that of
course, are not listed anywhere except on my form.  The ones on my form
that I am looking for have been tagged with an "N" in the tag control of
the field.

I can not seem to reference just these 20 fields with the "N".  This is
running horribly slow, of course, but at least I am almost there.      


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 strSQLTest As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
        
    For i = 0 To CurrentDb.TableDefs.Count - 1
        If (CurrentDb.TableDefs(i).Name Like "*SAF") Then
            Debug.Print "Table: " & CurrentDb.TableDefs(i).Name
            strTableName = CurrentDb.TableDefs(i).Name
            For j = 0 To CurrentDb.TableDefs(i).Fields.Count - 1
                'strFieldName = CurrentDb.TableDefs(i).Fields(j).Name
                Debug.Print "     Field: " &
CurrentDb.TableDefs(i).Fields(j).Name
                
                For k = 0 To Me.Controls.Count - 1
                    'Debug.Print "         Control Name: " &
Me.Controls(k).Name
                    If (Me.Controls(k).Name =
CurrentDb.TableDefs(i).Fields(j).Name) Then
                    strFieldName = CurrentDb.TableDefs(i).Fields(j).Name
                    Debug.Print strFieldName
                    
                       If Me.Controls(k).Name = strFieldName Then
                        strFieldNameChg =
"Forms![frmRequiredFieldReview]![" & strFieldName & "chg]"
                        strSQLTest = "update " & strTableName & " set "
& strFieldName & " = " & strFieldNameChg
                        Debug.Print strSQLTest
                        Stop
                                      
                    ' DoCmd.RunSQL strsqlTest
                    End If
                    End If
                Next  ' end of k loop
            Next  ' end of j loop
        End If
    Next   ' end of i loop
    Set cnn = Nothing 


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 Bobby Heid
Sent: Thursday, March 16, 2006 7:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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