If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Field cannot be updated error but Allows the update
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 |
#3
|
|||
|
|||
Field cannot be updated error but Allows the update
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 |
#4
|
|||
|
|||
Field cannot be updated error but Allows the update
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 |
#5
|
|||
|
|||
Field cannot be updated error but Allows the update
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 |
#6
|
|||
|
|||
Field cannot be updated error but Allows the update
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 |
Thread Tools | |
Display Modes | |
|
|