🚀 go-pugleaf

Hi Scott,

You hanve't mentioned the Access version.  But something like this should
work under 97.

Private Sub Form_Current()
    If Me.NewRecord Then
        With Me.RecordsetClone
            .MoveLast:  .MoveFirst
            If .RecordCount >= 5 Then
                MsgBox "Can't add more than 5 records!"
                .MoveLast
                Me.Bookmark = .Bookmark
            End If
        End With
    End If
End Sub

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

Scott Mordecai wrote in message ...
>Hi, I hope I can make some sense here...
>
>I have a form which has basic record details (person's name, telephone
>number, etc.). On this form is a datasheet sub-form where I can list a
>number of items. I would like to limit the number of entries on this
>datasheet.
>
>e.g. the datasheet has two fields, "Name" (linked to the main record
>table) and "Item". I would like each "Name" to be allowed only a maximum
>number of items.
>
>Any help, if the above makes any sense, is appreciated!
>--
> \|||/  Scott Mordecai
>< o,o > mailto:scott@musicman.demon.co.uk
>  \_/

On Sun, 3 May 1998 21:26:49 +0100 in comp.databases.ms-access, Scott
Mordecai <Scott@musicman.demon.co.uk> wrote:
>Hi, I hope I can make some sense here...
>
>I have a form which has basic record details (person's name, telephone
>number, etc.). On this form is a datasheet sub-form where I can list a
>number of items. I would like to limit the number of entries on this
>datasheet.
>
>e.g. the datasheet has two fields, "Name" (linked to the main record
>table) and "Item". I would like each "Name" to be allowed only a maximum
>number of items.
>
>Any help, if the above makes any sense, is appreciated!

Assuming the foreign key in the subforms recordset is called "FK" then:

Sub Form_BeforeInsert(Cancel As Integer)
  If DCount("*","ThisTable","FK = " & Me!FK)>=3 Then
    Cancel = True
    Msgbox "Rassssssssssssp!"
  End If
End Sub

You can replace DCount() with tCount() from the downloads section of my
FAQ, it's usually faster than DCount, especially over networks.

                    \|||/
                    /   \
                   C o o D
-----------------ooO--u--Ooo-------------------------------
To reply by mail, replace the "nospam" in my address with "trevor",
then replace the "o" in easynot with an "e".
But please DONT cc me in on posts, I read this group as well.
MS Access FAQ now available on my site below.
http://www.trevor.easynet.co.uk/

Roget's Thesaurus rules - ok, all right, very well, you bet, certainly

>You hanve't mentioned the Access version.  But something like this should
>work under 97.
>
>Private Sub Form_Current()

Thanks for the reply. I would love to try your little peice of code, but
I don't know exactly where I should enter it. :( Can you help?

Cheers,
-- 
 \|||/  Scott Mordecai
< o,o > mailto:scott@musicman.demon.co.uk
  \_/

>Assuming the foreign key in the subforms recordset is called "FK" then:
>
>Sub Form_BeforeInsert(Cancel As Integer)
>  If DCount("*","ThisTable","FK = " & Me!FK)>=3 Then
>    Cancel = True
>    Msgbox "Rassssssssssssp!"
>  End If
>End Sub

Thanks for that, but I'm afraid I have no idea where I should enter it.
One of the before/after update field options perhaps?

Cheers,
-- 
 \|||/  Scott Mordecai
< o,o > mailto:scott@musicman.demon.co.uk
  \_/

On Mon, 4 May 1998 17:48:05 +0100 in comp.databases.ms-access, Scott
Mordecai <Scott@musicman.demon.co.uk> wrote:
>>Assuming the foreign key in the subforms recordset is called "FK" then:
>>
>>Sub Form_BeforeInsert(Cancel As Integer)
>>  If DCount("*","ThisTable","FK = " & Me!FK)>=3 Then
>>    Cancel = True
>>    Msgbox "Rassssssssssssp!"
>>  End If
>>End Sub
>
>Thanks for that, but I'm afraid I have no idea where I should enter it.
>One of the before/after update field options perhaps?
>
>Cheers,
>-- 
> \|||/  Scott Mordecai
>< o,o > mailto:scott@musicman.demon.co.uk
>  \_/

Sub Form_BeforeInsert
    ^^^^
    Clue #1
            
Sub Form_BeforeInsert
         ^^^^^^^^^^^^
         Clue #2

In the form properties (event properties), select "BeforeInsert", select
"[Event Procedure]" from the drop down list. Now hit the builder button
next to it (the one marked "...").

                    \|||/
                    /   \
                   C o o D
-----------------ooO--u--Ooo-------------------------------
To reply by mail, replace the "nospam" in my address with "trevor",
then replace the "o" in easynot with an "e".
But please DONT cc me in on posts, I read this group as well.
MS Access FAQ now available on my site below.
http://www.trevor.easynet.co.uk/

Roget's Thesaurus rules - ok, all right, very well, you bet, certainly

>Sub Form_BeforeInsert
>    ^^^^
>    Clue #1
>            
>Sub Form_BeforeInsert
>         ^^^^^^^^^^^^
>         Clue #2

Clue #3 - if I'd had more than five seconds to learn Access and produce
an application I wouldn't have had to ask! (The last time I wrote an
application dBase III was *the* database to use!)

By the way, one of the suggestions here works a treat, the other gives
an error (the "Dcount" version), probably me just not understanding the
command and therefore not typing it in properly.

Thanks to all, 
-- 
 \|||/  Scott Mordecai
< o,o > mailto:scott@musicman.demon.co.uk
  \_/

On Wed, 6 May 1998 22:12:36 +0100 in comp.databases.ms-access, Scott
Mordecai <Scott@musicman.demon.co.uk> wrote:

>Clue #3 - if I'd had more than five seconds to learn Access and produce
>an application I wouldn't have had to ask! (The last time I wrote an
>application dBase III was *the* database to use!)

I hope you realise that I wasn't having a pop at you, merely giving
pointers as to you to understand where code goes so when others give you
form_afterupdate or fieldname_dblClick then you'll know.

>By the way, one of the suggestions here works a treat, the other gives
>an error (the "Dcount" version), probably me just not understanding the
>command and therefore not typing it in properly.

If the foriegn key field in question was text then that would have been:

DCount("*","ThisTable","FK = '" & Me!FK & "'")

                    \|||/
                    /   \
                   C o o D
-----------------ooO--u--Ooo-------------------------------
To reply by mail, replace the "nospam" in my address with "trevor",
then replace the "o" in easynot with an "e".
But please DONT cc me in on posts, I read this group as well.
MS Access FAQ now available on my site below.
http://www.trevor.easynet.co.uk/

Roget's Thesaurus rules - ok, all right, very well, you bet, certainly

>I hope you realise that I wasn't having a pop at you, merely giving
>pointers

Yeah, I know, it was just my attempt at being humorous (and 99% of the
time I fail miserably!). No, seriously I'm really grateful for the help.

BTW, if any one is interested, the application in question is for a
charity thing I'm doing for a friend. He's running a competition based
on the iminent soccer world cup - to guess the scores, final teams,
winner, and various other things based on the competition. He's hoping
to get a couple of hundred entries so keeping track of points and the
winner of the competition would be a nightmare if done manually!

Thanks again, you may be seeing more queries posted here in the very
near future!

Cheers,
-- 
 \|||/  Scott Mordecai
< o,o > mailto:scott@musicman.demon.co.uk
  \_/