Use findfirst to get a value 1

[lamago]

I am trying to copy data from one table to another, but the tables have different variable names. So I have a table named LayoutMatrix that has the names from one table that correspond to the names of the other table. Ie. ID, OldName, GlobalFieldName. I cant append it using SQL, but sometimes some records dont paste due to conversion, so im trying to use Recordsets to trap the errors. I have gotten this far. Is there a way to find the record where OldName="value" and then retrieve what the corresponding GlobalFieldName is?

For Each tdf In db.TableDefs
    'If its a CAHSEE results table then assign the layout from the description
    If Left(tdf.Name, 6) = "CAHSEE" Or Left(tdf.Name, 4) = "HSEE" Then
        strlayout = Mid(tdf.Properties("Description"), 7, 4)
        Set rsnew = db.OpenRecordset("PUSDRESULTSTRIAL") 'To table
        Set rsold = db.OpenRecordset(tdf.Name)  'From table
        rsold.MoveFirst ' go to the first record in the old table
        j = 1 'Begin Count of records
        While Not rsold.EOF
            rsnew.AddNew
            For Each fld In rsold.Fields
                rst.FindFirst ("[" & strlayout & "]= '" & fld.Name & "'")
                strname = rst.Fields("GlobalFieldName")
                [b]rsnew.Fields(strname) = fld[/b]
            Next fld
            rsnew.Update
            j = j + 1
            rsold.MoveNext
        Wend
        MsgBox j & " record(s) have been added", vbExclamation + vbOKOnly
    End If
Next tdf

 

[MajP]

There are a few moving pieces here, but at first glance it looks correct. Can you put a message box or debug.print in to verify the variables are what you want them to be?

   [b]debug.print strLayout [/b]     
   While Not rsold.EOF
            rsnew.AddNew
            For Each fld In rsold.Fields
                rst.FindFirst ("[" & strlayout & "]= '" & fld.Name & "'")
                strname = rst.Fields("GlobalFieldName")
            [b] debug.print strName   
             debug.print fld.value [/b]
             rsnew.Fields(strname) = fld.value
            Next fld
            rsnew.Update
            j = j + 1
            rsold.MoveNext
        Wend

 

[stix4t2]

The error handler can be used to do the lookup
I supplied some snippets

For assignment

     on error goto err_fieldNotFound
     rsnew.Fields(strname) = fld
     on error goto err_fieldGeneral

At end of routine, I am assuming sub, but change if function

     exit sub
err_fieldNotFound:
     strname = dlookup( "newName", "tblMatrix", "[oldName] = '" & strname & "'"
     resume 
err_fieldGeneral:
     msgbox "error " & Error$ & " " & errnumbe
r

I guess I was too lazy to look at your example. Please substitute your field and table names so the DLookup works in your database.

To expain, the end sub exits after the sub is finished and doesnt run into the error code. The err_fieldNotFound will lookup the new name and use it. The new name is used. Resume will send the code back up to the assignment with the new field name.

 

[lamago]

Thanks! I used the debug.prints and what is happening is that some fields dont correspond so I have to put some kind of code so that it skips fields that .findfirst doest find, but how can I get it to go to the next fld?

stix42
Im not sure I understand the error handler, I understand how it works, but there would not always be an error after strname = rst.Fields, or am I supposed to remove that?

For Each fld In rsold.Fields
  Debug.Print fld.Name
  rst.FindFirst ("[" & strlayout & "]= '" & fld.Name & "'")
  [b]If rst.NoMatch Then
      resume next fld
  End If[/b]
  strname = rst.Fields("GlobalFieldName")
  Debug.Print strname
  Debug.Print fld.Value
  rsnew.Fields(strname) =fld
Next fld

 

[MajP]

For Each fld In rsold.Fields
Debug.Print fld.Name
rst.FindFirst ("[" & strlayout & "]= '" & fld.Name & "'")
If rst.NoMatch Then
goto NoFldLabel
End If
strname = rst.Fields("GlobalFieldName")
Debug.Print strname
Debug.Print fld.Value
rsnew.Fields(strname) =fld
NoFldLabel:

Next fld

 

[lamago]

Beutiful. Thanks for all yout help.

 

[lamago]

As a followup

stix4t2 Why is it that you have an on error on top and on the bottom of that code, what does that do? And does anyone know what

on error goto 0 does?

 

[MajP]

The first check for a field not found error, the second catches other errors. goto 0 cancels the error handler resetting the value of the Err object, and trapping subsequent errors.

 

[stix4t2]

Only the fields that don't match will throw the error. So if the field works, then nothing is done. Otherwise it does the lookup and uses the matrix table for the substitution.