Sprinks,
I hope this helps you:
ACCOUNT Main Form
SELECT Financial_Master.FileNumber, Financial_Master.FinancialInstitution,
Financial_Master.AccountNumber, Financial_Master.Date_Open,
Financial_Master.Date_Close, Financial_Master.SID, [ClassifyFinancialEntry
Query].TypeOfAccount, [Profile_Master Query].LastName, [Profile_Master
Query].FirstName FROM [Profile_Master Query] INNER JOIN (Financial_Master
INNER JOIN [ClassifyFinancialEntry Query] ON Financial_Master.AccountNumber =
[ClassifyFinancialEntry Query].AccountNumber) ON [Profile_Master Query].SID =
FinancialAccount_Master.SID;
DATA SubForm
SELECT [Fin DATA].AccountNumber, [Fin DATA].MID, [Fin DATA].TypeOfAccount,
[Fin DATA].Date AS Bank_Date, [Fin DATA].[Deposits/Credits] AS [Amount],
Financial_Master.FileNumber
FROM [Fin DATA] INNER JOIN FinancialAccount_Master ON [Fin
DATA].AccountNumber = Financial_Master.AccountNumber
ORDER BY [Fin DATA].Date;
MONTHVSYEAR SubForm
SELECT DISTINCTROW [Fin DATA].AccountNumber, [Fin DATA].TypeOfAccount,
Format$([Fin DATA].[Bank_Date],'mmmm yyyy') AS [Bank_Date By Month], Sum([Fin
DATA].[Amount]) AS [Sum Of Amount], Count([Fin DATA].[Amount]) AS
[CountOfAmount], Format([Bank_date],"yyyy") AS [Year],
Format([Bank_Date],"mm") AS [Month]
FROM [Fin DATA]
GROUP BY [Fin DATA].AccountNumber, [Fin DATA].TypeOfAccount, Format$([Fin
DATA].[Bank_Date],'mmmm yyyy'), Format([Bank_date],"yyyy"),
Format([Bank_Date],"mm"), Year([Fin DATA].[Bank_Date])*12+DatePart('m',[Fin
DATA].[Bank_Date])-1
ORDER BY Format([Bank_date],"yyyy"), Format([Bank_Date],"mm");
TOTAL SubForm
SELECT [Fin MonthvsYear].AccountNumber, [Fin MonthvsYear].TypeOfAccount,
Sum([Fin MonthvsYear].[Sum Of Amount]) AS [SumOfSum Of Amount], Sum([Fin
MonthvsYear].[CountOfAmount]) AS [SumOfCountOfAmount]
FROM [Fin MonthvsYear]
GROUP BY [Fin MonthvsYear].AccountNumber, [Fin MonthvsYear].TypeOfAccount;
Sprinks said:
Ritram,
Please post the SQL of the RecordSources of the main and subforms. If they
are a named query, open the query in Design View, select View, SQL, and cut
and paste.
Sprinks
RITRAM said:
Sprinks,
The following is the information that you requested.
Main Form: Fin Account_Header
Subform Controls: AccountMaster;TypeOfAccount (LinkMasterFields)
AccountMaster;TypeOfAccount (LinkChildFields)
Subform Themselves: Fin DATA (DATA SubForm)
Fin MonthvsYear (MonthvsYear SubForm)
Fin Total (TOTAL SubForm)
As it is shown the subforms are link to the main form by AccountMaster and
TypeOfAccount. The record source information is derived from a table called
Finance. I hope this informations helps you to help me.
Thanks
Sprinks said:
Ritram,
I'm not sure how to further help without knowing more about your
application. Please post the following properties:
Main Form: RecordSource
Subform Controls: LinkMasterFields, LinkChildFields, Name
Subforms Themselves: RecordSource
Please also post the expressions of any calculations you are doing on the
subforms.
If any of the RecordSources are a named query, please post their SQL.
Sprinks
:
Sprinks,
Thanks for your help. I wrote "[Forms]![Account][Form].Recalc" to the after
update event of amount. It is working now, but I have a new problem. The
thing is that everytime it recalcs it goes back to the beginning of the DATA
form. How can I fix this problem?
Another question , when you wrote Me!Subform.Form.Recalc....How was I
suppose to write this? I wrote "Me!MonthvsYear.Form.Recalc" and it did not
work for me.
:
Ritram,
If the totals are defined within these other forms, do a recalc:
In the AfterUpdate event of the amount field:
Me!MySubform.Form.Recalc
Sprinks
:
Sprinks,
Thanks for your advize, but the "Data" form, "MonthvsYear" form , "TOTAL"
form and "Account" form are all queries of a Table name Finance.
I guess I did not explain myself properly. What I am trying to do is when I
enter an amount with a corresponding date to the "DATA" form it should
automatically refresh the "TOTAL" form and the "MonthvsYear" form to update
the data reflected on this forms. This is what I can not do. If you or
anybody can help me on this I really need this to finish my program.
PS. Please give me the information as detailed as possible.
Thanks in advance.
:
RITRAM,
It's not entirely clear from your description as to what you're trying to
do, but if you're trying to create monthly and overall totals, you don't need
the second two tables at all. Simply create a Totals query, Grouping By the
account and Summing on the Amount in the latter case. For monthly totals,
Group By each unique combination of the month and year, using the DatePart()
function (see VBA Help on its syntax).
Also, be careful not to use the name '"Date" as a field name--Date is a
reserved word in Access. Using them as field names can cause unpredictable
behavior.
Sprinks
:
I am working on a check and balance program and I have an issue on one of my
forms. I have a main form (name "Account") with three subforms ("Data",
"MonthsYear", and "Total"). All three subforms are conected to the main form
by the account number. The subform "Data" is a continues form and is the one
that I use to enter the amount and date. As you can guess by the name of the
other subforms (single forms) I need the other subforms to update each time I
enter a data to the "Data" subform. Please I need help on this one. I have
read all the other forums but if I put a Me. requery to the "Data" form it
starts requering like crazy and gives me an error.
