Query requery headache

[Carl_R]

On my form, I have an unbound ComboBox with a rowsouce of:

SELECT DISTINCTROW [qryOverDue].[ChangeID], [qryOverDue].[ImplementationDate], [qryOverDue].[Group] FROM [qryOverDue];

Also on the form are a CheckBox (Closed) and a TextBox (ClosedDate).

The underlying query displays records whose date is <NOW()-1 and 'Closed' = False

When a user selects a record from the ComboBox, they are taken to the record - this part works fine.

The user can then tick the CheckBox (causing its value to be True). The user can then enter the date in the TextBox ClosedDate.

The problem is, if the user tries to select another record from the ComboBox, there's an error (currently trapped with some instruction via a msgbox).

It's almost like a requery needs to run... but where and when?

My interim solution is: If the user advances to the next record and then goes back to the previous record, they are able to successfully select another record from the ComboBox but the record just updated is still in the list (even though Closed = True).

Obviously this 'solution' is pretty cheesy and I only mention it here to show my complete ineptness

 

[Rich]

Save the record on the AfterUpdate of the date field

 

[Carl_R]

Save the record on the AfterUpdate of the date field

Hi Rich

Tried that but same result.
I'm doing a DoCmd.Save (is this correct?)

If I do a Records/Save record from the menu, it works but the record still appears in the ComboBox.

 

[Rich]

DoCmd.RunCommand acCommandSaveRecord
ComboName.Requery

 

[Carl_R]

Hi Rich

Sweet

Private Sub ClosedDate_AfterUpdate()
DoCmd.RunCommand acCmdSave
DoCmd.GoToRecord , , acNext
Combo141.Requery
DoCmd.GoToRecord , , acPrevious
End Sub

Also do a refresh on the AfterUpdate of the CheckBox, just in case a user decides to uncheck it (this puts the record back in the ComboBox).

You're a life saver. (didn't know RunCommand existed!).

Now my sorry carcass doesn't look so sorry and a degree of kudos has been restored... until next time.