Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating combo box in subfrom (Access 2002)

    I have a receive payments form frmReceivePayments where I have a combo box, SelCompanyID, to select the CustomerID for the payment and store it in the payment record. I have a subfrom frmPaidInvoices where I apply the payment received to specific invoices. On this form I have a combo box, SelInvoice, to select the invoice number to apply payment to. I would like to limit the SelInvoice list to just invoices for the selected CustomerID. I have tried to update the list using the after update property of the CustomerID to requery the SelInvoice control but have been unable to get it to update the combo box list.

    The code I have tried is:
    Me!frmPaidInvoices.Form!SelInvoice.Requery
    I would appreciate any help or advice.
    Thanks

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

    Re: Updating combo box in subfrom (Access 2002)

    What is the rowsource for the combobox you're trying to filter? Unless that rowsource refers to the selected CustomerID, you won't get the result you want.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating combo box in subfrom (Access 2002)

    Charlotte,
    The row source is a query named qrySelInvoice
    there are three fields, InvoiceID, a calculated field Bal:[Total Cost]-[PaymentReceived], and CustomerID
    the Criteria for the Bal field is <>0
    and for CustomerId it is Forms![frmReceive Payments]![SelCompanyID]
    Thanks,
    Jim

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

    Re: Updating combo box in subfrom (Access 2002)

    Post the SQL of the query rather than just the criteria line. It's hard to guess what might be causing the problem otherwise.

    One thing I've seen in Access 2000 and later, is that the query engine doesn't always like comboboxes as criteria sources. Sometimes the workaround is to create a hidden textbox on the form to hold the value of the combobox and refer to that textbox in your query instead.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating combo box in subfrom (Access 2002)

    Charlotte,
    Following is the sql for the query qrySelInvoice

    SELECT tblOrders.InvoiceNo, [Total Cost]-[PaymentReceived] AS Bal
    FROM tblOrders
    WHERE ((([Total Cost]-[PaymentReceived])<>0) AND ((tblOrders.CustomerID)=[Forms]![frmReceive Payments]![SelCompanyID]));
    I hope this helps you understand what I have done.
    Jim

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

    Re: Updating combo box in subfrom (Access 2002)

    In an expression like [Total Cost]-[PaymentReceived], I would suggest you fully reference the fields like this:

    [tblOrders].[Total Cost]-[tblOrders].[PaymentReceived]

    That helps the query engine keep its brains straight, although it isn't necessarily required in a single table query. I'd also suggest wrapping the form reference in a Val() function to force it to evaluate the value in the combobox. As I mentioned before, the query engine in A2k and later sometimes balks at using comboboxes like this, so you have to nudge it.

    AND ((tblOrders.CustomerID)=<font color=red>Val(</font color=red>[Forms]![frmReceive Payments]![SelCompanyID]<font color=red>)</font color=red>));
    Charlotte

Posting Permissions

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