Access Form Text Box/Combo Box Expressions

Posted on 2007-07-30
Last Modified: 2013-11-28
I am a newbie trying to create a form in Access based upon the following table/fields:


I've created a combo box and a DISTINCT command that allows me to choose a Week Ending date for the last two years. When I choose a week ending date in the combo box, I want the form to populate each CarrierName (once), the carrier's TotalCost, and each carrier's TotalWeight for that week ending date. I was able to do this in the reports but can't figure it out in the forms.  

At the bottom of the reports I also had totals and frequencies. To count the number of times each carrier had a shipment that week ending date I used Count(DATA.CLOSE_OUT_DATE) AS FREQUENCY, to sum the totals I used Sum(DATA.TOTAL_RECEIVABLE) AS [TOTAL COST], to sum the weight I used Sum(DATA.TOTAL_WEIGHT) AS [TOTAL WEIGHT], and to get an average of weight over cost I used [TOTAL COST]/[TOTAL WEIGHT]*100 AS [C/W].

If someone can guide me on how to populate a text box for each Carrier_Name (once), the carrier's Total_Receivable, and Total_Weight based upon the Close_Out_Date in the combo box I'd apprecaite it. Also, some guidance on the totals based upon the SQL above would also be apprecaited! I'm somewhat familiar with SQL, but I've never used expressions before and I think that's what I'm supposed to use to populate these fields.

Here's an example of the desired form:

Choose Week Ending Date:
6/19/2007    <--COMBO BOX DOWN DOWN MENU

CARRIER NAME           FREQUENCY               TOTAL COST            TOTAL WEIGHT    C/WT
Abc Trucking               2                                $1,600                        75,000                   $2.13
Xyz Express                51                              $69,790                      584,208                $11.95                          

GRAND TOTAL           53                              $71,390                       659,208                $10.82

Question by:splashdoggie
    LVL 13

    Accepted Solution

    You could accomplish this with a form witha subform on it. Put your combo box on the form and your fields in the subform. Then you could use a filter to show the records from the combobox.

    Me.[Subform].Form.Filter = "[weekending]='" & Me.combobox1& "'"

    but you may also have to include a second combobox for the carrier name if you want to filter using the week ending and the carrier name.

    Me.[Subform].Form.Filter = "[weekending]='" & Me.Combobox1& "'"
    Me.[Subform].Form.Filter = Me.[weekending].Form.Filter & " AND carriername Like '" & Me.Combobox2 & "*'"
    Me.[Subform].Form.FilterOn = True
    LVL 13

    Expert Comment

    The week ending date combo box needs to be unbound (no data source)

    The form data source should be your query. In your query, add this to the criteria for the week ending column:

    Then requery your form on the after update event of your combobox.


    Author Comment

    So would I use:

    Me.[Subform].Form.Filter = "[weekending]='" & Me.combobox1& "'"

    as the ROW SOURCE? And I guess I would have to substitute [Subform] for the actual subform name and instead of [weekending] use DATA.CLOSE_OUT_DATE since that's where the week ending date field is located?

    I sound like a total newbie, but where is this placed?
    Me.[Subform].Form.FilterOn = True

    Author Comment

    I've created a form with the combobox, and a subform (child14) but I'm unsure of how to link both together and allow the week ending date to filter the subform. Could you go into a little more detail as to how each would be coded, maybe step by step if possible?
    Thank you!!
    LVL 13

    Expert Comment

    You do not have to link the forms. The combobox or textbox, however you decide to use, will filter the subform for you.

    You will need a button next to your combobox and in the on click put the code.
    LVL 13

    Expert Comment

    you may also want another button to remove the filter for future filters

    Behind that button on click put

    Me.[Subform].Form.FilterOn = False

    Obviously replace subform with the name of your subform.

    Author Comment

    Thank you, that worked.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now