Hello,
I have a form and a subform. On my parent form I have a field called “Workdate” and a tab control on which I have put my subforms (on each tab a subform which are all related to the workdate on my parent form). The field “Workdate” is a unique field, so I can’t create double records for one workdate. Everytime I add a new record (workdate) I want to automatically add a few records in the subforms with VBA-code. Now, when I try to add a record I always get the following error message :
Run-time error “3201”
You cannot add or change a record because a related record is required in table “tblWorkdates”.
When I go to my code the error points to the following line in my code : .Update.
The problem is the record in my parent form needs to be saved first before I can add records in the subform. I've tried to put the line "docmd.runcommand accmdsaverecord" in the "Workdat_AfterUpdate" sub procedure but then I get another problem when I try to add a record with a workdate that already exists. Then I get a message form access saying I can't create double records. At that moment I'd like to see my own message (see Form_Error) instead of one from access itself.
So, what code do I need to save the record in my parent form and where do I have to put it. And what code do I need for showing my error message instead of the one from access.
This is my code so far :
Private Sub Workdate_AfterUpdate()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Table1")
Set rs2 = db.OpenRecordset("Table2")
Do While Not rs1.EOF
With rs2
.AddNew
!MyField1 = Forms!MyForm!MyField1
!MyField2 = rs1!MyField2
.Update
End With
rs1.MoveNext
Loop
Forms!MyForm!MySubForm1.Requery
Forms!MyForm!MySubForm2.Requery
Forms!MyForm!MySubForm3.Requery
rs1.Close
rs2.Close
db.Close
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conErrorDoubleRecords = 3022
If DataErr = conErrorDoubleRecords Then
ShowMessage "This workdate already exists in the database."
Response = acDataErrContinue
DoCmd.RunCommand acCmdUndo
Workdate.SetFocus
Else
Response = acDataErrDisplay
End If
End Sub
Anyone who knows how to solve this problem?
Thanks already.
Pascal
I have a form and a subform. On my parent form I have a field called “Workdate” and a tab control on which I have put my subforms (on each tab a subform which are all related to the workdate on my parent form). The field “Workdate” is a unique field, so I can’t create double records for one workdate. Everytime I add a new record (workdate) I want to automatically add a few records in the subforms with VBA-code. Now, when I try to add a record I always get the following error message :
Run-time error “3201”
You cannot add or change a record because a related record is required in table “tblWorkdates”.
When I go to my code the error points to the following line in my code : .Update.
The problem is the record in my parent form needs to be saved first before I can add records in the subform. I've tried to put the line "docmd.runcommand accmdsaverecord" in the "Workdat_AfterUpdate" sub procedure but then I get another problem when I try to add a record with a workdate that already exists. Then I get a message form access saying I can't create double records. At that moment I'd like to see my own message (see Form_Error) instead of one from access itself.
So, what code do I need to save the record in my parent form and where do I have to put it. And what code do I need for showing my error message instead of the one from access.
This is my code so far :
Private Sub Workdate_AfterUpdate()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Table1")
Set rs2 = db.OpenRecordset("Table2")
Do While Not rs1.EOF
With rs2
.AddNew
!MyField1 = Forms!MyForm!MyField1
!MyField2 = rs1!MyField2
.Update
End With
rs1.MoveNext
Loop
Forms!MyForm!MySubForm1.Requery
Forms!MyForm!MySubForm2.Requery
Forms!MyForm!MySubForm3.Requery
rs1.Close
rs2.Close
db.Close
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conErrorDoubleRecords = 3022
If DataErr = conErrorDoubleRecords Then
ShowMessage "This workdate already exists in the database."
Response = acDataErrContinue
DoCmd.RunCommand acCmdUndo
Workdate.SetFocus
Else
Response = acDataErrDisplay
End If
End Sub
Anyone who knows how to solve this problem?
Thanks already.
Pascal
Last edited: