Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field not Updating Immediately (Access 2002)

    I am new to Access and have been given project to build a Little League Registration database. I have been successful in building the database and forms but have one problem. I have a main form where player information is posted. I have put a command button that calls another database called "payments". Users input registration and other fees paid by parents. When they close the payments window there is a field on main form that I want to populate a field called "Total Payments". What is happening is the field Total payments is not updating immediately. If I close the form and reopen then the "Payments Made" field is updated. I have tried various command on the "After Update" field but no luck.

    Any idea why this is not updating? Also this command I have in the "Total Payments" field on main form.

    =IIf(IsNull([MemberID]),0,DSum("[PaymentAmount]","Payments","[MemberID]=" & [MemberID])

    MemberID = autonum ID for each record/player
    PaymentAmount = field where payments amounts are entered
    Payments = payment table

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field not Updating Immediately (Access 2002)

    Welcome to Woody's Lounge!

    If you open the payments form as a dialog form, the code behind the command button will wait until the user closes it. You can then force calculated text boxes such as Total Payments to be updated by calling Me.Recalc (Me refers to the form running the code, i.e. the main form). The code could look like this, with the appropriate names substituted:

    Private Sub cmdPayments_Click()
    DoCmd.OpenForm "frmPayments", , , "[MemberID]=" & [MemberID], , acDialog
    Me.Recalc
    End Sub

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field not Updating Immediately (Access 2002)

    Thanks Hans for quick response. Does this code what your are talking about?

    Private Sub Payments_Click()
    On Error GoTo Err_Payments_Click
    If IsNull(Me![MemberID]) Then
    MsgBox "Enter member information before viewing payments form."
    Else
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.OpenForm "Payments", , , "[Payments]![MemberID] = Forms![VLL_Registrations]![MemberID]"
    End If

    Exit_Payments_Click:
    Exit Sub

    Err_Payments_Click:
    MsgBox Err.Description
    Resume Exit_Payments_Click
    End Sub

    Rick

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field not Updating Immediately (Access 2002)

    Hi Rick,

    Try this slight modification:
    <code>
    Private Sub Payments_Click()
    On Error GoTo Err_Payments_Click

    If IsNull(Me![MemberID]) Then
    MsgBox "Enter member information before viewing payments form."
    Else
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    DoCmd.OpenForm "Payments", , , "[MemberID] = " & [MemberID], , acDialog
    Me.Recalc
    End If

    Exit_Payments_Click:
    Exit Sub

    Err_Payments_Click:
    MsgBox Err.Description
    Resume Exit_Payments_Click
    End Sub
    </code>
    I replaced the DoCmd.DoMenuItem ... (which is Access 95 code) with RunCommand acCmdSaveRecord, and added a test to save the record only if it has been edited (is "Dirty"). The Payments form is being opened as a dialog, and the main form is recalculated after the user has closed the Payments form.

    (I used <!t>[tab]<!/t> tags to preserve indentation and <!t>
    Code:
    <!/t> and
    <!/t> tags to display the code in a fixed width font. See <!help=19>Help 19<!/help>.)

Posting Permissions

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