Running Sum on Form 5

perrymans · Aug 15, 2005

I have an unbound field that I want to count the number of records. This will be used to calculate the number of CAL licenses used.

I set the default value to 1 for the first box, but now I need to increment it by one for each record on the continuous form. I tried DMax but it doesn't seem to be working since I am using an unbound text box.

It would be acecptable for this to occur in the query, but I can't get it there either.

I want it to read

1
2
3
4
5

Not a total of all records.

Thanks. Sean.

Remou · Aug 15, 2005

Hi
Have you had a chance to see the FAQs in
Microsoft: Access Queries and JET SQL forum701?
There is a section on incremental values.

perrymans · Aug 15, 2005

They are no good. It skips numbers, starts at 11, and assigns the 1 to the empty record.

I want to apply the count to my sorted recordset.

There has to be a way.

Sean.

PHV · Aug 15, 2005

Any chance you could post the SQL code of your sorted recordset ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

perrymans · Aug 15, 2005

I sure can, although I don't see how it would make a difference.

Code:

SELECT Tbl_SoftwareCAL.SoftwareSubID, Tbl_SoftwareCAL.SoftwareID, Tbl_SoftwareCAL.LicenseNameID, Tbl_ListLicenseNames.LicenseName, Tbl_SoftwareCAL.LicenseType, Tbl_SoftwareCAL.CALNum, Tbl_SoftwareCAL.EmployeeID, Tbl_SoftwareCAL.Notes, Tbl_SoftwareCAL.Cost
FROM Tbl_ListLicenseNames INNER JOIN Tbl_SoftwareCAL ON Tbl_ListLicenseNames.LicenseNameID = Tbl_SoftwareCAL.LicenseNameID
ORDER BY Tbl_SoftwareCAL.SoftwareID, Tbl_SoftwareCAL.CALNum;

I would think it would be the same no matter how many fields were involved.

Sean.

ZmrAbdulla · Aug 15, 2005

If you need only count of records then
"Me.RecordSetClone.RecordCount" will bring the number of records for the current session. (Subform, filtered etc)
DCount("*","TableQueryName") will return number of records in the entire set.
Your textbox must be in the footer of the form.

Or you need to display record numbers then see download sample from microsoft.

http://support.microsoft.com/?kbid=233324

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

perrymans · Aug 15, 2005

Zameer,

I found a GetLineNumber function in the form sample that seems to be working thus far. It is pretty simple:

Code:

Function GetLineNumber(F As Form, KeyName As String, KeyValue)
   Dim rs As Object
   Dim CountLines As Integer

   On Error GoTo Err_GetLineNumber

   Set rs = F.Recordset.Clone

   ' Find the current record.
   Select Case rs.Fields(KeyName).Type
      ' Find using numeric data type key value?
      Case adSmallInt, adTinyInt, adBigInt, adInteger, adDecimal, adNumeric, adCurrency, adSingle, adDouble
         rs.FindFirst "[" & KeyName & "] = " & KeyValue
      ' Find using date data type key value?
      Case adDate
         rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
      ' Find using text data type key value?
      Case adChar, adVarChar
         rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
      Case Else
         MsgBox "ERROR: Invalid key field data type!"
         Exit Function
   End Select

   ' Loop backward, counting the lines.
   Do Until rs.BOF
      CountLines = CountLines + 1
      rs.MovePrevious
   Loop

Bye_GetLineNumber:
         ' Return the result.
         GetLineNumber = CountLines

         Exit Function

Err_GetLineNumber:
         CountLines = 0
         Resume Bye_GetLineNumber

End Function

The only problem is that when I begin to add a new record, it gives it a "1" in the count, but it is correctly calculated after you leave the record.

ANyone have any ideas how to correct or hide it during adding?

Thanks. Sean.

dRahme · Aug 15, 2005

If your table has an autonumber and you wish to increment, why not add an extra field using dcount in your form's recordsource and make your unbound textbox a bound text box:

CountRows: DCount("[CountsDue]","tblToCnts","[Counts]<=" & [Counts])

- [Counts] being the autonumber
- [CountsDue] being another field
- [CountRows] being the controlsource for your textbox

dRahme

perrymans · Aug 15, 2005

Sorry dRahme,

I don't understand what that is supposed to give me. It will number my recordset 1 through whatever?

Sean.

dRahme · Aug 15, 2005

Yes - It provides an incrementing number for your recordset and thus an incrementing number for your form.
30 records, you will see 1-30.

Added field in your recordset:

CountRows: DCount("[PickaField]","YourTable","[YourAutonumber]<=" & [YourAutonumber])

dRahme

