how to start a form/Query?

[AccessNewby]

Thank you for viewing my post. Please see attachment.

I have a historical data table and I'm trying to create a form that based on my selection of dates it will return a summary for each category. When I created a query it's not in a nice format and when I created in form i'm not able to access the data. Help....Do you know if I need a query or form or both?

Thank you in advance,

AccessNewby

 

[burrcm]

You would generally base a form on a query. How do you want to display/summarize your data? You can use the query wiz to do this. I have created a query which summarizes your data by month as an example.

Chris B

 

[AccessNewby]

Thank you for your assistance. I was looking for something a little different where a user can select a date parameters and it would calculate the sum within that date range. Thank you again.

 

[klynch0803]

How to Start a form/Query?

I would suggest creating a query with the results you want first.

I would then on the form you want add the Query as a subform linking the date on the Drop Down of your main form to the Date in the subform.

If I'm correct (With a little more detail but rather simple) this should finlter your dates if you do it properly.. See this code below you can modify to your forms possibly or check out the link I added.

This Code is courtesy of DevAshish from The Access Web (MVPS.org).


(Q) I would be like to be able to have my form automatically go to a particular record when I click a row in a list box or select a particular value in a combo box. How do I do this?

(A) If you have the ID column as the bound column in your listbox/combobox, you can put the following code in the AfterUpate event of the control:

CAUTION: Make sure you have read about the Bookmark Bug.

'****** Code Start *********
'Move to the record selected in the control Me.RecordsetClone.Findfirst "[ID] = " & Me![ComboOrListboxName] Me.Bookmark = Me.RecordSetClone.Bookmark
'******* Code End *********



http://www.mvps.org/access/forms/index.html

 

[AccessNewby]

Thank you for your reply. Please see the attachment. I'm very close to having what I want. I created a parameter query, where it allows me to select the start date and the end date, and i'm in the process of creating a form for this query. I don't know what I need to do to get the numbers to populate into this form.

I'll try out your suggestion right now. I'm sorry but I don't understand much (at all) about codes? VBA but i'm learning.


Thank you.
AccessNewby

 

[klynch0803]

I wil look at it..

Idea 1:

You could use that paramiter Query in your process and add a Start Date and End Date and End Date to your Main Form and then Maybe have an Update button that will update the data in the subform based on those dates.

Idea 2: same as above but it your eventProcedure would be under On_Change for the End Date and it would automatically refresh the subform.

 

[klynch0803]

Thank you for viewing my post. Please see attachment.

I have a historical data table and I'm trying to create a form that based on my selection of dates it will return a summary for each category. When I created a query it's not in a nice format and when I created in form i'm not able to access the data. Help....Do you know if I need a query or form or both?

Thank you in advance,

AccessNewby

Ok now I'm looking at your database creating a form that will show the Summary but I'm unclear what you want to display a Grand Total Summary of each column in Query or a detailed list of Grand Totals by Date?

 

[AccessNewby]

I've just revised my form. Please see attachment, it makes much more sense this time. Ideally, I would like to form to have two columns (one column for Volume and one column for dollar amount) for some reason the cells were not aligned correctly on the form.

parameters for Begin date and End date
Column 1, Column 2
P_Volume, P_Dollars(MM)
H_Dollar Volume, H_Dollar Dollar (MM)
R_Volume, R_Dollar (MM) etc.

The bottom of the first column is the sum of all the volumes
The bottom of the second column is the sum of all the dollars

Thank you klynch0803 sooooo much for your time and effort.

AccessNewby

 

[klynch0803]

Looks like you have what you were looking for in general.

Why dont you maybe redesign the form so it is a default background and see if you can change pages by using the Calender you have on your form and have it switch automatically to that specific date by using the filters to fancy it up?

Or add Next and Previous Buttons and a Close Form Button to go to a Main Menu.