Field cannot be updated error but Allows the update

I'm almost to the end of my bug list of this release (Thanks for helping me
get there!!!)

A subform on my main form has a combo box that gives the above error as soon
as a value is selected. However, the update still takes place.

The recordsource of the subform is:
SELECT dbo_UserTraining.*, dbo_Training.FacilityID, dbo_Training.TrainingDate,
dbo_Training.TrainingTime, dbo_Facility.FacilityName, dbo_Facility.
FacilityAddress, dbo_Facility.FacilityCity, dbo_Facility.FacilityState,
dbo_Facility.FacilityZip, dbo_Facility.FacilityPhone, dbo_Training.ProjectID
FROM (dbo_Facility INNER JOIN dbo_Training ON dbo_Facility.FacilityID =
dbo_Training.FacilityID) INNER JOIN dbo_UserTraining ON dbo_Training.
TrainingID = dbo_UserTraining.TrainingID
WHERE (((dbo_Facility.ProjectID)=113))
ORDER BY dbo_Facility.FacilityState, dbo_Facility.FacilityCity, dbo_Training.
TrainingDate, dbo_Training.TrainingTime;

Based on some other threads, I tried saving this as a query and naming the
query as the record source but then I wasn't able to change the form's
AllowAdditions property to True. As long as I'm using the Select statement it
allows me to AllowAdditions = False.

The RowSource of the combo box is:
SELECT dbo_Training.TrainingID, dbo_Facility.FacilityName, dbo_Facility.
FacilityCity, dbo_Training.TrainingDate, dbo_Training.TrainingTime,
qryTrainingAvailable.Available
FROM dbo_Facility INNER JOIN (dbo_Training LEFT JOIN qryTrainingAvailable ON
dbo_Training.TrainingID = qryTrainingAvailable.TrainingID) ON dbo_Facility.
FacilityID = dbo_Training.FacilityID
WHERE (((dbo_Facility.ProjectID)=113))
ORDER BY dbo_Facility.FacilityCity, dbo_Training.TrainingDate, dbo_Training.
TrainingTime, dbo_Facility.FacilityName;

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Hi TraciAnn --

I see no other posts ....

I can think of no reason why selecting a row in a combo box would be
trying to update anything.

Some thoughts as to debugging steps - perhaps I'll manage to supply some
ideas you havn't already investigated:

Have you established that the error is indeed being generated by Access
/ Jet and not from VBA code? From your description I gather that the
error pops either as soon as you "click" or exit the control. Do you
have any event code running that you can breakpoint before the error
pops? Sometimes I have been able to track something down by using
time-stamped debug.print statements liberally sprinkled throughout the
code in question - I remember spending a lot of time on a vexing problem
before I discovered that code that I was executing in an onExit event
was triggering other events and I was making hash out of things becuase
I was re-entering code that was never designed to be re-entrant.

Here's a little sub procedure I have in a standard code module for use
in debugging -- it will print a timestamp, and the Name and (default ..
typically Value) properties of controls (or other objects), and the
contents of any variables or literals that I pass it:


Public Sub dp(ParamArray a())
Dim v As Variant
Debug.Print Time;
On Error Resume Next
For Each v In a()
Debug.Print " "; v.Name;
Debug.Print " ["; v; "]";
Next v
On Error GoTo 0
Debug.Print
End Sub


to call it, just type

dp "anything you want to print", object or vairable name, etc

for example,

dp "sub_mySub", Me.cboSomeComboBox, "myVariable =", myVariable

Your subform recordsource: If you execute that saved query from the
database window, does it produce an upateable recordset?

How about the combo's rowsource? have you saved it as a query and tried
running it by itself?

--
Clif

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:98c5ae393af86@uwe...
I'm almost to the end of my bug list of this release (Thanks for
helping me
get there!!!)

A subform on my main form has a combo box that gives the above error
as soon
as a value is selected. However, the update still takes place.

The recordsource of the subform is:
SELECT dbo_UserTraining.*, dbo_Training.FacilityID,
dbo_Training.TrainingDate,
dbo_Training.TrainingTime, dbo_Facility.FacilityName, dbo_Facility.
FacilityAddress, dbo_Facility.FacilityCity,
dbo_Facility.FacilityState,
dbo_Facility.FacilityZip, dbo_Facility.FacilityPhone,
dbo_Training.ProjectID
FROM (dbo_Facility INNER JOIN dbo_Training ON dbo_Facility.FacilityID
=
dbo_Training.FacilityID) INNER JOIN dbo_UserTraining ON dbo_Training.
TrainingID = dbo_UserTraining.TrainingID
WHERE (((dbo_Facility.ProjectID)=113))
ORDER BY dbo_Facility.FacilityState, dbo_Facility.FacilityCity,
dbo_Training.
TrainingDate, dbo_Training.TrainingTime;

Based on some other threads, I tried saving this as a query and naming
the
query as the record source but then I wasn't able to change the form's
AllowAdditions property to True. As long as I'm using the Select
statement it
allows me to AllowAdditions = False.

The RowSource of the combo box is:
SELECT dbo_Training.TrainingID, dbo_Facility.FacilityName,
dbo_Facility.
FacilityCity, dbo_Training.TrainingDate, dbo_Training.TrainingTime,
qryTrainingAvailable.Available
FROM dbo_Facility INNER JOIN (dbo_Training LEFT JOIN
qryTrainingAvailable ON
dbo_Training.TrainingID = qryTrainingAvailable.TrainingID) ON
dbo_Facility.
FacilityID = dbo_Training.FacilityID
WHERE (((dbo_Facility.ProjectID)=113))
ORDER BY dbo_Facility.FacilityCity, dbo_Training.TrainingDate,
dbo_Training.
TrainingTime, dbo_Facility.FacilityName;

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1




