Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
 
 
Thread Tools Rating: Thread Rating: 2 votes, 3.50 average. Display Modes
Prev Previous Post   Next Post Next
Old 10-21-2005, 07:03 PM   #1
pascal
isolation
 
Join Date: Feb 2002
Location: Okegem, Belgium
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
pascal
Run-time error “3201”

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 by pascal; 10-22-2005 at 07:42 AM.
pascal is offline   Reply With Quote
 

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run time versions and missing references Dan_T General 8 07-19-2004 03:50 AM
Run time error 13 andrewf10 Forms 11 02-10-2004 02:49 AM
run time app mikeder General 0 02-18-2003 05:30 PM
obj required run time err – why? arage Modules & VBA 2 01-30-2001 04:41 PM




All times are GMT -8. The time now is 06:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World