Problem with .addnew

[LaurieTuff via AccessMonster.com]

I have a form which allows a user to add a new category to table [CPlan Type].
After update I then want to be able to add an additional record of that
category to a table called [CPlan Outcome] for each staff member.

I'm using the following code (Item holds the # of Staff members with their
ID#s in StaffIDArray()

Set RS = db.OpenRecordset("Cplan Outcome", dbOpenDynaset)
For Numb = 0 To Item
With RS
.AddNew
!StaffID = StaffIDArray(Numb)
!Category = NewCategory
!Outcome = ""
!Updated = Today 'CDate(Today)
!UpdatedBy = UpdatedBy
.Update
End With
Next Numb
RS.Close

I get error 3201, 'You cannot add or change a record because a related record
is required in table "CPlan Type"

How can I make this work??

Laurie

 

[Armen Stein]

I have a form which allows a user to add a new category to table [CPlan Type].
After update I then want to be able to add an additional record of that
category to a table called [CPlan Outcome] for each staff member.

I'm using the following code (Item holds the # of Staff members with their
ID#s in StaffIDArray()

Set RS = db.OpenRecordset("Cplan Outcome", dbOpenDynaset)
For Numb = 0 To Item
With RS
.AddNew
!StaffID = StaffIDArray(Numb)
!Category = NewCategory
!Outcome = ""
!Updated = Today 'CDate(Today)
!UpdatedBy = UpdatedBy
.Update
End With
Next Numb
RS.Close

I get error 3201, 'You cannot add or change a record because a related record
is required in table "CPlan Type"

How can I make this work??

Laurie

Sounds like you have referential integrity enforced between CPlan type
and CPlan Outcome.

If so, you'll need to include the new CPlan Type key (for the record
your user just added) in your update.

To see how this would need to work, open the CPlan Outcome table and
manually add a record using the fields you described in your code.
You'll probably see the the CPlan Type foreign key field is defaulting
to zero.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[LaurieTuff via AccessMonster.com]

Thanks for the prompt response Armen,

You are correct in that I do have referential integrity enforced but I don't
understand what I need to do to include the CPlan Type key in the update.

CPlan Type has only two fields, Type and Order. Type relates to the CPlan
Outcome (and also the CPlan Details) table in a one to many relationship.

Can you give me more specific guidance, please?

Laurie

I have a form which allows a user to add a new category to table [CPlan Type].
After update I then want to be able to add an additional record of that

[quoted text clipped - 23 lines]

Laurie

Sounds like you have referential integrity enforced between CPlan type
and CPlan Outcome.

If so, you'll need to include the new CPlan Type key (for the record
your user just added) in your update.

To see how this would need to work, open the CPlan Outcome table and
manually add a record using the fields you described in your code.
You'll probably see the the CPlan Type foreign key field is defaulting
to zero.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[George Nicholson]

I have a form which allows a user to add a new category to table [CPlan

Type].
After update I then want to be able to add an additional record of that
category to a table called [CPlan Outcome] for each staff member.

CPlan Type has only two fields, Type and Order. Type relates to the CPlan
Outcome

*If* CPlanType.Type is related to CPlanOutcome.Category then:
!Category = (your New CPlanType.Type value)
should work.

If that's not what the relationship is, then please provide the exact
fieldnames involved. You seem to be calling it Category in one place and
Type in another and its a bit confusing.

--
HTH,
George

Thanks for the prompt response Armen,

You are correct in that I do have referential integrity enforced but I
don't
understand what I need to do to include the CPlan Type key in the update.

CPlan Type has only two fields, Type and Order. Type relates to the CPlan
Outcome (and also the CPlan Details) table in a one to many relationship.

Can you give me more specific guidance, please?

Laurie

I have a form which allows a user to add a new category to table [CPlan
Type].
After update I then want to be able to add an additional record of that

[quoted text clipped - 23 lines]

Laurie

Sounds like you have referential integrity enforced between CPlan type
and CPlan Outcome.

If so, you'll need to include the new CPlan Type key (for the record
your user just added) in your update.

To see how this would need to work, open the CPlan Outcome table and
manually add a record using the fields you described in your code.
You'll probably see the the CPlan Type foreign key field is defaulting
to zero.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[LaurieTuff via AccessMonster.com]

CPlanType.Type is related to CPlanOutcome.Category as you have realised
(despite my confusingly using two names for the related fields!) Once a new
entry is created in the CPlanType table I can add a new category directly to
the CPlanOutcome table manually but my code will not work.
I felt pretty dumb not being able to fix this issue myself but I'm beginning
to feel more stupid now because I can't even get the suggested solutions to
work either!
What is *if* ? That's not a command I know or can find reference to.
Presumably my problem relates to my routine not recognising that a valid
relationship does exist?? So how could an IF statement help?

Laurie (still confused...)

I have a form which allows a user to add a new category to table [CPlan
Type].
After update I then want to be able to add an additional record of that
category to a table called [CPlan Outcome] for each staff member.

CPlan Type has only two fields, Type and Order. Type relates to the CPlan
Outcome

*If* CPlanType.Type is related to CPlanOutcome.Category then:
!Category = (your New CPlanType.Type value)
should work.

