Table <name> is read-only. (Error 3328)

[Stephen]

Hi,

I don't know why this error start to occur, but it just happens all of sudden.

Because of this, I can't edit table information on forms.

Could anyone help me to fix this read-only error?

Thank you

 

[John W. Vinson]

Hi,

I don't know why this error start to occur, but it just happens all of sudden.

Because of this, I can't edit table information on forms.

Could anyone help me to fix this read-only error?

Thank you

Not without knowing more about the problem, no.

Is this a local Access table? Linked from another .mdb file? Linked from
SQL/Server?

John W. Vinson [MVP]

 

[Stephen]

Dear John,

I didn't know what was causing the problem, but here is what I've found so
far.

It is a transaction form that brings data from company, transaction tables.

When I open the transaction form, it allows me to edit the information,
however if the form opened via filter criteria, it becomes read-only which is
causing no data change.

There is a transaction history form which can be opened from company, and
the form has subform that displays list of transactions by that company Id.

If users double click transaction Id field in the form, it triggers
following code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_transaction"

stLinkCriteria = "[transid] = " & Me![transid]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frm_transaction_history"

I also have checked form data properties to check allow edits is enabled.

I hope this tells my problem.

 

[John W. Vinson]

It is a transaction form that brings data from company, transaction tables.

When I open the transaction form, it allows me to edit the information,
however if the form opened via filter criteria, it becomes read-only which is
causing no data change.

"if the form opened via filter critera" - please explain; I presume you have
some code which sets criteria and then opens the form?

There is a transaction history form which can be opened from company, and
the form has subform that displays list of transactions by that company Id.

So you have BOTH a separate popup form, and ALSO a subform, to display
transactions? Is either of these forms editable?

If users double click transaction Id field in the form, it triggers
following code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_transaction"

stLinkCriteria = "[transid] = " & Me![transid]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frm_transaction_history"

I also have checked form data properties to check allow edits is enabled.

What are the Recordsource properties of the form? of the subform? Please post
the SQL if these are queries.

Again: *are these local tables* or linked from some other database or some
other program (such as SQL)?

John W. Vinson [MVP]

 

[Stephen]

Thank you very much John for your time to helping me.

The tables are local tables, not linked.

Here is the process.

1. User open "frm_company" to check whether any invoice issued or not.

There is an invoice search button which triggers following code;

Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT tbl_transaction.transcid FROM tbl_transaction "
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

_______________
If I change this to Dynaset, code doesn't show transaction in
subform

rs.FindFirst "transcid = " & Me![cid]

This checks matching company Id in table transaction.

If rs.NoMatch Then

MsgBox "No Invoice Issued To This Company!", vbOKOnly + vbCritical, _
"No Record"

Else

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_transaction_history"

stLinkCriteria = "[cid]=" & Me![cid]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frm_company"

If there are matching company Id it opens "frm_transaction_history",
and closes current form, "frm_company"

Exit Sub
rs.Close

End If

2. With "frm_transaction_history" form opened, users double click
transaction id field in "frm_transaction_history" subform to fire following
code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_transaction"

stLinkCriteria = "[transid] = " & Me![transid]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frm_transaction_history"

3. Finally "frm_transaction" is opened to view, and it closes
"frm_transaction_history"

"frm_company" (record source: table company, Dynaset) ------>

"frm_transaction_history" (record source: table company, Dynaset) -------->

"frm_transaction" (record source: table compan inner join table transaction,
Dynaset)

I am sorry Jonh, if this still doesn't explain clearly.

Please help.

 

[John W. Vinson]

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

That's why it's not updateable. dbOpenSnapshot is a non-editable point-in-time
snapshot of what was in the database at the time the recordset opens.

Use dbOpenDynaset if you want to edit the data.

John W. Vinson [MVP]

 

[John W. Vinson]

"frm_transaction" (record source: table compan inner join table transaction,
Dynaset)

