Consulting

Results 1 to 7 of 7

Thread: Execute Query of Form Record causing unwanted Requery of Subform

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location

    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?

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  5. #5
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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.

    temp1.jpg

    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
    Last edited by garyj; 02-06-2019 at 11:09 AM.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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

  7. #7
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    Thanks, for the help.
    Good good...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •