Table name is read-only. (Error 3328)

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

On Fri, 10 Aug 2007 00:28:00 -0700, Stephen
wrote:


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]

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" wrote:

On Fri, 10 Aug 2007 00:28:00 -0700, Stephen
wrote:


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]

On Fri, 10 Aug 2007 10:42:01 -0700, Stephen
wrote:

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]

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.

On Fri, 10 Aug 2007 12:26:00 -0700, Stephen
wrote:

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]

On Fri, 10 Aug 2007 12:26:00 -0700, Stephen
wrote:

"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]

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.

John W. Vinson wrote in
:

On Fri, 10 Aug 2007 12:26:00 -0700, Stephen
wrote:

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.

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Stephen wrote in
:

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).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/