Access Form Text Box/Combo Box Expressions

Posted on 2007-07-30
Medium Priority
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
  • 3
  • 3
LVL 13

Accepted Solution

wiswalld earned 2000 total points
ID: 19596603
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

ID: 19596619
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

ID: 19596686
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.


Author Comment

ID: 19596777
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

ID: 19596849
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

ID: 19596859
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

ID: 19605878
Thank you, that worked.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

839 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