Trouble with recordset update

[ponderdj]

Hello,

I have a problem I'd like some advice with. I created a form that takes a query and opens a recordset on form load like this:

Public Sub Form_Load()
'rstList1 declared at the module level  '------------------------------------------------------------------------------------
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    
    
    Set db = CurrentDb

    Set qdf = db.QueryDefs("QRY_COPS_REC")
    
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    
    Set rstList1 = qdf.OpenRecordset
    rstList1.Sort = "PDC"
'------------------------------------------------------------------------------------

rstList1 then populates a listview. There is a table that keeps comments related to what appears in the listview, and it has a status field, which is pulled by QRY_COPS_REC. The form shows that STATUS field both in the listview and in a combobox that lets the user edit the status. The combobox doesn't update rstList1, it edits the table that contains the comments/status.

*Here's the part that I'm confused about*
I built one form using this method to pull in info to populate the listbox when the form loads and then use the recordset when filtering and repopulating the listview, which allows me to avoid re-running the query. For some reason on that form, if the user changes the status in the combobox and saves it, rstList1 updates it's status and everything is displayed correctly.

I built a second form using exactly the same code, but feeding it with a different query. On this form, the combobox will show the correct STATUS when the user changes it and saves it, but rstList1 shows whatever STATUS it pulled when the form opened, it does not update.

So, the first form updates rstList1, the second does not... and they use identical code except for the query that feeds the recordset.

Both queries that feed rstList1 are not updateable.

On the second form, where rstList1 does not update itself I tried to open it dynaset and use .edit and .update but on the .update line I get a "Could not update. Database or object is read only." error.

Hope someone can help.

Thanks

 

[ponderdj]

How about if I have 2 relating questions.

1. Why would a recordset not allow me to update? I'm assuming because the query that fed the recordset is not updateable?

2. How and why would a recordset update itself when a user changes a value that it had already pulled? This is considering that the recordset was not requeried.

Thanks.

 

[PHV]

Why would a recordset not allow me to update

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

Thanks, PHV.

The weird thing is that it really came down to the fact that the query/recordset wasn't reflecting a change I had made in the table. I didn't need the query to be updateable since I was entering directly into the table, I just needed it to reflect the change (without requerying) I made after the record was saved. I ended up getting it to work with some structural changes.

 

[PHV]

Have a look at the FillCache method and the CacheSize property of the DAO.Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

I'll do that. If the query that feeds the recordset fails to show an update though, the associated dao.recordset wouldn't show that change either though, right?