Form_AfterInsert() Me.Requery Runtime 2115

[Thomasso]

Hello,



I have an "Orders" form and "OrderDetails" subform on it.

I want to run this code:

Private Sub Form_AfterInsert()
    
    Me.ItemOrder = DMax("ItemOrder", "vDE_PurchaseOrdersSub", "PurchaseOrderID=" & Me.PurchaseOrderID) + 1 ' this is an automatically generated ItemNumber for the particular order (1, 2, 3, etc.)
    Me.Requery

End Sub

I use the Me.Requery to immediately display the value in a text box after the record is inserted.

On the Me.Requery line, I get this error:



If I remove the first line and only leave the requery, the error disappears.

Why might this be? Thank you very much.
Tomas

[Micron]

You've begun a record (it is in edit mode) but then are trying to reload the form. Try inserting Me.Dirty = False (which will save the record). Not exactly sure what you want to happen after generating the number so I'm guessing there is no need to requery.

[Thomasso]

Thanks, however exactly same thing happens if I replace Me.Requery with Me.Dirty = False

[Micron]

Do you have BeforeUpdate code running on that form or a validation rule on a field as the message implies? If you have BeforeUpdate code for the form, then I suggest you post that and/or your validation rule(s).

[Thomasso]

I do have some BeforeUpdate code, but I commented everything out (form itself and fields as well), without any result. I was troubleshooting the thing for a couple of hours, need to sleep on it I guess...

[Micron]

You could always copy, compact then zip the db and post it. Would probably be faster. Did you try stepping through the code to see the series of events that take place?

[Thomasso]

Yes, there are many events. I tried stepping through of course, but I'm a little confused at some points.

I would send the DB, but the back end is a local Microsoft SQL Server, so no chance. I could send you the code for the entire form though, if that helps?

[Micron]

Might not be able to pinpoint anything without stepping through and that likely will fail with no tables/data. All I can suggest is post the entire form code and maybe someone will spot something. However, if you have calls to procedures contained in other modules, people will want to know what those are doing and might ask for that as well. Thus you might only shed some light on the problem if you include the called code as well. Probably not required if it only does something that is inconsequential to the issue, such as returning a value, checking for the existence of a file and so on. In that case you can add comments in the code to clarify what is returned by functions external to the form code.

I suggest copy the db, eliminate all that is not needed to replicate your issue, then use maketable queries to create whatever tables would be required and just pull in a few records. Then delete the external joins from the db copy. Test your form to ensure you can replicate the issue then compact/zip/post that. You did not answer the question about validation on any fields involved in the form records.

PS if you do post code, please paste code within code tags (use # button on posting toolbar) to maintain indentation and readability.

[Gicu]

I have a free utility that allow you to create an all-in-one from your fe\be files and use that to upload (after you remove sensitive data - there is a data cleaner on my site as well): http://forestbyte.com/ms-access-utilities/fba-fuze/

I would suggest you remove the Me.Requery and move the code to the BeforeInsert event and see what happens.

Cheers,

[Thomasso]

Thanks for the suggestions, BeforeInsert event did not help. I did found out a temporary workaround that works for me. I might come back to this issue later.