Error 3027 Cannot Update. Database or object is read-only (1 Viewer)

I am trying to add a data set via a form into a table using VBA. The code seems to work on other forms however it wont seem to work on this form.

The code works fine until it has to add the recordset (rst.AddNew)
Code is as follows:

Private Sub cmdAddtakings_Click()
On Error GoTo Err_Desc
'call the sub to do the add
Add_New_Takings Me.Name
Calc_FY_Totals "T", txtFY_End, Me.Name
Err_Exit:
Exit Sub
Err_Desc:
MsgBox Err.Number & Chr(13) & Err.Description, vbCritical, "Data Error"
Resume Err_Exit
End Sub

Sub Add_New_Takings(sFrmName)
Dim msg1 As String

Set rst = frm.RecordsetClone
rst.AddNew <------ Right here is gives the the error in the title
'rst(0) = IDNo
rst!TakingDate = frm!txt_TakingDate
rst!CashAmt = frm!txt_CashAmt
rst!EFT_Amt = frm!txt_EFT_Amt
rst.Update
rst.Close
frm.Requery
'clear the add variable
frm!txt_TakingDate = Date
frm!txt_CashAmt = Null
frm!txt_EFT_Amt = Null
frm!txt_total = 0
End Sub

PLEASE HELP!! Its driving me nuts,

Cheers!

DataChick

Can you add records in the form itself?

I am adding the information to the tbl using VBA and I am using a button that runs the above VBA to add the information. So, no, I can't add the inforamtion on the form as all of the fields rely on VBA.

One interesting thing to note, is that when I have this for open (frm_J_Takings) I can not open the table (tbl_J_Takings) for edit - It says that it is read-only also even though none of the fields appear to be bound??

Well, you're opening the recordset on a RecordsetClone of the form, so it follows that if you can't add records on the form you won't be able to through the recordset. I would just open the recordset directly on the underlying table.

hmmmm, well its just that the recordsetclone is required for the form. The fields are then written to tbl using the VBA. It does work for this code below...

Private Sub cmdAdd_Click()
On Error GoTo Err_Desc
'call the sub to do the add
Add_New_Purchase Me.Name
Calc_FY_Totals "E", txtFY_End, Me.Name
Err_Exit:
Exit Sub
Err_Desc:
MsgBox Err.Number & Chr(13) & Err.Description, vbCritical, "Data Error"
Resume Err_Exit
End Sub

Set rst = frm.RecordsetClone
rst.AddNew
rst(0) = IDNo
rst!ExpDate = frm!txtA_ExpDte
rst!CatID = frm!cboA_CatID
rst!SuppID = frm!cboA_Supp
rst!InvNo = frm!txtA_InvNo
rst!ExtraDesc = frm!cboA_ExtExpDesc
rst!TotAmt = Round(frm!txtA_Total, 2)
rst!GST = Round(frm!txtA_GST, 2)
'rst!DueDate = frm!txtA_DueDate
'rst!PaidDate = frm!txtA_PaidDate
rst!DT_Add = Now()
rst.Update
rst.Close
frm.Requery

'clear the add variable
frm!txtA_ExpDte = Date
frm!cboA_CatID = Null
frm!cboA_Supp = Null
frm!cboA_ExtExpDesc = Null
frm!cboA_ExtExpDesc.Requery
frm!txtA_InvNo = Null
frm!txtA_Total = 0
frm!txtA_GST = 0
End Sub


Tell me i'm doing something different here? The only difference is that when i'm working on the expenses form (frmExpenses) and I open the table (tblExpenses) it DOESNOT tell me im in read-only - which leads me to beleive the table is being used in the background....

now... on that note, I have On_Open VBA as below, maybe my tbl is continuing to be used once my on open has occurred...

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Desc
'set the current FY
txtFY_End = Current_End_FY
Calc_FY_Totals "T", txtFY_End, Me.Name
Err_Exit:
Exit Sub
Err_Desc:
MsgBox Err.Number & Chr(13) & Err.Description, vbCritical, "Data Error"
Resume Err_Exit
End Sub

Function Calc_FY_Totals(sTag As String, dtDte, sfrm)
'set the US dates
USFrDte = MakeUSDate(DateSerial(Year(dtDte) - 1, 7, 1))
USToDte = MakeUSDate(DateSerial(Year(dtDte), 6, 30))
'set the form
Set frm = Forms(sfrm)
'do based on tag
Select Case sTag
Case "T"
'set the sql
sql = "SELECT Count(*) " _
& "FROM [tbl_J_Takings]" _
& "WHERE TakingDate Between " & USFrDte & " And " & USToDte
If Return_Single_Value(sql) = 0 Then
frm!txtFY_Tot = 0
frm!txtFY_GST = 0
rst.Close
Else
sql = "SELECT Sum(CashAmt), Sum(EFT_Amt) " _
& "FROM [tbl_J_Takings] " _
& "WHERE TakingDate Between " & USFrDte & " And " & USToDte
Set rst = CurrentDb.OpenRecordset(sql)
frm!txtFY_Tot = rst(0)
frm!txtFY_GST = rst(1)
rst.Close
End If


End Select
frm.RecordSource = sql
frm.Requery
frm!txtFY_Tot.Requery
frm!txtFY_GST.Requery
End Function


Any thoughts?

When you use RecordsetClone, as noted in Help "This creates a dynaset-type Recordset that refers to the same underlying query or data as the form". Bottom line, if you can't add records to the form you won't be able to add records to the recordset. So it works for your forms that are otherwise editable, but not for any form such as this one that isn't. The solution is to not use RecordsetClone.

Ok, so how would you write it so its adds the data to the corresponding table as well as adds the clone afterwards?

I would add it to the table and then requery the form.

I fixed it.... as I thought, it was to do with the event procedure set on open of the form.

I just needed to set the record source as sql and then requery the entire form (i got that idea from your last post so thank you) so I closed the underlying table.

The recordsetclone was read only because the underlying table was still open in the background.

Thanks for your help!!

DataChick

Glad you got it sorted out, and welcome to the site by the way!