--
Clif

Hi Clif!

I can think of no reason why selecting a row in a combo box would be
trying to update anything.

Ummm....The selection of a Training Description in the combo box creates a
record in the underlying UserTraining table of the fsubUserTraining form.

So, as soon as Training Description is clicked it creates a new record in
UserTraining (creating UserTraining.UserTrainingID) inserts Training.
TrainingID into UserTraining.TrainingID and User.UserName into UserTraining.
UserName

(or do I not understand correctly on how the Jet works?)

Have you established that the error is indeed being generated by Access
/ Jet and not from VBA code?

The combo only has 2 Events with very little code
AfterUpdate - Me.AllowAdditions = False
OnEnter - Me.cboTrainScheduleID.Requery

I commented each with no resolve.

The subform only has 2 Events with very little code
OnCurrent - If Not Me.NewRecord Then Me.AllowAdditions = False End
If
OnOpen - If Me.RecordsetClone.RecordCount = 0 Then Me.
AllowAdditions = False End If

I commented each with no resolve.

Sometimes I have been able to track something down by using
time-stamped debug.print statements liberally sprinkled throughout the
code in question

Based on commenting out all code and still receiving the error, this probably
wouldn't help, but thanks for the code!!

Your subform recordsource: If you execute that saved query from the
database window, does it produce an upateable recordset?

No. I am able to create a record through the recordsource with no errors.

How about the combo's rowsource? have you saved it as a query and tried
running it by itself?

Yes. It runs as expected.

I'm having the SQL administrator check into the permissions of UserTraining
to see if maybe it is a permissions issue. I wouldn't think so since it does
allow the addition.

Any other ideas?

I appreciate all your work Clif! You have always been generous to me...Thanks!


--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

You may need suggestions from a more experienced developer.

The picture in my mind has changed a bit: It sounds like the
ControlSource for your Training Description combo box is
UserTraining.UserTrainingID, and selecting a Training Description is
analagous to starting to type into a field text box on the "add new
record" row of a datasheet view -- that is, a new record is created for
the child table, and Jet populates the appropriate fields based on the
relationships.

Have you posted the exact error message you are seeing? I'm thinking
there is something about the RowSource query of your combo box that is
triggering the error message ("Field cannot be updated"). Since you have
observed no ill effects other than the annoyance of the error pop, you
might be able to work around it with "DoCmd.SetWarning False", but that
carries it's own set of potential trouble. With SetWarnings off (read
the help files carefully!) you can disable things that you need to have
working.

(There has been recent discussion regarding SetWarnings, such as this
from Dirk Goldgar:
Subject: Auto-Save Object Changes: How To Turn Off?
Date: Thu, 11 Jun 2009 11:46:26 -0400
)

HTH!
--
Clif


"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:98d310b8c36a1@uwe...
Hi Clif!

I can think of no reason why selecting a row in a combo box would be
trying to update anything.

Ummm....The selection of a Training Description in the combo box
creates a
record in the underlying UserTraining table of the fsubUserTraining
form.

So, as soon as Training Description is clicked it creates a new record
in
UserTraining (creating UserTraining.UserTrainingID) inserts Training.
TrainingID into UserTraining.TrainingID and User.UserName into
UserTraining.
UserName

(or do I not understand correctly on how the Jet works?)

Have you established that the error is indeed being generated by
Access
/ Jet and not from VBA code?

The combo only has 2 Events with very little code
AfterUpdate - Me.AllowAdditions = False
OnEnter - Me.cboTrainScheduleID.Requery

I commented each with no resolve.

The subform only has 2 Events with very little code
OnCurrent - If Not Me.NewRecord Then Me.AllowAdditions = False
End
If
OnOpen - If Me.RecordsetClone.RecordCount = 0 Then Me.
AllowAdditions = False End If

I commented each with no resolve.

Sometimes I have been able to track something down by using
time-stamped debug.print statements liberally sprinkled throughout the
code in question

Based on commenting out all code and still receiving the error, this
probably
wouldn't help, but thanks for the code!!

Your subform recordsource: If you execute that saved query from the
database window, does it produce an upateable recordset?

No. I am able to create a record through the recordsource with no
errors.

How about the combo's rowsource? have you saved it as a query and
tried
running it by itself?

Yes. It runs as expected.

I'm having the SQL administrator check into the permissions of
UserTraining
to see if maybe it is a permissions issue. I wouldn't think so since
it does
allow the addition.

Any other ideas?

I appreciate all your work Clif! You have always been generous to
me...Thanks!


--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1




--
Clif

Clif,

I found the problem, it is a separate text box with a default value. I turned
off the default in the form and setting it at the table level but I'm
perplexed as to why it wouldn't allow a default value but I could enter it
directly into the table.

Nevertheless, issue resolved...thanks for your help!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Hi TraciAnn -

Sometimes I find that I have to set some things aside simply because I
don't have the time to track down the precise cause / chain of events --
it's never easy to let a mystery lie unresolved, though.

In this case I suspect there is some kind of timing conflict; but off
the top of my head I can't say why.

Normally, a form control's defaultValue property will override the table
column's defaultValue property.


Glad you found it!

--
Clif

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:99022fa80e805@uwe...
Clif,

I found the problem, it is a separate text box with a default value. I
turned
off the default in the form and setting it at the table level but I'm
perplexed as to why it wouldn't allow a default value but I could
enter it
directly into the table.

Nevertheless, issue resolved...thanks for your help!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1




--
Clif