Form not updating

[AdamFeerst]

I have a form based on a non-udateable query. To change data in a field, I click on the field. It makes visible an unbound combo box, and assigns the underlying value to that combo box (Me.cmbSID = Me.SID), and shifts focus to that box.

AfterUpdate changes the value in the underlying table:

stSQL = "UPDATE tblParentList SET tblParentList.SID = '" & Me.cmbSID & _
"' WHERE tblParentList.ParentID = " & Me.ParentID
Db.Execute stSQL, dbFailOnError


Me.Refresh
​

The update happens in the underlying table. However, that change isn't reflected on the form. I can see the changes if I close, reopen, and navigate back to that record. However, I don't see the changes if I just go to another record then back, keeping the form open.

What am I doing wrong?

Adam
Denver, CO USA

 

[jdraw]

Why is this??

non-udateable query

Try Me.Requery instead of Me.Refresh

 

[AdamFeerst]

Been there. Done that. No luck.

tID = Me.ParentID
Form.Requery
DoCmd.GoToControl "ParentID"
DoCmd.FindRecord tID

 

[jdraw]

Can you post a copy of your dtabase? remove anything confidential/private

We only need enough data to show the problem

You can remove people and use Porky Pig, General Purpose, Hesan Idiot etc.

 

[GinaWhipp]

Non-updateable Recordsets will only refresh after the Form is closed and reopened because that Recordset is like a *snapshot* frozen from the time you opened it. So, in order to adjust you need to adjust the Recordset.

 

[AdamFeerst]

It'll take some work to do that. Let me think about it a bit to see if it's worth the time.

What I don't understand is that the update works when changing one field, either refresh or requery, but not on others, despite the same code (public sub). Could the form be corrupted? If so, do I need to remake it?

 

[jdraw]

Here are some reasons why a query is not updateable/ is read only (Allen Browne)

Thanks Gina

 

[AdamFeerst]

Gina,

That's good to know. However, why does it update on one field?

Now what?

1) I could try to make it an updateable query. I think to do that, I'd have to put the one field that makes it nonupdateable (left/right join) into a sub form.

2) I could call a module that closes, then reopens the form.

Any suggestions, above or other?

 

[GinaWhipp]

I cannot explain the erratic behavior of Access. Normally, non-updateable means nothing changes from first *frozen* opening.

I would try to adjust the Recordset. It's not normal to have a Recordset that can't be updated attached to a Form.

 

[gemma-the-husky]

stSQL = "UPDATE tblParentList SET tblParentList.SID = '" & Me.cmbSID & _
"' WHERE tblParentList.ParentID = " & Me.ParentID
Db.Execute stSQL, dbFailOnError


Me.Refresh

is this the after update event for the combo box.

if so, I think me.refresh or me.requery may just be refreshing/requerying the control, rather than the form.

I am surprised gina thinks you cannot requery a non-updateable form. I would have thought you could do so.

maybe you need syntax like

me.form.requery

 

[GinaWhipp]

When you open a Recordset that is UN-updateable is has been my experience that won't change until it's reopened. Now, you could fool Access by closing and then reopening the Recordset but refreshing the Form does nothing since it's the Recordset that needs refreshing.

 

[AdamFeerst]

Gina & Dave/Gemma,

I've tried form.requery with no luck. That leads me to what Gina said, although I don't understand why a requery doesn't do the same thing as a close and reopen. However, that doesn't explain why the update (both refresh and requery) works for one field. Yes, it is an AfterUpdate event.

Making the underlying query updateable isn't straight forward, at least I can't think of a way after first and second looks. For all other purposes, that's not a problem.

For the most part, people will only be viewing data on screen. Only a handful of people will be able to edit any data, and then infrequently. It displays monthly revenues grouped and summarized different ways (e.g., product type, product, children/parent companies, sales rep). I use a form because I find it easier for the user to navigate with than a report. Maybe I'm wrong in that assessment.

For the few people and few instances where edits are needed, it looks like I'll have to close and reopen the form.

 

[GinaWhipp]

We could try something else../ set quey and Form to *Dynaset (Inconsistent Updates)* and try Me.Requery then. If that works, you could use some code to flip that so only authorized Users have that Access. Note, setting that should allow the data to be updated.