Code to count table record based on the Input

[the1rock]

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]

DCount function should do the trick.

 

[the1rock]

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]

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]

actually i dont know what you talking about!

 

[Mihail]

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

 

[the1rock]

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]

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]

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]

Use only one of this rutines:

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]

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]

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]

Thanks Alot Mihail