Trouble with form requery that has been modified on beforeupdate combo box event

[Michael R]

Dear users and experts,

An unbound combo box in my form is responsible for changing a city name for an update query that creates a temprorary table which the form uses as its record source.

In case that table is already created, and the combo box value is to be changed again, the temp. table needs to be deleted and the form needs to be requeried. I use on before update event of the combo box:

Private Sub citylist_BeforeUpdate(Cancel As Integer)
    If DCount("CusId", "tblTempDist") > 0 Then
        If MsgBox("This will reset the temprorary list, continue?", vbExclamation + vbOKCancel  _
            , strProjectTitle) = vbCancel Then
            Cancel = True
            DoCmd.CancelEvent
            SendKeys "{ESC}", False
            Exit Sub
        Else
            'user agrees to reset
            Cancel = False
 
            DoCmd.RunSQL " delete * from tblTempDist; "
 
            Me.Requery      ' here the error appears
 
            Exit Sub
        End If
    Else
        Cancel = False
    End If
End Sub

Changing citylist combo box value returns the following error on Me.Requery line: Run-time Error '2115': The macro or function set to the BeforeUpdate or ValidationRule propery for this field is preventing database1 from saving the data in the field.

Replacing Me.Requery with Me.cusId.SetFocus returns the following error: Run-time error '2108': You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method

I don't understand the underling problem of this error, what am I doing wrong and how to solve this.

Need your help.

Thanks,
Michael.

[NeoPa]

You are requerying the form when you want to requery just the ComboBox.
Try changing that line to :

            Call Me!citylist.Requery

[Michael R]

You are requerying the form when you want to requery just the ComboBox.
Try changing that line to :

            Call Me!citylist.Requery

Well, it returns the same mistake as with form requery...
The funny thing is, that it only returns that mistake on BeforeUpdate event. If I requery afterwards in a seprate command , requery works

[NeoPa]

Try putting that one line in the AfterUpdate event procedure then.
It won't do any harm - even if it wasn't necessary (but you can make it work only in the same circumstances with code if you really want to).
It may be because it won't allow a requery of a field from within the BeforeUpdate of that same field for reasons of data integrity.

[Michael R]

Try putting that one line in the AfterUpdate event procedure then.
It won't do any harm - even if it wasn't necessary (but you can make it work only in the same circumstances with code if you really want to).
It may be because it won't allow a requery of a field from within the BeforeUpdate of that same field for reasons of data integrity.

Of course! The AfterUpdate event! I should've think of that!

Thanks :)

[NeoPa]

Of course! The AfterUpdate event! I should've think of that!

Thanks :)

It wasn't so obvious.
The BeforeUpdate event was correct for what you were doing, it's just that it can't allow you to change an item while you're still determining whether the proposed change to that same item should be allowed. If it didn't protect you from doing that it could really get its knickers in a twist.

[Michael R]

The BeforeUpdate event was correct for what you were doing, it's just that it can't allow you to change an item while you're still determining whether the proposed change to that same item should be allowed.

But in the BeforeUpdate event I was trying to change not the combo-box, but the form's underlying table which is updated based on the combo-box value. The combo-box is unbound.

Still, the change/update of the underlying table was possible only on the AfterUpdate event.

Regards.

[NeoPa]

That wasn't the version I was working on.
I was under the impression that you had applied the change I suggested in post #2.
Not that it's important now I suppose.
Glad you've got it sorted anyway :)

[Michael R]

That wasn't the version I was working on.
I was under the impression that you had applied the change I suggested in post #2.
Not that it's important now I suppose.
Glad you've got it sorted anyway :)

Thanks for you openhandness :)