Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Apply to all in a query (2002)

    Hi,

    I have a form that is filtered by a query. The query shows all invoices between a date range. This is working fine. The user wants to be able to click a button that applies payment to all invoices that in the query with one click. Although I've advised that this is not a great way to do AR and is more likely to cause errors in payment allocations (as in items paid that aren't etc.) they insist.

    I have place a cmd button on the form with the the code:

    Me.AmountAllocated = Me.Total_Billed

    This works fine with the exception that it only applies it to the first records in the query. I need it to apply it to all records in the query.

    The form properties are set to cycle = all records, default view = continuous forms, allow additions = no.

    Thanks,
    Leesha

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

    Re: Apply to all in a query (2002)

    Me.AmountAllocated refers to the value of AmountAllocated in the current record only, so if you change it, you will only change it in the current record (the first one if you haven't moved to another record yet). You will have to execute an update query to update all records. Make a backup copy of your database before trying the following.

    Create a new query based on the query that acts as record source of the form.
    Select Query | Update Query.
    Add AmountAllocated to the query grid.
    In the 'Update to' row of the AmountAllocated column, enter [Total_Billed]
    Save this query as qryApplyPayment.

    Change the On Click code of your command button to

    DoCmd.OpenQuery "qryApplyPayment"

    When you click the button, you'll be warned that you are about to execute an action query, then that you're about to update n records. This is useful during testing - it will give you an idea of what is happening. But you probably won't want this in the "production" version. You can turn off the warnings as follows:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryApplyPayment"
    DoCmd.SetWarnings True

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply to all in a query (2002)

    Hi Hans,

    I haven't tried this yet but in reading it and figuring out the rationale behind what you have me doing I can see how it should do what I need. Thanks! I particularly like the code for the warnings.

    Thanks again,
    Leesha

Posting Permissions

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