Need Help Changing a record

marsha4jesus

New member
Local time
Today, 03:52
Joined
Aug 19, 2007
Messages
7
I have a form for viewing records, but if the user wants, they can click the "EDIT" button to change a record. Once the "EDIT" button is clicked, the controls are unlocked and the user can make changes. The problem is when I attempt to scroll to the next record the error message is returned on the recordset.edit statement.
Run-Time Error "3027" Cannot update. Database is read-only.I verified in the debugger that the .AllowEdits property for the form is set to "TRUE"

Please assist, this project is past due. Thank you.
 
Last edited:
I need some more information about this. What is the record source for this Form. Is it a query and if so does it include Aggregate functions like SUM. It would be helpful if you could post the record source on the forum. If it is a query it is best if you look at it in SQL view mode and post the SQL.
 
It is a big mess! I will do better the next time.



sqlstring = "SELECT tblContactDetails.EMPLOYEE_ID, tblContactDetails.Status, tblContactDetails.DateReceived, "
sqlstring = sqlstring + "tblDistricts.DI, tblContacts.FullName, tblContacts.EmailName, tblContacts.WorkPhone, "
sqlstring = sqlstring + "tblEmployees.FULLNAME AS CompletedbyName, tblContacts.FullName AS ContactName, "
sqlstring = sqlstring + "tblContactDetails.TimeReceived, tblContactDetails.DateCompleted,"
sqlstring = sqlstring + "tblContactDetails.TimeCompleted, tblContactDetails.Nature,"
sqlstring = sqlstring + "tblContactDetails.Category_Code, tblContactDetails.Category_Comment,"
sqlstring = sqlstring + "tblContactDetails.ForwardedToLead, tblContactDetails.CompletedBy,"
sqlstring = sqlstring + "tblContactDetails.UserName, tblDistricts.DIN "
sqlstring = sqlstring + "FROM ((tblContactDetails INNER JOIN tblDistricts ON tblContactDetails.District = "
sqlstring = sqlstring + "tblDistricts.DI) LEFT JOIN tblEmployees ON tblContactDetails.CompletedBy = "
sqlstring = sqlstring + "tblEmployees.EMPLOYEE_ID) LEFT JOIN tblContacts ON tblContactDetails.EMPLOYEE_ID ="
sqlstring = sqlstring + "tblContacts.EMPLOYEE_ID "
sqlstring = sqlstring + "WHERE (((tblDistricts.DI)=" & Me.OpenArgs & ") AND ((tblContactDetails.UserName)='" & UserName & "'));"

Set db = CurrentDb
Set rst = db.OpenRecordset(sqlstring)
 
Thanks for that. It might be a good idea to put in a debug.print sqlstring just before you open the recordset to check that it has generated everything OK.

I think you may find that it is the Left Join that is making this query non-updateable. See this link for some advice on getting the query updateable. http://www.allenbrowne.com/ser-61.html

Good Luck
 
Left Joins Removed & Still Can't Update.

Thanks Rabbi. I removed the left joins and still get the same error. Please assist.

Can't update, database or object is read-only.
Still cannot update. The query is:

sqlstring = "SELECT tblContactDetails.EMPLOYEE_ID, tblContactDetails.Status, tblContactDetails.DateReceived,"
sqlstring = sqlstring + "tblContactDetails.TimeReceived, tblContactDetails.TimeCompleted, tblContactDetails.DateCompleted, "
sqlstring = sqlstring + "tblContactDetails.Nature, tblContactDetails.Category_Code, tblContactDetails.Category_Comment, "
sqlstring = sqlstring + "tblContactDetails.ForwardedToLead, tblContactDetails.CompletedBy, tblContactDetails.District, "
sqlstring = sqlstring + "tblContactDetails.UserName, tblDistricts.DIN "
sqlstring = sqlstring + "FROM tblContactDetails INNER JOIN tblDistricts ON tblContactDetails.District=tblDistricts.DI;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sqlstring)


Again, when I check the AllowEdits property, it is set to True. I have other fields on the form that are disabled and locked, however, they are not part of my query.

Does anyone know of I sample database has add and change form routines?
I have a separate form for adding a record.
 
Left Joins Removed & Still Can't Update.

Thanks Rabbi. I removed the left joins and still get the same error. Please assist.

Can't update, database or object is read-only.
Still cannot update. The query is:

sqlstring = "SELECT tblContactDetails.EMPLOYEE_ID, tblContactDetails.Status, tblContactDetails.DateReceived,"
sqlstring = sqlstring + "tblContactDetails.TimeReceived, tblContactDetails.TimeCompleted, tblContactDetails.DateCompleted, "
sqlstring = sqlstring + "tblContactDetails.Nature, tblContactDetails.Category_Code, tblContactDetails.Category_Comment, "
sqlstring = sqlstring + "tblContactDetails.ForwardedToLead, tblContactDetails.CompletedBy, tblContactDetails.District, "
sqlstring = sqlstring + "tblContactDetails.UserName, tblDistricts.DIN "
sqlstring = sqlstring + "FROM tblContactDetails INNER JOIN tblDistricts ON tblContactDetails.District=tblDistricts.DI;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sqlstring)


Again, when I check the AllowEdits property, it is set to True. I have other fields on the form that are disabled and locked, however, they are not part of my query.

Does anyone know of I sample database has add and change form routines?
I have a separate form for adding a record.
 
If you run your query from the Query builder is it possible to add a new record at the bottom of the datasheet?
 
Change Routin

I ran the following query in the query builder and I cannot add a record to the bottom of the datasheet.

SELECT tblContactDetails.EMPLOYEE_ID, tblContactDetails.Status, tblContactDetails.DateReceived,tblContactDetails.TimeReceived, tblContactDetails.TimeCompleted, tblContactDetails.DateCompleted, tblContactDetails.Nature, tblContactDetails.Category_Code, tblContactDetails.Category_Comment, tblContactDetails.ForwardedToLead, tblContactDetails.CompletedBy, tblContactDetails.District, tblContactDetails.UserName, tblDistricts.DIN FROM tblContactDetails INNER JOIN tblDistricts ON tblContactDetails.District=tblDistricts.DI where tblcontactdetails.district = 14;
 
That means that your query is not updatable. Do you really need the field from tblDistricts as thats where I think the problem is. Are the fields tblDistricts.DIN and tblDistricts.DI different. Are the two tables really only connected by tblContactDetails.District=tblDistricts.DI. I wonder if you have your inner join the wrong way round . I guess that tbl Districts is the Parent and tblContactDetails is the child. It might be worth building the query in Query Builder and seeinhow it generates the INNER JOIN in the SQL View.

I am finishing work now but I will have another look when I get home in a couple of hours.

Goodluck
 
Need Help Changing Record

No, I do not really need the tblDistricts. This table simply has the district number (1-25) and the associated District Name. Since I use district alot, I want to store the district number and not the name into the tblContactDetails table. You have resolved my problem in that I tried the query with the districts table and I can add a record.

If I set the join fields, tblContactDetails.District and tblDistricts.DI correctly, should I be able to update using the query? I will try and see. Thanks for your assistance!!!!!!!
 
Need Help Changing Record

Well! You solved my problem. Thank you so much. I will test it to see if changing the indices works;). I will let you know how it goes.

Thanks again and have a wonderful afternoon! Well, it is 11:39am here in Austin, Texas!
 
Need Help Changing Record

I changed the tbldistricts.di field to required and indexed with no duplicates and in tblContactDetails.district, i changed this field to indexed and duplicates ok. I can now update my record!

Thanks Rabbi!:)
 

Users who are viewing this thread

Back
Top Bottom