Save Error Using Requery

[Guest]

I have a form tied directly to a table. I update a record, and need to
refresh the form. When I run .Requery, I get:

You must save the current field before you run the requery action.

Any suggestions on what code I need to add, for Access to see the record as
having been saved?

Thanks, in advance.

Sharkbyte

 

['69 Camaro]

Hi.

Any suggestions on what code I need to add, for Access to see the record as
having been saved?

Before requerying, try:

Me.Dirty = False

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[John Vinson]

Any suggestions on what code I need to add, for Access to see the record as
having been saved?

Put a line

If Me.Dirty = True Then Me.Dirty = False

prior to the requery. This will force a save, if it's needed.

John W. Vinson[MVP]

 

[Dirk Goldgar]

I have a form tied directly to a table. I update a record, and need
to refresh the form. When I run .Requery, I get:

You must save the current field before you run the requery action.

Any suggestions on what code I need to add, for Access to see the
record as having been saved?

What event are you using to execute this requery? Maybe I'm wrong, but
I wouldn't think you'd get that message unless you were executing the
requery in the Change or BeforeUpdate event of a bound control, which is
not an appropriate time to do it. I *think* this message is telling you
that you have an uncommitted value in the control, which must be
committed before you save the record and requery. Could you give some
further details on exactly what's going on to trigger the error?

 

[Guest]

Was using that, but needed to move it further up, in the code.

Thanks.

 

[jbrumbau]

If you don't want Before_Update, After_Update, and record saving to all fire from frm.dirty=false, do the following:

Sub refreshFrmCtl(ByRef frm As Form, ByRef ctl As Control)
    Dim attempts As Integer
    
On Error GoTo Err_Handler
    attempts = 0
    If frm.Dirty Then
        ctl.Value = ctl.Value
    End If
    ctl.Requery
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number = 2118 And attempts = 0 Then
        ctl.Value = 1 'Assuming 1 is your NULL value in the combo box lookup table
        attempts = attempts + 1
        Resume
    Else
        Msgbox Err.Number & "-" & Err.Description
        Resume Exit_Handler
    End If
End Sub