Execute Query of Form Record causing unwanted Requery of Subform

I have a continuous form with table Donations. Each donation can be broken down to one or more designated areas which I call ItemizedGifts. So when I click on a Donation record, the Current event causes the subform below to query for the list if ItemizedGifts. The total of the items must balance with the Donation, so I have a text field, Err, which shows a red 'X' when they do not balance, and is cleared when they do.

The check is made by comparing the Donation amount with: TotalItems = DSum("Amount", "ItemizedGifts", "RevBatchID = GetItems4Batch()")

I have followed the code step by step during run to know exactly what the problem is... but not how to fix it.

During entry of the items, the Amount is entered first. The update event of Amount triggers this:
Me.Dirty = False 'causes record to be saved so that the DSum in DoMath will include the immediate record
Form_Donations.DoMath

DoMath has a number of entries, but the offending one is here...
If MyMath <> 0 Then
CurrentDb.Execute "UPDATE Donations SET Error = 'X' WHERE RevBID = " & MyBatch
Else: CurrentDb.Execute "UPDATE Donations SET Error = '' WHERE RevBID = " & MyBatch
End If

Anyway, when the user is enters the record amount that balances MyMath the update query removes the 'X' in the main form record, and when the code ends, and the 'X' shows up, it requeries the subform and sends the focus to the first record. The user then needs to reset focus to finish entering that line. I do not believe any of my code calls for the requery, so it must be internal on updating information on the main form. When removing the Execute query code, the subform works fine. So it is certainly causing the issue.

I could wait until the end of the record entry to update, but would prefer a fix when the amount is entered.

Any ideas?

Why use a query to update the "X", why not just set it using VBA?
You can also use a VBA Docmd to save the record as well.

Hmm, is that not what I did with this? ----> CurrentDb.Execute "UPDATE Donations SET Error = 'X' WHERE RevBID = " & MyBatch

I considered setting it as shown below, but thought the other saved time.

Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM Donations WHERE (([RevBatchID]) = (GetMyBatch()));")
rst.Edit
rst![Err] = "X"
rst.Update
Set rst = Nothing
db.Close


What way would you set it using vba that wouldn't trigger a requery?

Gary

Gary, you can manipulate the sub form directly from the mainform.
As I am not sure how the 2 are linked I am not sure how simple the code will be, but in it's simplest form it looks like this

Me.subformname.Err ="X"

where subformname is the actual name of your subform, however depending on how you have designed the forms it may be necessary to manipulate the subform's recordset to identify the correct record, to do so you would need to place the Focus on the subform first.

Can you show a screenshot of the Main & Sub forms?

Sorry, been away at meetings and on other priorities.

I can do so, but its the other way around. When I am in the subform, I want to manipulate the data in the main form. Me.Form_Donations.Err = "X". I am pretty sure I tried that first, but tried several things since so can't recall. I will try again. Here is the screenshot.

Attachment 23705

The circle shows current entry on subform. I drew the arrow to show two places that are edited as soon as the user touches the <Tab> key. The 1.00 amount will become 0.00 and that works. The "X" in the main form will become blank. And it works as well, but doing so causes the cursor to jump up to the top line, which indicates to me that a requery occurred in the subform.

Okay, so I got it to work. YAY!!!!

Private Sub Amount_AfterUpdate()

DoCmd.RunCommand acCmdSaveRecord

Form_Donations.MainFormMath

If MyMath = 0 Then Form_Donations.ErrBox = "" Else Form_Donations.ErrBox = "X"

Form_Donations.Dirty = False

End Sub

Before I had the MainFormMath set the X, because it needs to be there when the upper donation record is entered (no data below so out of balance). Math is done and the visual markers ('X' and outstanding amount) are set in red. So when entries were made in the subform, the afterupdate would trigger it to go back to the main form vba to redo the math. It was there that the amounts were being set, and for some reason manipulating the data by the textbox did not allow me to save the main record and while I worked on the rest of the subform, the main form was still in edit mode. Force saving caused code errors. So I tried the update query which changed the data directly, but forced a requery of the subform. I now added the line below the MainFormMath, which manipulates the data as you suggested, and it allows the save, and doesn't requery the subform. I am not sure why or how, but it works. Now I need to do a simple fix to create the 'X' in the mainform, but there are lots of ways to do that.

But on another topic, you will see I used the DoCmd to save the subform record while in the subform vba. How would I do the same for the Form_Donations while inside the subform?

Thanks so much for your help.

Gary

The easiest way would be to set the focus to the mainform and then do the docmd.
You are also supposed to be able to use

If Me.Dirty Then
Me.Dirty = False
End If

or possibly from the subform

If Me.Parent.Dirty Then
Me.Parent.Dirty = False
End If

Thanks, for the help.
Good good...