"Cannot Update. Database or Object is Read-Only."

[TitaniaTiO2]

I am getting an error message "Cannot Update. Database or Object is
Read-Only."

My form allows edits. All my text boxes are enabled and are not locked.

Any suggestions?

Thanks

Sharon

 

[Linq Adams via AccessMonster.com]

When are you getting this message?

 

[Bob Quintal]

I am getting an error message "Cannot Update. Database or Object
is Read-Only."

My form allows edits. All my text boxes are enabled and are not
locked.

Any suggestions?

Thanks

Sharon

What is the record source for this form? Some queries are read only,
for example union queries and totals queries, even though the tables
underlying them are read-write.

Sometimes queries that have more than two tables are also read only,
or queries with left or right joins.

If you need more help, copy and paste the query's SQL into a
followup message.

 

[TitaniaTiO2]

I get this error when I try to close the form.

Here is my code:

Private Sub cmbEmployeeName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Expr1] = '" & Me![cmbEmployeeName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

strSQL = "SELECT DISTINCT qryEditTrainingStatus.DocumentNumber FROM
qryEditTrainingStatus"
strSQL = strSQL & " WHERE qryEditTrainingStatus.Expr1 = '" &
cmbEmployeeName & "'"
strSQL = strSQL & " ORDER BY qryEditTrainingStatus.DocumentNumber;"

cmbSOPNumber.RowSource = strSQL

End Sub

Private Sub cmbSOPNumber_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DocumentNumber] = '" & Me![cmbSOPNumber] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
txtDocumentTitle.Visible = True
txtHistoryTrainedTo.Visible = True
txtTrainingDate.Visible = True
cmbTrainingStatus.Visible = True

End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
Set rs = Me.Recordset.Clone
rs.HistoryTrainedTo = Me![txtHistoryTrainedTo]
rs.TrainingDate = Me![txtTrainingDate]
rs.TrainingStatus = Me![cmbTrainingStatus]
DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub


Private Sub Form_Open(Cancel As Integer)
cmbEmployeeName = Null
cmbSOPNumber = Null
cmbTrainingStatus.Visible = False
txtDocumentTitle.Visible = False
txtHistoryTrainedTo.Visible = False
txtTrainingDate.Visible = False

End Sub

 

[TitaniaTiO2]

Query SQL
SELECT tblDocument.DocumentNumber, tblDocument.DocumentTitle,
tblPerson.LastName, tblPerson.FirstName, tblDepartment.Department,
tblTraining.TrainingDate, tblTraining.HistoryTrainedTo,
tblTraining.TrainingStatus, [LastName] & ", " & [FirstName] AS Expr1
FROM tblDepartment INNER JOIN ((tblDocument INNER JOIN (tblPerson INNER JOIN
tblTraining ON tblPerson.PersonID = tblTraining.PersonID) ON
tblDocument.DocumentNumber = tblTraining.DocumentNumber) INNER JOIN
tblEmployment ON tblPerson.PersonID = tblEmployment.PersonID) ON
tblDepartment.DepartmentID = tblEmployment.DepartmentID
WHERE (((tblTraining.TrainingStatus)="Training Document Issued"));

 

[Bob Quintal]

Query SQL
SELECT tblDocument.DocumentNumber, tblDocument.DocumentTitle,
tblPerson.LastName, tblPerson.FirstName, tblDepartment.Department,
tblTraining.TrainingDate, tblTraining.HistoryTrainedTo,
tblTraining.TrainingStatus, [LastName] & ", " & [FirstName] AS
Expr1 FROM tblDepartment INNER JOIN ((tblDocument INNER JOIN
(tblPerson INNER JOIN tblTraining ON tblPerson.PersonID =
tblTraining.PersonID) ON tblDocument.DocumentNumber =
tblTraining.DocumentNumber) INNER JOIN tblEmployment ON
tblPerson.PersonID = tblEmployment.PersonID) ON
tblDepartment.DepartmentID = tblEmployment.DepartmentID WHERE
(((tblTraining.TrainingStatus)="Training Document Issued"));

That query contains FIVE (5) source tables. That's probably one of
the problems.

Instead of writing to recordsetClone in your CmdClose_Click sub,
try writing to the relevant table (tblTraining) directly, using a
Where clause to select the correct record.

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & me.txtPersonID _
& " AND DocumentNumber = & & me.txtdocumentNumber _
& " AND TrainingStatus)= ""Training Document Issued"""
rs.HistoryTrainedTo = Me![txtHistoryTrainedTo]
rs.TrainingDate = Me![txtTrainingDate]
rs.TrainingStatus = Me![cmbTrainingStatus]
DoCmd.Close

I also believe you should be creating a new row in tblTraining for
each time the employee is retrained, not updating the existing row,
which is what happens now, and what your code was attempting to do.

Q

 

[TitaniaTiO2]

Thanks for the help. I thought I copied it and updated correctly, but...well
now I am getting a syntax error pointing to this statement.

