Not that I understand everything -- <gripe> need better Automated Row Fetch (ARF) documentation </gripe>, but I now have CREATE, QUERY, and DELETE working and I can choose to clear the form (or NOT). And I don't claim this is the best or simplest or most-effecient or most straight-forward approach. It's one virtue is that it does seem to work.
I need to be careful state that this involves both Automated Row Processing (ARP) -- which enables INSERT, UPDATE and DELETE. And Automated Row Fetch (ARF) -- which enables SELECT. ARF is found under Page Rendering and ARP is found under page processing. As we have seen, the two are -- or can be -- interdependent. For example, I believe the error I talk about next is caused by ARF trying to fetch the record just deleted by ARP.
Starting from the situation of 1) having the values retained on the screen when CREATE is clicked and the record is added and 2) getting a database error after a DELETE of a row, A solution is the "reset" processes. Under Page Rendering add a "reset page" process to: "Clear Cache for all Items on Pages (PageID,PageID,PageID)" and indicate the page with the database operations and under Conditions: When Button Pressed: choose CANCEL, CREATE, DELETE, QUERY or SAVE. I made two of these - one for CREATE and one for DELETE. To cover both buttons, you need two processes as you can only indicate one button per process.
Now the form (screen) is cleared after the CREATE (by process 1). And since it's also cleared after the DELETE (by process 2), there is no Database error on DELETE. (The source of that error was ARF trying to retrieve the deleted record because the PK field had the "just deleted" key value. This nulls it and I have ARF set not to attempt a fetch when the key field is null.
So, how about that?
Burleson has an example here which adds more detail than I recall seeing before. http://books.google.com/books?id=S-e1INFVAnoC&pg=PA156&lpg=PA156&dq=APEX+set+memory+cache+on+fetch&source=bl&ots=-wkQMebbBq&sig=xzByeKyyWcOzhkbXoiDUS5x9mkY&hl=en&sa=X&ei=Zrr1UOmRHqiv2QWFtICACQ&sqi=2&ved=0CD8Q6AEwAg#v=onepage&q=APEX%20set%20memory%20cache%20on%20fetch&f=false
I think the DML Fetch Mode settings he shows are new to the ARP screen in 4.2. I don't see them in 4.1.