MS Access Form Format Question

[Michael Burkett]

I have 2 forms that I am using for inputing data. The first form
('Client') has various info based on a 4 digit client number (name,
address, ect).

The second form ('group') has a group name and the option to add up to
10 of the aofrementioned account numbers that have something in common
(similiar mailing addresses for instance).

What I am trying to do is get a box in the 'Client' form to
automatically show the group name entered in the 'Group' form IF the
account number is one of the 10 account numbers in the 'Group' form.

Any ideas. Please be as specific as you can. Thank you in advance for
any help!

Michael Burkett

[Phil Stanton]

Re: MS Access Form Format Question

Can a client be assigned to more than 1 group?

Phil
"Michael Burkett" <michael@ccminc .com> wrote in message
news:2899627d.0 311101406.27dfc 83d@posting.goo gle.com...[color=blue]
> I have 2 forms that I am using for inputing data. The first form
> ('Client') has various info based on a 4 digit client number (name,
> address, ect).
>
> The second form ('group') has a group name and the option to add up to
> 10 of the aofrementioned account numbers that have something in common
> (similiar mailing addresses for instance).
>
> What I am trying to do is get a box in the 'Client' form to
> automatically show the group name entered in the 'Group' form IF the
> account number is one of the 10 account numbers in the 'Group' form.
>
> Any ideas. Please be as specific as you can. Thank you in advance for
> any help!
>
> Michael Burkett[/color]

[Michael Burkett]

Re: MS Access Form Format Question

"Phil Stanton" <phil@stantonfa mily.co.uk> wrote in message news:<3fb0159b$ 0$106$65c69314@ mercury.nildram .net>...[color=blue]
> Can a client be assigned to more than 1 group?
>
> Phil
> "Michael Burkett" <michael@ccminc .com> wrote in message
> news:2899627d.0 311101406.27dfc 83d@posting.goo gle.com...[color=green]
> > I have 2 forms that I am using for inputing data. The first form
> > ('Client') has various info based on a 4 digit client number (name,
> > address, ect).
> >
> > The second form ('group') has a group name and the option to add up to
> > 10 of the aofrementioned account numbers that have something in common
> > (similiar mailing addresses for instance).
> >
> > What I am trying to do is get a box in the 'Client' form to
> > automatically show the group name entered in the 'Group' form IF the
> > account number is one of the 10 account numbers in the 'Group' form.
> >
> > Any ideas. Please be as specific as you can. Thank you in advance for
> > any help!
> >
> > Michael Burkett[/color][/color]

No. Any given client will only be in 1 group. Thanks

[Phil Stanton]

Re: MS Access Form Format Question

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_BeforeU pdate(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.OpenRecord set(SQLStg)
If CountSet!CountO fClientID > 10 Then
MsgBox "You have " & CountSet!CountO fClientID & " 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[color=blue]
>
> No. Any given client will only be in 1 group. Thanks[/color]

[Michael Burkett]

Re: MS Access Form Format Question

"Phil Stanton" <phil@stantonfa mily.co.uk> wrote in message news:<3fb10f00$ 0$113$65c69314@ mercury.nildram .net>...[color=blue]
> 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_BeforeU pdate(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.OpenRecord set(SQLStg)
> If CountSet!CountO fClientID > 10 Then
> MsgBox "You have " & CountSet!CountO fClientID & " 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[color=green]
> >
> > No. Any given client will only be in 1 group. Thanks[/color][/color]

THANK YOU!