Link to home
Start Free TrialLog in
Avatar of splashdoggie
splashdoggie

asked on

Access Form Text Box/Combo Box Expressions

I am a newbie trying to create a form in Access based upon the following table/fields:

TABLE=Data
FIELD1=Carrier_Name
FIELD2=Total_Receivable
FIELD3=Total_Weight
FIELD4=Close_Out_Date

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



ASKER CERTIFIED SOLUTION
Avatar of wiswalld
wiswalld
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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:

Forms!YourFormName.YourWeekEndingFieldName
Then requery your form on the after update event of your combobox.

-a
Avatar of splashdoggie
splashdoggie

ASKER

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
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!!
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.
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.
Thank you, that worked.