Code to count table record based on the Input (1 Viewer)

the1rock · Aug 14, 2013

In my DB i have field Called Group .
I want a Vb Code to count the record in the Table based on the input in the Group Field on The Form.(lets say Group = 1 , after many inputs of the first group i want it to count)
And if its more than 20 . show Msgbox thats you need to use Diff num for the Group.
Or any other way that do so after 20 records for each Group
Thanks In Advance

Mihail · Aug 14, 2013

DCount function should do the trick.

the1rock · Aug 14, 2013

Thanks for your reply
i tried Dcount but its will count all the record in the table
and i dont want that .
i want it to count the record for the input
lets say if i have 10 record having group num 1 so its must show that theres 10 records when i inter the group num 1

Mihail · Aug 14, 2013

Take a closer look to this function (Help files, Web).
The function has a Where clause that allow you to count records that have a certain property.

the1rock · Aug 14, 2013

actually i dont know what you talking about!

Mihail · Aug 14, 2013

About THIS.
I've said Where clause. They say Criteria. The same thing

the1rock · Aug 14, 2013

first the Criteria doesnt work to count when i input its keep count all records
2nd if its worked it will keep counting every single input and i dont want that
i am not an expert in vb so if you may help!

Mihail · Aug 14, 2013

And I'm not expert in English so, maybe, is why I don't understand what you mean.

Upload the database with simple data.
Before upload, convert it in Access 2003 or 2007 version then ZIP it.
Because you have less than 10 posts you only can upload in ZIP format.

the1rock · Aug 14, 2013

x = DCount("*", "bills", "[group]=group.value")
this will count in every single input
i want it when its got more than 20 input for the group to show msgbox says u got 20 records for this group

Mihail · Aug 14, 2013

Use only one of this rutines:

Code:

Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
Const MaxCountForGroup As Long = 20
    If DCount("*", "bills", "[group] = " & Me.Group.Value) >= MaxCountForGroup Then
        MsgBox ("GG = " & MaxCountForGroup)
        Cancel = True
        Me.Group.SetFocus
    End If
End Sub

Private Sub Group_AfterUpdate()
Const MaxCountForGroup As Long = 20
    If DCount("*", "bills", "[group] = " & Me.Group.Text) >= MaxCountForGroup Then
        MsgBox ("GG = " & MaxCountForGroup)
        Me.Group.SetFocus
    End If
End Sub

And turn ON the Option Ecplicit.
Will save you from a lot of troubles in the future.

the1rock · Aug 14, 2013

Thanks
its work with me like this also
If (DCount("*", "bills", "[group]=group.value") > 20) Then

MsgBox ("Enter New Group")
End If

one more question .
how do i lock the DB . i mean tables , queries . so only the user can use the forms only

Mihail · Aug 14, 2013

First of all, by splitting the DB.
See here what this mean and how to.
In time you will have more questions about this subject so I advice you to start a new thread on this subject.

the1rock · Aug 14, 2013

Thanks Alot Mihail

Code to count table record based on the Input (1 Viewer)

the1rock

New member

Mihail

Registered User.

the1rock

New member

Mihail

Registered User.

the1rock

New member

Mihail

Registered User.

the1rock

New member

Mihail

Registered User.

the1rock

New member

Attachments

Mihail

Registered User.

the1rock

New member

Mihail

Registered User.

the1rock

New member

Similar threads

Users who are viewing this thread