OK Michael Piece of cake
I presume the GroupsTable contains
GroupID Autonumber
Group Text Required = true
In the ClientsTable you need
ClientID Autonumber
ClientRef Number Long Integer Indexed Yes Duplicates
allowed no ' That is your reference no
Client Name Text
ClientAdd1 Text
etc
GroupID Number Long Integer
Set up a relationship between the ClientsTable and GroupsTable joining the
GroupID in the GroupsTable with the GroupID in the ClientsTable and enforce
referential ingegrity
You then need your client form based on the ClientsTable (or query because
you may want to sort it by Clientname)
Have a Combo box called GroupID
ControlSource is GroupID
RowSourceType is Table/Query
RowSource "SELECT Group, GroupID
FROM Groups ORDER BY Group"
ColunmCount 2
ColumnWidth 2cm,0cm
BoundColumn 2
This will show the correct group for each client
You then need some visual basic to check the number of clients in this group
before you update it
Click on the GroupID Combo Box
In the properties box choose the events tab and under the Before Update type
[E square bracket
It should change this to [Event Procedure]
At the right of that line there are 3 dots which opens the VB box
Type
Option Compare Database
Option Explicit
Private Sub GroupID_BeforeUpdate(Cancel As Integer)
Dim MyDB As Database
Dim CountSet As Recordset
Dim SQLStg As String
SQLStg = "SELECT Count(ClientID) AS CountOfClientID "
SQLStg = SQLStg & "FROM Clients "
SQLStg = SQLStg & "WHERE GroupD = " & GroupID & ";"
Set MyDB = CurrentDb
Set CountSet = MyDB.OpenRecordset(SQLStg)
If CountSet!CountOfClientID > 10 Then
MsgBox "You have " & CountSet!CountOfClientID & " Clients in this
group", vbCritical
Cancel = True
End If
CountSet.Close
Set CountSet = Nothing
End Sub
Some of the above will already be there, so make sure it only occurs once
Have fun
Phil
No. Any given client will only be in 1 group. Thanks
