Run-time Error 2465 1

[mkallover]

I'm getting a weird run-time error every once in a while and I can't figure out what is causing it. My main form is called "frmProject" and has a subform called "frmSubUpdates". frmSubUpdates is in datasheet view is based on a query that has a where clause pointing to a field on frmProject:

SELECT *
FROM tblUpdates
WHERE tblUpdates.CPOProjectNum=Forms!frmProject!CPOProjectNum
ORDER BY tblUpdates.DateSub DESC;

When a user needs to add/edit an update they open another form called "frmUpdates". When the user clicks 'Save' on this form a small piece of code runs to save the record and then requery frmSubUpdates:

DoCmd.RunCommand acCmdSaveRecord

If CurrentProject.AllForms("frmARTUpdate").IsLoaded Then
Forms.frmARTUpdate.frmSubARTUpdate.Form.Requery
End If

If CurrentProject.AllForms("frmProject").IsLoaded Then
Forms!frmProject!frmSubUpdates.Form.Requery
End If

DoCmd.Close acForm, "frmUpdates"

So that works fine and dandy until the user decides to sort any field in frmSubUpdates. When they do, the get an input box asking for the value of "frmSubUpdates.tblProjects.CPOProjectNum". If they input the correct CPOProjectNum everything is fine but if they don't all records clear out of the subform.

If the user clicks "Cancel" instead of inputting the number they get a message that says "CPO Project Tracker can't find the field "Forms" referred to in your expression" and upon clicking debug, the VBA window opens and highlights the following line:

Forms!frmProject!frmSubUpdates.Form.Requery

Any suggestions? I'm pulling my hair out over this one.

 

[lameid]

It may behave better if you master and child fields instead of a recordsource with a where clause. Although nothing is jumping out at me as it shouldn't work.

It might also work better if you sort on the subform's orderby property as opposed to in the query.

 

[mkallover]

I tried using the master/child fields and that seems to be working OK. I'll keep testing to make sure but I think that might do it.

Thanks for the suggestion.