Addnew throwing error 3027

[ScottInTexas]

In one sub I am trying to add a record to to "Abstracts" table. Records in
the abstracts table are related to the States and counties tables. So I open
a recordset with joins. I have tried several times after checking everything
out and cannot find any reason why the recordset should be read only. I have
also checked for a lock file (as suggested in another post).

The code:

Private Sub cmdSave_Click()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

If blnCheckFields Then
'save new abstract
Set db = CurrentDb()
strSQL = "SELECT Abstracts.StateID As AStateID, Abstracts.CountyID
AS ACountyID, Abstracts.FileNo, " & _
"Abstracts.OriginalGrantee, Abstracts.Patentee, " & _
"Abstracts.PatentDate, Abstracts.PatentNumber,
Abstracts.PatentVol, Abstracts.Certificate, Abstracts.Section, " & _
"Abstracts.Acreage, Abstracts.AbstractName,
Abstracts.AbstractNo, County.CountyID, States.StateID" & _
" FROM States INNER JOIN (County INNER JOIN Abstracts ON
County.CountyID = Abstracts.CountyID) " & _
"ON (Abstracts.StateID = States.StateID) AND (States.StateID
= County.StateID) " & _
"WHERE (((County.CountyID)=" & cboCounty & ") AND
((States.StateID)=" & cboState & "));"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.FindFirst "AbstractNo" = txtAbstractNo
If rs.NoMatch Then
rs.AddNew
Else
rs.Edit
End If
With Me
rs!ACountyID = .cboCounty
rs!AStateID = cboState
rs!FileNo = .txtFileNo
rs!OriginalGrantee = .txtGrantee
rs!Patentee = .txtPatentee
rs!Patentdate = .txtDate
rs!PatentNumber = .txtPatentNo
rs!PatentVol = .txtVol
rs!Certificate = .txtCert
rs!Section = .txtSection
rs!Acreage = .txtAcreage
rs.Update
End With
ClearFields
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Else
'cancelout and leave routine
MsgBox "Missing Required Data", vbOKOnly
End If

End Sub

This routine is found in the form "frmAbstracts" and there is nothing else
open or running at the time. I have done it this way since the beginning of
time. Can't figure out what is going on.

Access 2007, Windows XP Professional, Dell Vostro 1510

Thanks for your help.

Scott

 

[AccessVandal via AccessMonster.com]

I would avoid the join with State and County, just try it out without the
table join.

strSQL = "SELECT StateID,CountyID
,FileNo, " & _
"OriginalGrantee, Patentee, " & _
"PatentDate, PatentNumber, PatentVol, Certificate, Section, " & _
"Acreage, AbstractName, AbstractNo " & _
" FROM Abstracts " & _
" WHERE CountyID=" & cboCounty & " AND StateID)=" & cboState & "));"
........
With Me
rs!CountyID = .cboCounty
rs!StateID = cboState
rs!FileNo = .txtFileNo
rs!OriginalGrantee = .txtGrantee
rs!Patentee = .txtPatentee
rs!Patentdate = .txtDate
rs!PatentNumber = .txtPatentNo
rs!PatentVol = .txtVol
rs!Certificate = .txtCert
rs!Section = .txtSection
rs!Acreage = .txtAcreage
rs.Update
End With
.....
Don't forget about required fields.

ScottInTexas wrote:
snip......