Run-time error “3201” (1 Viewer)

pascal

isolation
Local time
Today, 07:03
Joined
Feb 21, 2002
Messages
62
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
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:03
Joined
Feb 19, 2002
Messages
42,981
By moving the focus from the parent form to the subform, you cause Access to save the mainform record. It is not necessary to do it again. It sounds like you are not populating the foreign key when you add the new record.
 

pascal

isolation
Local time
Today, 07:03
Joined
Feb 21, 2002
Messages
62
Hi Pat,

Thanks for your reply.
As you said, I've managed to move the focus to my subform.
So, the code first moves the focus to my subform and than adds new records in my subform with VBA. See my new code with the line I added to move to the subform.
Still I have a problem when I try to add a new record in my parent form with a date that already exists. The following access message occurs :

"Run-time error 2110"
Microsoft Access can't move the focus to the control "MySubForm".


Actually, what I want to see here is the message I wrote (view my code) and then return to the field "Workdate" to add a new date.
The message I wrote is in the event Form_Error.

My code :

Private Sub Workdate_AfterUpdate()

Forms!MyForm!MySubForm.SetFocus

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

Hopefully you can help me out once again, Pat, or anyone else.

Thanks already.

Pascal
 

Users who are viewing this thread

Top Bottom