Copy Data from one table to another (1 Viewer)

[access2010]

Could I please receive assistance in copying the data in table => Investments01_Appendix to the table => Investments01_tbl,
ONLY if the same field name in table => Investments01_tbl is empty.

I do not want to overwrite any existing data in the table => Investments01_tbl, that is in our Access 2003 database.

Your assistance will be appreciated.
Nicole

 

[CJ_London]

on my phone so cannot view your db. I assume empty means either null or a zls so sounds like you need an iif statement for each field

update destable
Set destable.fld1=iif(nz(destable.fld1,””)=“”,sourcetable.fld1,destable.fld1), destable.fld2=…..
From destable inner join sourcetable on destable.pk=sourcetable.pk

 

[Eugene-LS]

ONLY if the same field name in table => Investments01_tbl is empty.

Private Sub UpdateRecordsIfIsEmpty()
'   Copying the data from table "Investments01_Appendix" to the table "Investments01_tbl", _
    ONLY if the same field name in table "Investments01_tbl" is empty.
'--------------------------------------------------------------------------------------------------
Dim rsSRS As DAO.Recordset
Dim rsDST As DAO.Recordset
Dim objField As DAO.Field
Dim sVal$, lRecID&, lCountRecords&, lCountUpdates&, dTimer As Date
'--------------------------------------------------------------------------------------------------
On Error GoTo UpdateRecordsIfIsEmpty_Err

    dTimer = Now
    sVal = "Select * From Investments01_Appendix"
    Set rsSRS = CurrentDb.OpenRecordset(sVal, dbOpenSnapshot)

    With rsSRS
        Do Until .EOF = True
            lRecID = !InvestmentID
            sVal = "Select * From Investments01_tbl WHERE Investmentl_ID = " & lRecID
            Set rsDST = CurrentDb.OpenRecordset(sVal, dbOpenDynaset)
            If rsDST.RecordCount > 0 Then
                For Each objField In .Fields
                    If IsNull(objField.Value) = False Then
                        If IsFieldPresent(rsDST, objField.Name) = True Then
                            'if the same field name in table is empty:
                            If IsNull(rsDST(objField.Name).Value) Then
                                rsDST.Edit
                                    rsDST(objField.Name).Value = objField.Value
                                rsDST.Update
                                lCountUpdates = lCountUpdates + 1
                            End If
                        End If
                    End If
                Next
            End If
            rsDST.Close
            lCountRecords = lCountRecords + 1
            .MoveNext
        Loop
    End With

    sVal = "Processed records: " & lCountRecords & " - Updated: " & lCountUpdates & _
        " Values.  Duration: " & Format(Now - dTimer, "hh:nn:ss")
    Debug.Print sVal
   
UpdateRecordsIfIsEmpty_End:
    On Error Resume Next
    Set objField = Nothing
    rsSRS.Close
    Set rsSRS = Nothing
    rsDST.Close
    Set rsDST = Nothing
   
    Err.Clear
    Exit Sub

UpdateRecordsIfIsEmpty_Err:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
    "in Sub: UpdateRecordsIfIsEmpty in module: [Your Module Name]", vbCritical, "Error in Application"
    Resume UpdateRecordsIfIsEmpty_End
End Sub

Private Function IsFieldPresent(rs As DAO.Recordset, sFieldName As String) As Boolean
Dim objField As Field
On Error GoTo IsFieldPresent_Err
    Set objField = rs.Fields(sFieldName)
    IsFieldPresent = True

IsFieldPresent_Bye:
    Set objField = Nothing
    Exit Function

IsFieldPresent_Err:
    Err.Clear
    Resume IsFieldPresent_Bye
End Function

 

[ebs17]

Maybe a query like the following will help:

UPDATE
   Investments01_tbl AS T
      INNER JOIN Investments01_Appendix AS S
      ON T.Investmentl_ID = S.InvestmentID
SET
   T.Symbol_Stock = IIF(T.Symbol_Stock IS NULL, S.Symbol_Stock, T.Symbol_Stock),
   T.BMO_Intel = IIF(T.BMO_Intel IS NULL, S.BMO_Intel, T.BMO_Intel),
   T.CFRA_Advc = IIF(T.CFRA_Advc IS NULL, S.CFRA_Advc, T.CFRA_Advc)

The missing fields for the update would have to be added accordingly.

 

[JHB]

You are asking for BIG trouble, when you use Lookup fields (Combo box and List view) at table level.

 

[access2010]

Thank you, Eugene-LS.

Your suggested code works.

We do appreciate your assistance.
Crystal