VBA Excel data Import into Subform- extra code to update the FK field in child table with ParentID

[poachui]

Thanks June,


I think id keep the access table headers for neatness and just import the excel data in the same order.

There are only two conditions extra to the code I have are.

1. adding new records:
If no records exist with a rs!projectId=Me.ID then
excl import and
make projectID field = me.ID


2.updating records from previous import
If records already exist with a rs!projectId=Me.ID then

replace those records (where rs!projectID=Me.ID) with excel import

(possible delete then loop to first add new argument?)

hope that makes sense,
thanks

[June7]

I just realized the spreadsheet has a ToProject field so I am a little confused by that. If this field does not identify the associated project, what is it for?

Consider revised code:

For i = 2 To xlRange.Rows.Count
    rs.FindFirst "ProjectID=" & Me.ID & " And Years=" & xlRange.Cells(i, 1)
    If rs.NoMatch Then
        rs.AddNew
        rs!projectID = Me.ID
    Else
        rs.Edit
    End If
    For j = 1 To xlRange.Columns.Count
        rs.Fields(Replace(xlRange.Cells(1, j), " ", "")) = xlRange.Cells(i, j)
    Next j
    rs.Update
Next i

No Delete action.

However, I am having trouble with the FindFirst method, never been an issue for me before. http://msdn.microsoft.com/en-us/libr...ffice.15).aspx
Change the code to:
Set rs = CurrentDb.OpenRecordset("tblWCCVintage", dbOpenDynaset)

[poachui]

Hi June, away from internet for weekend, sorry for the delay.

The 'ToProject' field is just another values field. There isn't actually an identifying field (e.g. project name) as each unique project has its own excel sheet. The unique identifier will be the ProjectID field when its added upon import.

The code works great except for couple of small niggles.

I get a error because a rs.update follows and r.s edit in the 'i=' rows loop.

And:
By using the findfirst method all records are imported the first time, but edits are only made to matching records.
So if the record count changes in excel, the extra records are not added/subtracted in access.

I can only think to solve this by using some of the code from my original post which I realise now was aimed at solving dynamic record count. So...
1) nesting a rs.RecordCount into rs.Find so that IF the rs.recordcount (for records with rs!project.id=Me.ID) < xlRange.Rows.Count - 1, then add new records (until records count matches)

Or, maybe easier?

2a) including a delete all matching records command before running the import loops,
2b) adding a delete all matching records button which the user should do before each import.