What did I mess up?

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & Me.txtPersonID _
& " AND DocumentNumber = " & Me.txtDocumentNumber
& " AND TrainingStatus = " "Training Document Issued"""



I actually do not want it to overwrite the record. The user enters a
history and prints a report (training document) (which makes Traiinng Status
be "Training Document Issued")
then a few weeks later they come back in, change the status to training
complete and enter the completion date.

Thanks

Titania

That query contains FIVE (5) source tables. That's probably one of
the problems.

Instead of writing to recordsetClone in your CmdClose_Click sub,
try writing to the relevant table (tblTraining) directly, using a
Where clause to select the correct record.

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & me.txtPersonID _
& " AND DocumentNumber = & & me.txtdocumentNumber _
& " AND TrainingStatus)= ""Training Document Issued"""
rs.HistoryTrainedTo = Me![txtHistoryTrainedTo]
rs.TrainingDate = Me![txtTrainingDate]
rs.TrainingStatus = Me![cmbTrainingStatus]
DoCmd.Close

I also believe you should be creating a new row in tblTraining for
each time the employee is retrained, not updating the existing row,
which is what happens now, and what your code was attempting to do.

Q

 

[Bob Quintal]

Hi,

Thanks for the help. I thought I copied it and updated correctly,
but...well now I am getting a syntax error pointing to this
statement.

What did I mess up?

You didn't mess up. I forgot to wrap the SQL in the
Currentdb.OpenRecordset() method. I was in a rush to get some
supper. Sorry about that.

SET rs = CurrentDB.OpenRecordset("SELECT......Issued""")
should fix the problem.
' please paste the whole statement from my previous post I'm still
hungry. And check to make sure I didn't miss any criteria.


Or you could store the sql to a string variable and put the var name
into the function

Dim strSQL as String
strSQL = "SELECT......Issued""" ' please paste the whole statement.
SET rs = CurrentDB.OpenRecordset(strSQL)

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & Me.txtPersonID _
& " AND DocumentNumber = " & Me.txtDocumentNumber
& " AND TrainingStatus = " "Training Document Issued"""



I actually do not want it to overwrite the record. The user
enters a history and prints a report (training document) (which
makes Traiinng Status be "Training Document Issued")
then a few weeks later they come back in, change the status to
training complete and enter the completion date.

Thanks

Titania

That query contains FIVE (5) source tables. That's probably one
of the problems.

Instead of writing to recordsetClone in your CmdClose_Click sub,
try writing to the relevant table (tblTraining) directly, using a
Where clause to select the correct record.

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & me.txtPersonID _
& " AND DocumentNumber = & & me.txtdocumentNumber _
& " AND TrainingStatus)= ""Training Document Issued"""
rs.HistoryTrainedTo = Me![txtHistoryTrainedTo]
rs.TrainingDate = Me![txtTrainingDate]
rs.TrainingStatus = Me![cmbTrainingStatus]
DoCmd.Close

I also believe you should be creating a new row in tblTraining
for each time the employee is retrained, not updating the
existing row, which is what happens now, and what your code was
attempting to do.

Q

 

[TitaniaTiO2]

Thanks!

Hi,

Thanks for the help. I thought I copied it and updated correctly,
but...well now I am getting a syntax error pointing to this
statement.

What did I mess up?

You didn't mess up. I forgot to wrap the SQL in the
Currentdb.OpenRecordset() method. I was in a rush to get some
supper. Sorry about that.

SET rs = CurrentDB.OpenRecordset("SELECT......Issued""")
should fix the problem.
' please paste the whole statement from my previous post I'm still
hungry. And check to make sure I didn't miss any criteria.


Or you could store the sql to a string variable and put the var name
into the function

Dim strSQL as String
strSQL = "SELECT......Issued""" ' please paste the whole statement.
SET rs = CurrentDB.OpenRecordset(strSQL)

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & Me.txtPersonID _
& " AND DocumentNumber = " & Me.txtDocumentNumber
& " AND TrainingStatus = " "Training Document Issued"""



I actually do not want it to overwrite the record. The user
enters a history and prints a report (training document) (which
makes Traiinng Status be "Training Document Issued")
then a few weeks later they come back in, change the status to
training complete and enter the completion date.

Thanks

Titania

That query contains FIVE (5) source tables. That's probably one
of the problems.

Instead of writing to recordsetClone in your CmdClose_Click sub,
try writing to the relevant table (tblTraining) directly, using a
Where clause to select the correct record.

Set rs = "SELECT * from tblTraining" _
& " WHERE PersonID = " & me.txtPersonID _
& " AND DocumentNumber = & & me.txtdocumentNumber _
& " AND TrainingStatus)= ""Training Document Issued"""
rs.HistoryTrainedTo = Me![txtHistoryTrainedTo]
rs.TrainingDate = Me![txtTrainingDate]
rs.TrainingStatus = Me![cmbTrainingStatus]
DoCmd.Close

I also believe you should be creating a new row in tblTraining
for each time the employee is retrained, not updating the
existing row, which is what happens now, and what your code was
attempting to do.

Q