Running Sum

[Guest]

Hi everyone, there is Running Sum control in Report. Does anyone know is there a the same for form? Thanks

 

[Tom Wickerath]

Hi Liza,

You can download a sample Forms database from Microsoft:

Access 2000: http://support.microsoft.com/?id=233324
Access 97: http://support.microsoft.com/?id=175066

The third sample form is titled "How to create a running sum". They use the domain aggregate
function DSum to create the running sum. You can also go to this KB article directly:
http://support.microsoft.com/?id=210495

Tom
_____________________________________


Hi everyone, there is Running Sum control in Report. Does anyone know is there a the same for
form? Thanks

 

[Guest]

Thanks but it is not what I am looking for. In Access report, I can define a unbound control and using the running sum to make it as a line number. for example when I print a invoice, I do not know how many line items. So this running sum control can start from 1. .. 2. ... 3. .. 4. .. until end of the line items
I want to do the same in a subform so that there will be a line number to each row.

 

[Arvin Meyer]

Thanks but it is not what I am looking for. In Access report, I can

define a unbound control and using the running sum to make it as a line
number. for example when I print a invoice, I do not know how many line
items. So this running sum control can start from 1. .. 2. ... 3. .. 4.
... until end of the line items.

I want to do the same in a subform so that there will be a line number to

each row.

This will work where LineNumber is the field in the subform table, txtID is
the Link Field control in the Main and subform and txtLineNum is the textbox
in the subform which holds the LineNumber field.

Private Sub Form_Current()
On Error Resume Next
Dim x As Integer
x = DMax("LineNumber", "TableName", "ID =" & Forms!MyForm!txtID)

If IsNull(x) Then
Me!txtLineNum.DefaultValue = 1
Else
Me!txtLineNum.DefaultValue = x + 1
End If

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks. I read the Access Help and found that the first arg must be a field in a table or query. I think the problem is I don't have that field. I put a textbox in front of each row of my subform. I am not familiar with Access so if I mis-understand you, please explain again.

 

[Guest]

Hi Arvin, I don't understand the third arg in DMax. Appreciate some explanation. Thanks

 

[Guest]

Hi, I have found that the x will not change if the sub in On Current event. We use codes to assign a query to the Control Source and use Select .. Case to match the controls of the subfrom and fields of the query. So the On Current event takes place once only. Therefore the Line Number does not change. Please advise what I have done wrong.

 

[Arvin Meyer]

Hi, I have found that the x will not change if the sub in On Current

event. We use codes to assign a query to the Control Source and use Select
... Case to match the controls of the subfrom and fields of the query. So
the On Current event takes place once only. Therefore the Line Number does
not change. Please advise what I have done wrong.

LineNumber must be a bound field in the table underlying the subform's
recordsource.

You do not need (or want) to use a Case statement as there are only 2
possible cases: there is a value or there is not.

The third argument in DMax is the "where" clause which limits the record to
that particular key. We don't want to consecutively number all the records
in the table, we want the numbers to start over with each foreign key.

I use the DefaultValue property, which occurs once with each OnCurrent
event. This allows me to manually change the line numbers later, if I want
to re-order the records in a form or report.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I believe your suggestion and explanation are great. Please allow me to ask some dummy question as I am not familiar with Access. It seems subform is very difficult to handle by codes

What you mean is when I build a table I should have this Line Number field at the very beginning? And the third argument "ID=" means foreign key (not primary key) of the table?

My another dummy question is does subform only accept query as Control Source? What if I have a recordsets, can I write (insert) the record of the recordsets one by one to the rows of subform? The reason that I want to do it all by codes is I want a full control of my application. Query will update the table every time when a form is closed or navigating to another record

Since my main and subform are unbound forms and have not link to each other, I need to use Select .. Case to assign the Control Source to dispplay the data. I am not sure if mine is a correct approach. Please advise.

 

[Tom Wickerath]

Hi Liza,

Does this KB article help?

How to Display Line Numbers on Subform Records
http://support.microsoft.com/?id=210340

Tom
_______________________________________


I believe your suggestion and explanation are great. Please allow me to ask some dummy question
as I am not familiar with Access. It seems subform is very difficult to handle by codes.

What you mean is when I build a table I should have this Line Number field at the very beginning?
And the third argument "ID=" means foreign key (not primary key) of the table?

My another dummy question is does subform only accept query as Control Source? What if I have a
recordsets, can I write (insert) the record of the recordsets one by one to the rows of subform?
The reason that I want to do it all by codes is I want a full control of my application. Query
will update the table every time when a form is closed or navigating to another record.

Since my main and subform are unbound forms and have not link to each other, I need to use Select
... Case to assign the Control Source to dispplay the data. I am not sure if mine is a correct
approach. Please advise.

 

[Guest]

Hi Tom, the function is exactly what I want. I am studying it since my main and suform are unbound forms. BTW, is there any ADO version?

 

[Tom Wickerath]

Hi Liza,

My knowledge of ADO is pretty limited. However, this article on MSDN may be helpful:

Porting DAO Code to ADO with the Microsoft Jet Provider, by Alyssa Henry
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp?frame=true


Tom
____________________________________


Hi Tom, the function is exactly what I want. I am studying it since my main and suform are
unbound forms. BTW, is there any ADO version?