This recordset is not updateable???

[madEG]

Hello,

I have a commercial application that I have been asked to modify. It is an accdb over sql server 2005. For the most part things have been going pretty well, until...

This is the situation. We have two tables, tblPeople, and tblPeopleTypes. I added a new field to tblPeopleTypes called "ControlGroupYN". This field is a bit in sql server for the backend, and seen as a yes/no in access.

(What I am trying to do is pull across a new field from tblPersonType into the form based on a tblPerson's relationship to tblPeopleTypes (1:1), so that when the new field is Y, do something, and when the new field is N to do something else to the controls on the form... anyway...)

After making some changes to the form's record source to bring across the new field's value, and upon opening the form things are ok. Records are able to be searched for (using the search code included below - which I believe to be the culprit here) and updates are able to be made to the records.

But, when I use the search function a second time, the values in the controls are unable to be modified, and I recieve in the status bar the following msg: "This recordset is not updateable."

Note: I googled that error, and it said that I don't have a pkey, but I checked and I do for both tables - I even dropped and re-linked the two tables just in case esomething got hosed - but no joy...


Ok - the steps I took to get to this problem are this:

I went to the form that controls the tblPeople info and looked for the record source info. In the form's record source field, the original info was: "SELECT * FROM tblPeople WHERE [tblPeople].[Person ID]=-1; "

Ok - give me everything from the people table where there is an id... sounds good...

I changed that to be:

SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople, tblPeopleTypes WHERE [tblPeople].[People Type]=[tblPeopleTypes].[People Type ID]

That should (and does) value the check box for the additional field I dropped on the form.

Then I found the below in the Form_Open event...

' MGG Dec 01, 2008 - change the form's select to include the bishop control grp field...
'   mstrBaseRS = "SELECT * FROM tblPeople "
   mstrBaseRS = "SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople, tblPeopleTypes WHERE [tblPeople].[People Type]=[tblPeopleTypes].[People Type ID] AND tblPeople.[Person ID]=-1"

The original line was what I have commented out - the select * from tblpeople. The new line with the where join seemed reasonable based on the changes I want...

That part seems ok...

Then...

There was another area where the mstrBaseRS was being set, in the search function on the form, that will bring back individual records into the view of the form.

Private Sub Find_People_Button_Click()
   'Purpose    :
   'Parameters :
   '    -
   'Created    :
   'Sample Call:
   'Modified   :
   '--------------------------------------------------------
   Dim lngNewPersonId As Long
   Dim strWhereText As String
   Dim rstPeople As Recordset
   Dim rstAddress As Recordset
   On Error GoTo Error_PeopleButtonClick
   lngNewPersonId = InvokePeopleWizard()

   
   ' 1/30/2002 ef Add option to return only one record
   If lngNewPersonId <> 0 Then
     If gbolFormReturnOne Then
        '#817 05-07
' MGG Dec 01, 2008 - change the form's mstrBaseRS to become the below, always, not just when blank
'        If mstrBaseRS = "" Then
        If mstrBaseRS <> "" Then
' MGG Dec 01, 2008 - change the form's select to include the bishop control grp field...
'           mstrBaseRS = "SELECT * FROM tblPeople "
           mstrBaseRS = "SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople, tblPeopleTypes WHERE [tblPeople].[People Type]=[tblPeopleTypes].[People Type ID] "
        End If
        '#817 end
' MGG Dec 01, 2008 - change the form's select to include the bishop control grp field...
'         Me.RecordSource = mstrBaseRS & " Where tblPeople.[Person ID] = " & lngNewPersonId
         Me.RecordSource = mstrBaseRS & " AND tblPeople.[Person ID] = " & lngNewPersonId

     Else
         ' original code
         strWhereText = "[Person Id] = " & str(lngNewPersonId)
         Set rstPeople = Me.RecordsetClone
         rstPeople.FindFirst strWhereText
         If Not rstPeople.NoMatch Then
             Me.Bookmark = rstPeople.Bookmark
         End If
      
      strWhereText = "[Address Id] = " & CStr(glngAddressIdSelect)
      Set rstAddress = Me!PeopleSubform.Form.RecordsetClone
      rstAddress.FindFirst strWhereText
      If Not rstAddress.NoMatch Then
         Me!PeopleSubform.Form.Bookmark = rstAddress.Bookmark
         Me!PeopleSubform.Form.Refresh
      End If
      Me.Refresh
    End If
    ' ef end of mods
  
   
   
   End If
   Exit Sub
Error_PeopleButtonClick:
   ReportError
   Exit Sub
End Sub

Similarly, I commented out the original lines, and added the new query for the record source as being set in the mstrBaseRS. The first change was - well, to be honest - to ensure that this was happening - I couldn't understand why mstrBaseRS would be blank, so I countered that... the next two lines were to garner all the data needed for the forms data controls, and the latter to keep the " And " statements inline with the new query...


So...

Again, in short. The form opens and runs ok. Hitting the search button calls up a search form that returns a single record works ok. Updating that returned record works ok... BUT when I re-run the search function pased in above the record that is returned is returned in efectively a read-only state.

Thoughts?

Thanks for reading this far...

-Matt G.

 

[Vonnie]

That error can also happen if the record is locked by the previous search. Maybe recordset that wasn't closed? might try closing the rstaddress or rstpeople between searches - if you haven't tried that yet

 

[Pat Hartman]

FROM tblPeople, tblPeopleTypes WHERE

Jet sees this syntax as a Cartesian product and therefore not updateable. Change it to the newer syntax style:

mstrBaseRS = "SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople Inner Join tblPeopleTypes ON [tblPeople].[People Type]=[tblPeopleTypes].[People Type ID] 
Where tblPeople.[Person ID]=-1"

 

[madEG]

Woot! Thank You!

 

[madEG]

Pat, question...

Ok - this is not a slag on access - but rather a question...

Why does this query work in sql server mgt studio but not in access? Is it the way the query optimizer works, such that it didn't like what I wrote? I tend to write my queries in SSMS, and then move them to access when I have what I (think) I need.

I just want to understand more so I can foresee these problems moving forward...

Thanks again for your help.

-Matt G.

 

[Pat Hartman]

Jet makes an assumption when you leave out the join information and that assumption is that you want a Cartesian product. Jet still runs the query and produces the same results as SQL server, it just marks the query as not-updateable. The newer SQL specs suggest that explicit joins are the current standard and the old style Cartesian Product with selection criteria is out-dated.

When working with Access, it is best to use saved querydefs if at all possible. Given that, the easiest way to build them is with the QBE. Personally, I'd rather click and drag than have to type all the column names so I never build my queries any other way. If the selection criteria is too complex for the simplistic QBE view, I switch to SQL view and make changes there.