CosmoKramer · Aug 15, 2005

On this web site:

http://easyweb.easynet.co.uk/~trevor/AccFAQ/queries.htm

click on this link: Sequence numbers on the fly (counter emulation)

It's pretty straight-forward, I've used it before in a query that a sequence number was desired....

Si hoc legere scis, nimis eruditionis habes

perrymans · Aug 15, 2005

dRahme,

It is skipping some numbers for some reason giving me the following.

CountRows
1
10
11
12
13
2
7
8
9

CosmoKramer, I have seen that same approach used in the FAQ, but it doesn't work well/ at all.

In the end, it looks like the GetLineNumber function from MS Form Sample is the best solution. It works and is dynamic when adding and removing records (eventually at least).

Thank you all. Sean.

dRahme · Aug 15, 2005

Hi, it should work. The row that you are counting does have to have data in it. If there are empty fields, it will not count those. I'd check the sort order too and try sorting on the autonumber field.

perrymans · Aug 15, 2005

The fields I was using were autonumbers, so no nulls. Also if it is dependent on the autonumber sequence (which can be broken by deleting records), I will have to pass.

Thank you though. Sean.

dRahme · Aug 15, 2005

No, a broken autonumber sequence will not affect the numbering. It just needs to be an incrementing sequence of numbers. I use this myself in several forms.

Try this:

CountRows: DCount("[Autonumber]","YourTable","[Autonumber]<=" & [Autonumber])

and then sort Ascending on Autonumber. Unless I am not fully understanding something re your recordset, this does work.

dRahme

ZmrAbdulla · Aug 15, 2005

dRahme,
I was trying your suggestion that works well in a complete set of query. However it fails on split records.
Example:
I have an Invoice form, recordset of it is a query. It should split by invoice.
Invoice 1
1 Item1 Price Qty Amount
2 Item2 Price Qty Amount
3 Item3 Price Qty Amount
..........

Invoice 2 supposed to be starting from 1 to... like below

1 Item1 Price Qty Amount
2 Item2 Price Qty Amount
3 Item3 Price Qty Amount

But it starts from the next number where the previous ended
Like below..

4 Item1 Price Qty Amount
5 Item2 Price Qty Amount
6 Item3 Price Qty Amount

Do you have any suggestion?

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

dRahme · Aug 16, 2005

Zameer, Counts Revisited.

The key to doing what you want to do is to join at least two table fields to get one unique value to reset the count.

This will demonstrate using two and three fields to break the count.

Create a table - tblInv
Fields:
ID - Autonumber
InvNo - Number
InvDesc - Text
InvPart - Text
InvDate - Date

Populate it with this:
InvNo InvDesc InvPart InvDate
1 Inv1 Item1 03/03/2005
1 Inv1 Item2 03/03/2005
1 Inv1 Item3 03/04/2004
1 Inv1 Item4 03/04/2005
2 Inv2 Item1 04/03/2005
2 Inv2 Item2 04/03/2005
2 Inv2 Item3 04/03/2005
3 Inv3 Item1 04/06/2005
3 Inv3 Item2 04/06/2005
3 Inv3 Item3 04/06/2005
3 Inv3 Item4 04/07/2005
3 Inv3 Item5 04/07/2005
3 Inv3 Item6 04/07/2005

The Query:

SELECT tblInv.InvNo, tblInv.InvDesc, tblInv.InvDate, DCount("InvPart","tblInv","[InvNo] & [InvDesc] = '" & [InvNo] & [InvDesc] & "' and ID <= " & [ID]) AS TheCount
FROM tblInv;

To further break on a date:

SELECT tblInv.InvNo, tblInv.InvDesc, tblInv.InvDate, DCount("InvPart","tblInv","[InvNo] & [InvDesc]& [InvDate] = '" & [InvNo] & [InvDesc] & [InvDate] & "' and ID <= " & [ID]) AS TheCount
FROM tblInv;

whoops, remember to preface with 'Val' for a number

Hope that helps,

dRahme

ZmrAbdulla · Aug 17, 2005

dRahme, I didn't have time to see how it works. It should be a perfect solution, may be a little change as I connect subtable with invoice id.

I will try and let you know later.
Thanks

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

pdldavis · Sep 13, 2005

dRahme, you ought to make that last reply a FAQ. Nice post and solves my current problem.

Thanks, Dan

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Running Sum on Form 5

IS-IT--Management

Technical User

IS-IT--Management

MIS

IS-IT--Management

Technical User

IS-IT--Management

Technical User

IS-IT--Management

Technical User

Programmer

IS-IT--Management

Technical User

IS-IT--Management

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor