Full Version: Form and SubForm adding new records vba
UtterAccess Forums > Microsoft® Access > Access Forms
Hi, I am trying to create new records in a parent table and its child table within a form and sub form.
On particular I have a command button which when clicked will create new parent and child records based on the current record selected.
The technique I am using is to open a recordset of the parent table addnew and update and then close the recordset.
I am getting error messages based on duplicate keys when I do anything with the form - I am guessing it's because I have created the record in the table and it is also in the form linked to the table.
Below is an extract of the code behind the command button.
Apologies if this is not well explained as I confess to being confused about this one.
Any hints or pointers greatly appreciated
'create new job header record
rstJobH.Open "tblJobHeader", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
With rstJobH
!JobCode = strNewJobCode
!JobDescription = ""
!JobType_ID = Me.tbJobType_ID.Value
!JobDescription2 = ""
!Redundant = False
End With
Set rstJobH = Nothing
'create new job detail records based on
Could you copy your db, remove any sensitive data, leaving enough "dummy" data
for us to see the problem, compact & repair then zip & attach?
Than explain which form you want us to look at.
Hi Cynthia,
Thanks for coming back.
All have a go - there is quite a bit of data I need to get rid of first...
Zipped file attached
The form is
from the bespoke menu "Job" then "Job Detail" the command button I have the problem with is captioned "Create a new job based on old"
The form name is "frmJobDetails"
Try deleting relationships between the three tables tblJobType, tblJobHeader & tblJobDetail
change primary key in
tblJobtTpe to JobTypeID
tblJobHeader to JobID
tblJobDetail to JobDetailIDPK
After doing this, when I highlighted the JobCode I was able to create a duplicate job with a new JobID.
Thanks Cynthia I'll check out your solution. Will need to check if it has any knock on effect (can't remember why I chose those primary keys).
ill let you know outcome.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.