I am sorry Jonh, if this still doesn't explain clearly.

I've asked twice, now here's a third time:

Please post the SQL view of this query.

The transaction form will not be updateable unless the inner join is from a
unique index in the company table to a (enforced referential integrity)
foreign key in transactions, and you'll only be able to edit the transactions
fields, not the company field.


John W. Vinson [MVP]

 

[Stephen]

Dear John,

I've changed it to dynaset, but I am still out of luck.

The transaction form doesn't allow me to edit data.

This is so weird.

Thank you for your time.

 

[David W. Fenton]

That's why it's not updateable. dbOpenSnapshot is a non-editable
point-in-time snapshot of what was in the database at the time the
recordset opens.

Use dbOpenDynaset if you want to edit the data.

But that's only the lookup SQL -- used only to see if there's data
to load into the other form.

 

[David W. Fenton]

1. User open "frm_company" to check whether any invoice issued or
not.

There is an invoice search button which triggers following code;

Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT tbl_transaction.transcid FROM tbl_transaction
" Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

_______________
If I change this to Dynaset, code doesn't show
transaction in
subform
rs.FindFirst "transcid = " & Me![cid]

This checks matching company Id in table transaction.

This is ridiculously inefficient. There are two approaches that are
much more efficient:

1. put the FindFirst criteria in your SQL and then check for
..RecordCount = 0.

2. don't check at all, and have the OnOpen event of your form tell
the user if there are no records.

Here's what your code would look like for the first approach:

Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT tbl_transaction.transcid FROM tbl_transaction "
strSQL = strSQL & "WHERE [transcid=" & Me!cid
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount 0 Then
MsgBox "No Invoice Issued To This Company!", _
vbOKOnly + vbCritical, "No Record"
Else
DoCmd.OpenForm "frm_transaction_history", , , _
"[cid]=" & Me!cid
DoCmd.Close acForm, Me.Name
End If

The second approach would just open the form *hidden*:

DoCmd.OpenForm "frm_transaction_history", , , _
"[cid]=" & Me!cid, , acHidden

Then in the form's OnOpen event:

If Me.RecordsetClone.RecordCount = 0
MsgBox "No Invoice Issued To This Company!", _
vbOKOnly + vbCritical, "No Record"
Cancel = True
Else
Me.Visible = True
End If

That takes care of it and means you don't have to do a lookup before
you open the form. This is the type of method I always use (though
not exactly this simple).

 

[Stephen]

Thank you, David.

It works really well, but it still haven't fix my "read-only" problem after
it opens 'frm_transaction"

Boy, I feel so dumb, and don't know where I should start to look for.

Thank you again, David.

1. User open "frm_company" to check whether any invoice issued or
not.

There is an invoice search button which triggers following code;

Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT tbl_transaction.transcid FROM tbl_transaction
" Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

_______________
If I change this to Dynaset, code doesn't show
transaction in
subform
rs.FindFirst "transcid = " & Me![cid]

This checks matching company Id in table transaction.

This is ridiculously inefficient. There are two approaches that are
much more efficient:

1. put the FindFirst criteria in your SQL and then check for
..RecordCount = 0.

2. don't check at all, and have the OnOpen event of your form tell
the user if there are no records.

Here's what your code would look like for the first approach:

Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT tbl_transaction.transcid FROM tbl_transaction "
strSQL = strSQL & "WHERE [transcid=" & Me!cid
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount 0 Then
MsgBox "No Invoice Issued To This Company!", _
vbOKOnly + vbCritical, "No Record"
Else
DoCmd.OpenForm "frm_transaction_history", , , _
"[cid]=" & Me!cid
DoCmd.Close acForm, Me.Name
End If

The second approach would just open the form *hidden*:

DoCmd.OpenForm "frm_transaction_history", , , _
"[cid]=" & Me!cid, , acHidden

Then in the form's OnOpen event:

