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):
Then where in my main code should I put it?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
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:
But can I alter it to better reflect the initial Dim/Set??Code:CurrentDb.Execute "UPDATE tblWCCVintage SET ProjectID=" & Me.ID & " WHERE ProjectID Is Null" Me.subfrmWCCVintage.Requery
This is a learning experience for me, so all comments welcome.