Save Error Using Requery

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Any suggestions on what code I need to add, for Access to see the record as
having been saved?
Click to expand...

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]
 
Sharkbyte said:
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?
Click to expand...

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?
 
If you don't want Before_Update, After_Update, and record saving to all fire from frm.dirty=false, do the following:

Code: 
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
 
Back
Top