Running Sum (Line no.) in Form

[Sanjaylml]

How can I put a Running Sum (Line No.) in a form, as in the case of Report?

(Primary key i.e. Auto generate Number) does not solve my problem, as in my form, filter is used to display only selected records. I want to generate serial no. (running sum) at the time of entering new records, based on my filtered records in table.

Your help is solicited.

Thanks in advance.

[NeoPa]

The short answer is that you can't. There may be clever ways around this but in principle it doesn't work.
In your position I would capture the BeforeUpdate event and add a number in after checking the max number used in the table so far (DMax() will give you the value you need - plus one of course).

[FishVal]

Hi, there.

So basically you want to have a textbox/label with RecordNumber as in navigation bar. Am I right?

[Sanjaylml]

Hi, there.

So basically you want to have a textbox/label with RecordNumber as in navigation bar. Am I right?

Yes I want a text box / label with a record no.

[Sanjaylml]

The short answer is that you can't. There may be clever ways around this but in principle it doesn't work.
In your position I would capture the BeforeUpdate event and add a number in after checking the max number used in the table so far (DMax() will give you the value you need - plus one of course).

Kindly give me example how to use DMAX with an increment.

[FishVal]

Yes I want a text box / label with a record no.

Something like this.

Private Sub Form_Current()
 
    Dim varRecordNumber As Variant
 
    If Me.NewRecord Then
        varRecordNumber = "*"
    Else
        varRecordNumber = Me.Recordset.AbsolutePosition + 1
    End If
    Me.txtRecordNo = varRecordNumber 
 
End Sub
 

[NeoPa]

Kindly give me example how to use DMAX with an increment.

Dim lngRecNo as Long
 
lngRecNo = Nz(DMax("[RecordNumber]", "[YourTable]"), 0) + 1

[Sanjaylml]

Dim lngRecNo as Long
 
lngRecNo = Nz(DMax("[RecordNumber]", "[YourTable]"), 0) + 1

Thanks for your reply. Now, my program has slightly been changed. Now, I want automatically increment value in database, based on group(field). Is it possible? You can evident from the following table:

ID        Item             BillNo             Qty 
01       Aluminium     RSA-01          100
02       Aluminium     U/06007           50
01       Scrap            2003                50
01       Zinc              24/07-08           33
03       Aluminium     24242-AL         22
02       Scrap            AB-24            241

I am sure, you can understand what I want in the program.

Awaiting for reply.

Thanks in advance.

[NeoPa]

...
I am sure, you can understand what I want in the program.
...

Seems like misplaced confidence I'm afraid.
None of that made any sense to me at all :(

[FishVal]

Pass criteria to DMax function

lngRecNo = Nz(DMax("[RecordNumber]", "[YourTable]","[Item Name]='Aluminium'"), 0) + 1
 

[NeoPa]

Nice one Fish.
If that was the question that's a good answer (Even got the SQL quote chars right) :)

[FishVal]

Nice one Fish.
If that was the question that's a good answer (Even got the SQL quote chars right) :)

Glad it worked for you. Welcome to TSDN.

:-P

Nevermind. Just a .... joke [?]

[NeoPa]

:D
.

[Sanjaylml]

I am still confused. I have passed following code in Before Update() event but it did not work, in the way I want.

Dim lngRecNo As Long
lngRecNo = Nz(DMax("[LineNumber]", "[Receipt]", "[ItemID]=1"), 0) + 1

Please let me know where this code to be written whether in

a) Before Upadate() - as I wrote.
b) Before Insert in Form window
c) Deafult value of control

Also, I am afraid if another item is selected (Besides Aluminium), how it will work. For each ItemID, a separate code is required.

Please enlighten me.

Thanks in advance.

Pass criteria to DMax function

lngRecNo = Nz(DMax("[RecordNumber]", "[YourTable]","[Item Name]='Aluminium'"), 0) + 1
 

[FishVal]

Hi, there.

Sure filter being passed to DMax() may and should be dynamic.

In presumption your form control bound to [ItemID] table field has name [ItemID].

In BeforeUpdate event handler.

Dim lngRecNo As Long
lngRecNo = Nz(DMax("[LineNumber]", "[Receipt]", "[ItemID]=" & Me![ItemID]), 0) + 1
 

[NeoPa]

