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

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 3.50 average. Display Modes
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
Old 10-22-2005, 11:20 AM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-22-2005, 12:37 PM   #3
pascal
isolation
 
Join Date: Feb 2002
Location: Okegem, Belgium
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
pascal
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

pascal is offline   Reply With Quote
Reply

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 11:04 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