If that's not what the relationship is, then please provide the exact
fieldnames involved. You seem to be calling it Category in one place and
Type in another and its a bit confusing.

Thanks for the prompt response Armen,

[quoted text clipped - 30 lines]

 

[George Nicholson]

*If* CPlanType.Type is related to CPlanOutcome.Category (which you have
confirmed) then include the following line in your Addnew...Update block:

!Category = CPlanType.Type

You will probably want to substitute a variable containing the value of
CPlanType.Type in the above, but you need to supply a value to the Category
field and it has to be an existing CPlanType.Type value. If this is in an
AfterUpdate event, you may be able to simply substitute the value of the
appropriate control on the form.

!Category = strMyNewType
or
!Category = Me.txtCPlanType


--
HTH,
George

CPlanType.Type is related to CPlanOutcome.Category as you have realised
(despite my confusingly using two names for the related fields!) Once a
new
entry is created in the CPlanType table I can add a new category directly
to
the CPlanOutcome table manually but my code will not work.
I felt pretty dumb not being able to fix this issue myself but I'm
beginning
to feel more stupid now because I can't even get the suggested solutions
to
work either!
What is *if* ? That's not a command I know or can find reference to.
Presumably my problem relates to my routine not recognising that a valid
relationship does exist?? So how could an IF statement help?

Laurie (still confused...)

I have a form which allows a user to add a new category to table [CPlan
Type].
After update I then want to be able to add an additional record of that
category to a table called [CPlan Outcome] for each staff member.

CPlan Type has only two fields, Type and Order. Type relates to the
CPlan
Outcome

*If* CPlanType.Type is related to CPlanOutcome.Category then:
!Category = (your New CPlanType.Type value)
should work.

If that's not what the relationship is, then please provide the exact
fieldnames involved. You seem to be calling it Category in one place and
Type in another and its a bit confusing.

Thanks for the prompt response Armen,

[quoted text clipped - 30 lines]

Microsoft Access MVP
www.JStreetTech.com

 

[LaurieTuff via AccessMonster.com]

Hi George,
The action is triggered from an AfterUpdate event and loads the new category
into the global public string NewCategory (this does actually occur) from the
form (ie NewCategory= Me.txtType) but the active routine is in a module which
first loads a StaffArray() with the ID of those staff requiring the update
and then runs the code I first showed here.
So in theory everything is in place but....????

Laurie

*If* CPlanType.Type is related to CPlanOutcome.Category (which you have
confirmed) then include the following line in your Addnew...Update block:

!Category = CPlanType.Type

You will probably want to substitute a variable containing the value of
CPlanType.Type in the above, but you need to supply a value to the Category
field and it has to be an existing CPlanType.Type value. If this is in an
AfterUpdate event, you may be able to simply substitute the value of the
appropriate control on the form.

!Category = strMyNewType
or
!Category = Me.txtCPlanType

CPlanType.Type is related to CPlanOutcome.Category as you have realised
(despite my confusingly using two names for the related fields!) Once a

[quoted text clipped - 37 lines]

 

[LaurieTuff via AccessMonster.com]

Hi George - further to my previous post I have now realised what my problem
is! I am calling my routine from the AfterUpdate event having assumed that
the update referred to the txtType field having updated the underlying [CPlan
Type] table. This isn't the case of course - only the txtType field has been
updated at that point!
So, I still have a problem. How can I automatically call my routine after
the table has been updated with the new entry?
- perhaps that will be an easier problem top resolve!

Regards, Laurie

Hi George,
The action is triggered from an AfterUpdate event and loads the new category
into the global public string NewCategory (this does actually occur) from the
form (ie NewCategory= Me.txtType) but the active routine is in a module which
first loads a StaffArray() with the ID of those staff requiring the update
and then runs the code I first showed here.
So in theory everything is in place but....????

Laurie

*If* CPlanType.Type is related to CPlanOutcome.Category (which you have
confirmed) then include the following line in your Addnew...Update block:

[quoted text clipped - 16 lines]

 

[Armen Stein]

Hi George - further to my previous post I have now realised what my problem
is! I am calling my routine from the AfterUpdate event having assumed that
the update referred to the txtType field having updated the underlying [CPlan
Type] table. This isn't the case of course - only the txtType field has been
updated at that point!
So, I still have a problem. How can I automatically call my routine after
the table has been updated with the new entry?
- perhaps that will be an easier problem top resolve!

Regards, Laurie

Hi Laurie,

It sounds like you are using the After Update event on the *control*.
You can use the After Update or After Insert event on the *form* to
run code after the whole record is updated or inserted in the
database.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[LaurieTuff via AccessMonster.com]

Quite right Armen - and everything now works just as I wanted it to.
Thanks to you and George for your assistance, much appreciated!

Regards, Laurie

Hi George - further to my previous post I have now realised what my problem
is! I am calling my routine from the AfterUpdate event having assumed that

[quoted text clipped - 6 lines]

Regards, Laurie

Hi Laurie,

It sounds like you are using the After Update event on the *control*.
You can use the After Update or After Insert event on the *form* to
run code after the whole record is updated or inserted in the
database.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com