Full Version: Form and SubForm adding new records vba
UtterAccess Forums > Microsoft® Access > Access Forms
frankosull
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
.AddNew
!JobCode = strNewJobCode
!JobDescription = ""
!JobType_ID = Me.tbJobType_ID.Value
!JobDescription2 = ""
!Redundant = False
.Update
End With
rstJobH.Close
Set rstJobH = Nothing
'create new job detail records based on
cpetermann
Frank,
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.
thanks,
Cynthia
frankosull
Hi Cynthia,
Thanks for coming back.
All have a go - there is quite a bit of data I need to get rid of first...
frankosull
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"
Alternatively
The form name is "frmJobDetails"
cpetermann
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.
HTH,
Cynthia
frankosull
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.