If Me.RecordsetClone.RecordCount = 0
MsgBox "No Invoice Issued To This Company!", _
vbOKOnly + vbCritical, "No Record"
Cancel = True
Else
Me.Visible = True
End If

That takes care of it and means you don't have to do a lookup before
you open the form. This is the type of method I always use (though
not exactly this simple).

 

[John W. Vinson]

It works really well, but it still haven't fix my "read-only" problem after
it opens 'frm_transaction"

One question:

If you already have a working Subform based on the Transactions table, which I
presume you can edit, *why open frm_transaction AT ALL!?*

You can do your editing right there on the subform.

John W. Vinson [MVP]

 

[Stephen]

Dear John,

Here is the query.


SELECT tbl_transaction.*, tbl_company.caddress, tbl_company.ccity,
tbl_company.cstate, tbl_company.czip, tbl_company.ctax
FROM tbl_company INNER JOIN tbl_transaction ON tbl_company.cid =
tbl_transaction.transcid;


I'm so sorry not to tell my situation clearly.

Thank you.

 

[Stephen]

Hi, John,

Yeah, I can edit the transaction subform well, which I can freely add,
remove, or switch item with price, but I need to change invoice date,
shipping address, etc in main form sometime.

Mainform "frm_transaction" contains:

Invoice Number
Invoice Date (need to change sometime)

Company Name (need to change sometime)
Company address (need to change sometime)

Sales Person Name (need to change sometime)
Shipping address (need to change sometime)

Subform "frm_transaction_detail" (editable)

Quantity
Item Description
Total Price

Nothing in the main form editable.
I'm totally stuck.

Sorry for bothering you for this stupid problem.

Thank you.

 

[John W. Vinson]

SELECT tbl_transaction.*, tbl_company.caddress, tbl_company.ccity,
tbl_company.cstate, tbl_company.czip, tbl_company.ctax
FROM tbl_company INNER JOIN tbl_transaction ON tbl_company.cid =
tbl_transaction.transcid;


I'm so sorry not to tell my situation clearly.

Is cid the Primary Key of tbl_company?
Have you defined a Relationship between tbl_company joining cid to transcid?
again... WHY do you feel that you need to open a separate form?

John W. Vinson [MVP]

 

[John W. Vinson]

Yeah, I can edit the transaction subform well, which I can freely add,
remove, or switch item with price, but I need to change invoice date,
shipping address, etc in main form sometime.

Mainform "frm_transaction" contains:

Invoice Number
Invoice Date (need to change sometime)

Company Name (need to change sometime)
Company address (need to change sometime)

Sales Person Name (need to change sometime)
Shipping address (need to change sometime)

Subform "frm_transaction_detail" (editable)

THANK YOU.

Now I think I understand your structure well enough to help.

If frm_transaction contains fields from both tbl_company and tbl_transaction,
then you CANNOT edit the Company fields, only the transaction fields.

Solution: use the Not In List event of the combo box selecting the CompanyID,
or perhaps a command button, to open a *separate* form based on tbl_company.
You will not be able to edit both tables on one form.

John W. Vinson [MVP]

 

[Stephen]

Dear John,

Oh, I see.

I didn't know that it can't be changable if multiple tables present in one
form.

However, I recall that I was able to change those field at one point, and it
can be changed if I just open the form alone.

Thank you very much for your time to help me.

 

[David W. Fenton]

However, I recall that I was able to change those field at one
point, and it can be changed if I just open the form alone.

This might be a case where DISTINCTROW would help. In your
recordsource, on the Query properties, choose UNIQUE RECORDS and see
if that makes it editable.

 

[Stephen]

Dear David,

Would you please tell me how to set Unique records for the query properties?

I can't see where to change the properties.

Thank you so much.

 

[John W. Vinson]

Would you please tell me how to set Unique records for the query properties?

I can't see where to change the properties.

Right mouseclick the background of the table window, and choose Properties.

John W. Vinson [MVP]