Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform Change Isn't Reflected on Main Form (Access 97)

    I hope I can explain this adequately with enough detail to make it clear. If you need more information, please ask.

    I have a form with several subforms. The user will be adding/modifying data on the subforms. On the main form, I have text boxes that show sum values based on the data in the subforms. The data source for the text boxes is a query. When I first open the form, the sum values are correct. But when data in the subform is changed, the sums are not updated. Forcing a requery doesn't seem to work. Moving between records doesn't work. The only way I've been able to update the values is to close and reopen the form.

    Thanks for any direction you can provide. Again, if you need more information, please let me know.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    I suppose you have some code to calculate the values of the textboxes.
    In the AfterUpdates of the subforms, do a call to rerun the code that you use when you open the form.
    If the code is in the on open event of the main form, isolate it in a apart function, and call that function also in the on open of the main form.
    Francois

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    Actually, there is no code. The values come from the data source for the table (two queries). This is the SQL for the data source for the form:

    SELECT tblAttendees.BadgeID, tblAttendees.LName, tblAttendees.FName, tblAttendees.Address, tblAttendees.City, tblAttendees.State, tblAttendees.Zip, tblAttendees.Country, tblAttendees.EMail, tblAttendees.Phone, tblAttendees.BName, tblAttendees.SDate, tblAttendees.EarlyReg, tblAttendees.Donation, tblAttendees.Panel, tblAttendees.Interpreter, tblAttendees.Wheelchair, tblAttendees.SpOther, qryTotalsDue.RegFee, qryTotalsDue.Souvenirs, qryTotalsDue.Tours, qryTotalsDue.Meals, qryTotalsDue.TotalDue
    FROM tblAttendees INNER JOIN qryTotalsDue ON tblAttendees.BadgeID = qryTotalsDue.BadgeID
    ORDER BY tblAttendees.LName, tblAttendees.FName;

    The values in qryTotalsDue come from a series of queries similar to this one:

    SELECT tblSouvenirDetails.BadgeID, Sum([amount]*[qty]) AS STotal
    FROM tblSouvenirs INNER JOIN tblSouvenirDetails ON tblSouvenirs.SouvenirID = tblSouvenirDetails.SouvenirID
    GROUP BY tblSouvenirDetails.BadgeID;

    Doing a requery seems to the the solution, but it doesn't work.

    --Karyl

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    When you do a requery, are you sure that you requery the main form, and not the subforms ?
    Francois

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    That was it--or close enough that it allowed me to fix it! I knew it had to be something simple. Many, MANY thanks!

    --Karyl

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    New Problem. The requery works, the data updates exactly the way I want it to. However, after the requery, the form jumps to the first record in the recordset instead of staying on the current record. Is there an easy way to stop this from happening? Or a hard way? Any way?

    --Karyl

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    This is normal behavior for a requery. What you have to do is store the key for the current record in a variable before the requery and then use that value to find that record again after the requery.
    Charlotte

  8. #8
    New Lounger
    Join Date
    May 2002
    Location
    Denver, CO
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    Karyl, if you are trying to goto the current record in a subform, you'll need to do an extra bit of coding in VBA or a macro. I've attached the MSDN article that explains this - it worked for me on one of my projects.

    Larry Moore
    Attached Files Attached Files

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    As with the crash of the lounge my previous message has been lost and MarK Liquorman pointed me that a findfirst would be better than the AboslutePosition (wich I agree), here a new peace of code to do it with findfirst.
    <pre>Dim position As Long
    position = Me.nameid
    Me.Requery
    Me.Recordset.FindFirst "[NameID] = " & position</pre>

    This assume that you replace NameID with a unique field of your form and the field is numeric.
    If the unique field is a text field, use
    <pre>Dim position As String
    position = Me.nameid
    Me.Requery
    Me.Recordset.FindFirst "[NameID] = '" & position & "'"</pre>

    Francois

  10. #10
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    Sorry I didn't get back to you sooner. I tried the absolute position suggestion last Sunday, but I couldn't get it to work. Today was the first chance I had to get back to it. Here is the code I have in my subform. I get "Run-time error '2465' - Application-defined or object-defined error" on the last line of code.

    Private Sub Form_AfterUpdate()
    Dim lngPosition As Long
    lngPosition = Forms!frmRegistrationForm.BadgeID
    Forms!frmRegistrationForm.Requery
    Forms!frmRegistrationForm.RecordsetClone.FindFirst "[BadgeID] = " & lngPosition
    End Sub

    Because I'm running the code from a subform, I can't use "Me" to identify the object. Thanks for your help!

    --Karyl

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    This may or may not simplify things, when requerying main form from subform, I use this code for subform's AfterUpdate event to return to same record on main form (this example unique field is numerical):

    <pre>Private Sub Form_AfterUpdate()

    Dim lngID As Long
    Dim frm As Form
    Dim rst As DAO.Recordset

    Set frm = Me.Parent.Form
    Set rst = frm.RecordsetClone

    With frm
    lngID = .BadgeID
    .Requery
    rst.FindFirst "[BadgeID] = " & lngID
    .Bookmark = rst.Bookmark
    End With

    Set frm = Nothing
    Set rst = Nothing

    End Sub
    </pre>

    Using object variable (frm) set to subform's parent (main form) form property simplifies syntax, you don't have to specify name of main form, etc.
    HTH

  12. #12
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Change Isn't Reflected on Main Form (Access 97)

    Many, many thanks! The code works exactly as I was trying to get the other to perform. I've been away from Access programming for a couple years, and it is like swimming upstream trying to remember how to do some of this stuff. I guess it's time to get the Access books out and start a refresher course! I still get such a thrill when it WORKS, however, that it has really been fun to get back into it again.

    --Karyl

Posting Permissions

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