Let us know how you get on with this Sanjay.
Notice that the quotes (') are not required in this version as the [ItemID] is a numeric field.

[Sanjaylml]

Not working this command in the before update event. I have tried with text box but not getting value increased & showing "0"

I have gone though various articles about DMAX() but fail to find any solution.

Any solution ??????

Hi, there.

Sure filter being passed to DMax() may and should be dynamic.

In presumption your form control bound to [ItemID] table field has name [ItemID].

In BeforeUpdate event handler.

Dim lngRecNo As Long
lngRecNo = Nz(DMax("[LineNumber]", "[Receipt]", "[ItemID]=" & Me![ItemID]), 0) + 1
 

[NeoPa]

Sanjay,

Firstly please don't shout (all uppercase) and waste space on the page. This is annoying for people trying to read it (and ultimately trying to help you out).

Secondly, if you would like us to understand what your problem is then it would be a good idea to share your code with us.

[Sanjaylml]

As desired, I am giving below the code of the form, which will help you in understanding the exact requirement, which I am facing in auto increment based on criteria.

Option Compare Database
Private blnConfirmUndo As Boolean
Private blnConfirmDelete As Boolean
Private blnSaveButton As Boolean
Private lngDeleted As Long
 
Private Sub cmdAdd_Click()
    Me.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
    Me.ItemID.SetFocus
End Sub
 
Private Sub cmdDel_Click()
    blnConfirmDelete = False
    ResponseMSG = MsgBox(Prompt:="You are permanently deleting this record, are you sure?", Buttons:=vbYesNo)
    If ResponseMSG = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
        Me.Refresh
    End If
    blnConfirmDelete = True
End Sub
 
Private Sub cmdSave_Click()
Dim dbs As Database
Dim rcd As Recordset
Set dbs = CurrentDb
Set rcd = dbs.OpenRecordset("Receipt")
    If IsNull(Item) Then
        MsgBox ("Item is missing"), vbCritical
        DoCmd.GoToControl ("Item")
        Exit Sub
    End If
    If IsNull(Quantity) Then
        MsgBox ("Quantity is missing"), vbCritical
        DoCmd.GoToControl ("Quantity")
        Exit Sub
    End If
    If IsNull(BillNumber) Then
        MsgBox ("Bill Number is missing"), vbCritical
        DoCmd.GoToControl ("BillNumber")
        Exit Sub
    End If
    If IsNull(BillDate) Then
        MsgBox ("Bill Date is missing"), vbCritical
        DoCmd.GoToControl ("BillDate")
        Exit Sub
    End If
    If IsNull(ImporterID) Then
        MsgBox ("Party Name is missing"), vbCritical
        DoCmd.GoToControl ("Importerid")
        Exit Sub
    End If
    blnSaveButton = True
    DoCmd.RunCommand acCmdSaveRecord
    blnSaveButton = False
    Me.Receive_SubForm.Requery
    DoCmd.OpenQuery ("Update Receipt")
End Sub
 
Private Sub Exit_Click()
DoCmd.Quit
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngRecNo As Long
lngRecNo = Nz(DMax("[LineNumber]", "[Receipt]", "[ItemID]=" & Me![ItemID]), 0) + 1
End Sub
 
Private Sub ItemID_AfterUpdate()
    Me.cmdSave.Enabled = True
End Sub
 
Private Sub Receive_SubForm_Enter()
    If IsNull(Me![ItemID]) Then
        MsgBox "You must enter details before viewing records"
        Me.ItemID.SetFocus
    End If
Me.Refresh
End Sub
 
Private Sub Form_AfterUpdate()
    Me.AllowAdditions = False
    Form_Current
End Sub
 
Private Sub Form_Current()      
    If IsNull(Me![ItemID]) Then
        DoCmd.GoToControl "ItemID"
    End If
 
    Me.ItemID.SetFocus
 
    Dim blnNewRecord As Boolean
    blnNewRecord = Me.NewRecord
    Me.AllowAdditions = blnNewRecord
    'Me.AllowAdditions = False
    Me.cmdAdd.Enabled = Not blnNewRecord
    Me.cmdDel.Enabled = Not blnNewRecord
 
    Dim blnDirty As Boolean
    blnDirty = Me.Dirty
    Me.cmdSave.Enabled = blnDirty
End Sub
 
Private Sub Form_Dirty(Cancel As Integer)
    Me.cmdSave.Enabled = True
End Sub
 
Private Sub Form_Open(Cancel As Integer)
    DoCmd.SetWarnings Off
    DoCmd.Maximize
    blnConfirmUndo = True
    blnConfirmDelete = True
End Sub
 
Private Sub Head_DblClick(Cancel As Integer)
DoCmd.OpenForm ("Receivehd"), , , , , acDialog
Me![Head].Requery
End Sub
 
Private Sub ImporterID_DblClick(Cancel As Integer)
DoCmd.OpenForm ("Importer"), , , , , acDialog
Me![ImporterID].Requery
End Sub
 
Private Sub ImporterID_NotInList(NewData As String, Response As Integer)
MsgBox "This Party is not in list.Double Click to Add ", vbInformation
End Sub
 
Private Sub ItemID_DblClick(Cancel As Integer)
DoCmd.OpenForm ("Item"), , , , , acDialog
Me![ItemID].Requery
End Sub
 
 
 
Private Sub ItemID_NotInList(NewData As String, Response As Integer)
MsgBox "This item is not in list.Double Click to Add ", vbInformation
End Sub
 
Private Sub Head_NotInList(NewData As String, Response As Integer)
MsgBox "This item is not in list.Double Click to Add ", vbInformation
End Sub
 
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    Case 33, 34
    KeyCode = 0
End Select
End Sub
 
Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    Case 33, 34
    KeyCode = 0
End Select
End Sub

Sanjay,

Firstly please don't shout (all uppercase) and waste space on the page. This is annoying for people trying to read it (and ultimately trying to help you out).

Secondly, if you would like us to understand what your problem is then it would be a good idea to share your code with us.

[FishVal]

As desired, I am giving below the code of the form, which will help you in understanding the exact requirement, which I am facing in auto increment based on criteria.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngRecNo As Long
lngRecNo = Nz(DMax("[LineNumber]", "[Receipt]", "[ItemID]=" & Me![ItemID]), 0) + 1
End Sub

It will be a good idea to save calculated number to the table as well.
The easiest way to do it is to add to the form a control bound to [LineNumber] table field. Let us say the control is Textbox named [LineNumber].

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me![LineNumber] = Nz(DMax("[LineNumber]", "[Receipt]", _
        "[ItemID]=" & Me![ItemID]), 0) + 1
End Sub
 

[Sanjaylml]

You are great!

Thanks a lot. Now, I am able to do the job, what I required.

Thanking you once again.

It will be a good idea to save calculated number to the table as well.
The easiest way to do it is to add to the form a control bound to [LineNumber] table field. Let us say the control is Textbox named [LineNumber].

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me![LineNumber] = Nz(DMax("[LineNumber]", "[Receipt]", _
        "[ItemID]=" & Me![ItemID]), 0) + 1
End Sub
 

[FishVal]

Glad it worked for you. Good luck.

Best regards,
Fish