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

[poachui]

Hi All,

would be grateful for some help, i'm just starting to code VBA.

I am importing an basic excel range into access using VBA code I found (code atached in .txt). I have this working OK, except my FK field remains empty (makes sense as its not in the excel import).

The button is on a Parent form 'frmProjects' and the import data is to display in the child sub form 'subfrmWCCVintages'

The table 'tblProjects' (PrimaryKey:ID) is in a 1-Many with 'tblWCCVintages' (ForeignKey: ProjectID).

Form:
'frmProjects'
(Master:ID)



Sub form
'subfrmWCCVintages'
(Child ID: ProjectID)

The data imports OK to 'tblWCCVintages', but the ProjectID field is blank-so of course it doesn't show in 'subfrmWCCvintages'

I would like to add some code, so that after the import, the Foreign Key ProjectID field populates using the 'ParentID' of the current open Main 'frmProject'

In my amateur way, iv tried to outline what i think the code might look like (still including the dim/set from full code):

Private Sub Command407_Click()
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblWCCVintage")


 If IsNewRS = True And rs.EOF = True Then             (not sure this is needed)
 If rs.Fields FKID= Null                                         (should this be instead by:  & "WHERE ProjectID is Null")
    rs.Update Set FKID=" Me.PKID                          (Is this the right referral to the open form?) 

Me.subformContainerName.Requery


EndSub

Then where in my main code should I put it?

Thanks for your patience

P.S in the time it took me to write this explanation, I did it with an add on from June7 in this post:
https://www.accessforums.net/program...orm-18795.html

The code looks like this:

CurrentDb.Execute "UPDATE tblWCCVintage SET ProjectID=" & Me.ID & " WHERE ProjectID Is Null"
Me.subfrmWCCVintage.Requery

But can I alter it to better reflect the initial Dim/Set??
This is a learning experience for me, so all comments welcome.

[June7]

Not sure what you mean by 'better reflect the initial Dim/Set'.

You could populate the ProjectID in the import loop and then the UPDATE action would not be needed.

rs.AddNew
rs!ProjectID = Me.ID

[poachui]

Ah, sorry for ambiguity. I only meant that instead of writing the whole string out (which works perfect), how could I make use of the Dim/Set predefinitions-just to make the code neater.

Infact your answer was perfect.

As the line 'rs.AddNew' already existed in the import loop. I only needed to add the 'rs!ProjectID = Me.ID' line so it looks like this:

For i = 2 To xlRange.Rows.Count
If rs.RecordCount < xlRange.Rows.Count - 1 And rs.EOF = True Then
rs.AddNew
rs!ProjectID = Me.ID
IsNewRS = True
End If

Unfortunately now the subform and table both have to be refreshed to show the new values.

I tried a Me.Refresh and Me.Requery (and Me.subfrmWCCVintage.Requery) to the end of the code but only the subform updates.

The source table still needs to be manually refreshed.
This might not be a problem as the user wont be looking at the table, but i would still like to know how to refresh the table.

I figure it has something to do with the focus being on the form...

[poachui]

A bit more troublesome is that, for each import event, the entire record set is overwritten each time, no matter which project I am importing to.

This makes sense as I must now account for the ProjectID when deciding what is new data, or an edit of the previous recordset.

I thought to use the line

'rs!ProjectID <> Me.ID' as a extra condition to be met for deciding whether to add new data.

I put the first line in here as an extra condition:
For i = 2 To xlRange.Rows.Count
If rs.RecordCount < xlRange.Rows.Count - 1 And rs.EOF = True And rs!ProjectID <> Me.ID Then
rs.AddNew
rs!ProjectID = Me.ID
IsNewRS = True



but it returns an error 'no records' :/

I then thought to add 'rs!ProjectID = Me.ID' as a nested condition for the record count (somehow?) so that a new records are added when none can be found with the current projectID
Something like:
For i = 2 To xlRange.Rows.Count
If rs.RecordCount (rs!ProjectID = Me.ID) < xlRange.Rows.Count - 1 And rs.EOF = True Then
rs.AddNew
rs!ProjectID = Me.ID
IsNewRS = True
End If

Thanks for your time on this, really appreciate it!

I do think being able to import an exel range into a 1-M relationship table has got to be useful to others as well.

[June7]

Records should not be overwritten, the code is adding new records. If you are just adding records, shouldn't matter if the recordset count is different from the Excel row count. If you only need to add record if the ProjectID is not already in table, a DLookup on the table can determine that.

Not sure what you mean by needing to refresh the table. The records are committed to table with the rs.Update code. Requery form to display new records.

I also don't understand the need for the second rs.Update and the rs.Delete. Never seen that.

Also never seen Jump:. What is this for?

Could be the code is more complicated than need be. I need to know more about the rules for import.

[poachui]

Yes, I think they're getting deleted later on. But that's OK if you happen to change your excel data and want to overwrite your previous import (which will happen in my case i'm sure).
I would just like it to:
1)add new data to a child table, and then write in the Foreign key as defined by the Parent form-to establish a relationship and allow the data to display in the subform (based on the child table).
2)If re-importing, then Edit/overwrite only data with the assigned projectID value specified in the form

Thanks to your earlier info, Iv succeed in writing in the foreign key during an import.

Now I just need it to use the FK:ProjectID as condition when choosing which records to overwrite/edit if the import is repeated.


FOr the rest...
Hmm..for The jump i think it just seems to skip the columns edit code (if the initial 'IsNewRs' condition is met) and so define the columns (titles?) directly from the excel range.

The refresh- I meant I had to click the refresh button on the Ribbon in order for the data to appear in the table-even though its there all along, not a problem was just curious why the rs.requery/rs.refresh in the code only made the subfrm data appear automatically.

This seems like it should be a really common task, '...Import some excel range to your child table, and its assigned a key from the main form so it can appear in the subform. If you re-import, it changes only the recordset with the parent FK.'

If i figure this out, I will definitely be keeping the code somewhere safe as it wasn't easy even finding that initial script.
Thanks again June.

[June7]

Why have the table open at all?

Why is Excel involved?

Import may be common for some (I rarely face this, usually go the other way).

Adding records is simple. Overwriting existing gets more complicated. I would have separate code.

Simplest might be if the table can be completely purged and all records reimported. Or if the spreadsheet structure is simple enough, set a link.

Sample of spreadsheet data might be helpful.

[poachui]

Oh sorry, The Table wont be open at all, I only was using it to check values were being imported correctly.

Excel is needed as there are some lengthy calculations which create the values being imported. I will later want to export initial the variables into the excel-but thats another story.

Thought about the link route, but the spreadsheet structure will likely change as its updated, and each set of imported excel records will be from a different spreadsheet. I thought it easiest for the user to just have a button, then pick the sheet. As it stands, they will need to put the excel range of use into a new tab unti I figure out how to identify the excel column headings-again another story. But got to start somewhere.

[June7]

How can build import code if the spreadsheet structure is dynamic?

[poachui]

I think by identifying excel column headings = access column headings. I've seen an example somewhere. But we will probably stick with copying into a clean tab, and linking within excel.
The problem with linking is I need my data to be in a relationship with other tables, not a new linked table that stands alone. .

[June7]

Why do you need relationships established? Many experienced developers don't even bother setting relationships. I seldom do.

[poachui]

I wouldnt know where to start linking tables without sql.

For now, i just need to import this data. I'll keep messing around with it, there's usually a way.

But thanks you'v been a great help getting me started with the import code.
Much appreciated.

[June7]

rs!ProjectID <> Me.ID condition should work. I should have thought of it instead DLookup.

If you want to provide spreadsheet for testing, I could probably refine the code.

[poachui]

Hi, Thanks, that would be really helpful. I've created a basic mockup access database for your convenience with table/form names the same, and some dummy data.

If you open frmProject, you will see the subform, and the Import button.

VBATestImport.zip

There are two projects, and im trying to get one excel import each. Currently the second import overwrites all fields except the FK, so the data changes, but remains tied to project 1.

Ryan

[June7]

Here's a start. This code will just do straight import.

For i = 2 To xlRange.Rows.Count
    rs.AddNew
    rs!ProjectID = Me.ID
    For j = 1 To xlRange.Columns.Count
        rs.Fields(Replace(xlRange.Cells(1, j), " ", "")) = xlRange.Cells(i, j)
    Next j
    rs.Update
Next i

It assumes the table field names will be found in the Excel sheet. Had to account for difference of a space in Excel column headers. If you want to assume the table field names and Excel headers will be in the same order, regardless of names, and always the same number of fields in each, could use:
rs.Fields(j) = xlRange.Cells(i, j)

Something has to be consistent.

Now what adjustments do you want? What conditions need to be considered?