limit record on continous subform

[sobeit via AccessMonster.com]

hello

how can i limit a number of record in a continous subform

 

[Arvin Meyer [MVP]]

Something like this should work in the subform's Current event, to limit to
5 records:


Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount > 4 Then
Me.DefaultEditing = 4
Else
Me.DefaultEditing = 2
End If

 

[John W. Vinson]

hello

how can i limit a number of record in a continous subform

Use the Form's BeforeInsert event to count the number of records already
added, and cancel the Insert with a warning message:

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[tablename]", "<appropriate criteria>") >= 10 Then
Cancel = True
MsgBox "Only 10 records allowed for this <whatever>", vbOKOnly
End If
End Sub

 

[sobeit via AccessMonster.com]

thanks for the immediate response

sorry arvin i am not that good in vb i want to limit my records to only 9
when i use the code that u gave i cannot edit records on my subform

 

[sobeit via AccessMonster.com]

thanks john when i add ur code i also cannot add or edit into my subform
please help

hello

how can i limit a number of record in a continous subform

Use the Form's BeforeInsert event to count the number of records already
added, and cancel the Insert with a warning message:

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[tablename]", "<appropriate criteria>") >= 10 Then
Cancel = True
MsgBox "Only 10 records allowed for this <whatever>", vbOKOnly
End If
End Sub

 

[John W. Vinson]

thanks john when i add ur code i also cannot add or edit into my subform
please help

hello

how can i limit a number of record in a continous subform

Use the Form's BeforeInsert event to count the number of records already
added, and cancel the Insert with a warning message:

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[tablename]", "<appropriate criteria>") >= 10 Then
Cancel = True
MsgBox "Only 10 records allowed for this <whatever>", vbOKOnly
End If
End Sub

This has absolutely nothing to do with *editing* existing records. It only
takes effect if you are adding a new record.

Please post your actual code, the name of your table, the Master Link Field
and Child Link Field properties of the subform, and indicate the actual error
that you're getting.

 

[sobeit via AccessMonster.com]

master link - drnomain
child link - drnosub
main table - delivery receipt
sub table - monitoring

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[MONITORING]", "[PROJECT]") >= 10 Then
Cancel = True
MsgBox "Only 10 records allowed ", vbOKOnly
End If
End Sub

i am not sure if i did the right insertion on the code u have given
i cannot add or edit on the subform it only pops the message only 10records...

 

[John W. Vinson]

master link - drnomain
child link - drnosub
main table - delivery receipt
sub table - monitoring

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[MONITORING]", "[PROJECT]") >= 10 Then
Cancel = True
MsgBox "Only 10 records allowed ", vbOKOnly
End If
End Sub

i am not sure if i did the right insertion on the code u have given
i cannot add or edit on the subform it only pops the message only 10records...

This will allow only ten records in the MONITORING table. The third argument
to DCount must be a valid SQL WHERE clause without the word WHERE. Since I
cannot see your database, I have *no way to know* how you would determine that
there are enough records in the subform; at a *guess* it might be

DCount("*", "[MONITORING]", "[drnosub] = " & Me!drnosub)

but since I don't know what PROJECT, drnosub, or drnodomain might be or what
they mean, I cannot be sure.

Remember: *you* can see your database. You know how it's structured. We don't.

 

[sobeit via AccessMonster.com]

i thought

if DCount("*",[MYSUBTABLENAME]","[MYFIELD_IN_SUBTABLE]")>= 10 then
Cancel = true

or what are u trying to say is

if DCount("*",[MYSUBTABLENAME]","[CHILDLINKFIELD]")>= 10 then

master link - drnomain
child link - drnosub

[quoted text clipped - 10 lines]

i am not sure if i did the right insertion on the code u have given
i cannot add or edit on the subform it only pops the message only 10records...

This will allow only ten records in the MONITORING table. The third argument
to DCount must be a valid SQL WHERE clause without the word WHERE. Since I
cannot see your database, I have *no way to know* how you would determine that
there are enough records in the subform; at a *guess* it might be

DCount("*", "[MONITORING]", "[drnosub] = " & Me!drnosub)

but since I don't know what PROJECT, drnosub, or drnodomain might be or what
they mean, I cannot be sure.

Remember: *you* can see your database. You know how it's structured. We don't.

 

[John W. Vinson]

i thought

if DCount("*",[MYSUBTABLENAME]","[MYFIELD_IN_SUBTABLE]")>= 10 then
Cancel = true

or what are u trying to say is

if DCount("*",[MYSUBTABLENAME]","[CHILDLINKFIELD]")>= 10 then

Neither.

You have two examples in which the third argument is a fieldname.

The third argument is NOT a fieldname; it is a logical expression which is
TRUE if the record counts toward the limit, and FALSE if it does not.

It will look SOMETHING like

"[Fieldname] = " & [someotherfieldname]

where [Fieldname] is a field in MYSUBTABLENAME - the foreign key field in the
one to many relationship, typically - and [someotherfieldname] is the name of
a field containing the value of the current main form record's key value.
These may well be the same, but my telepathy is really on the blink tonight,
and *I don't know your form or table structure* so I don't know what the
correct fieldnames should be.

 

[sobeit via AccessMonster.com]

yes i am the same person

im just hoping i could find another answer that could suit my understanding

thanks john

i thought

[quoted text clipped - 4 lines]

if DCount("*",[MYSUBTABLENAME]","[CHILDLINKFIELD]")>= 10 then

Neither.

You have two examples in which the third argument is a fieldname.

The third argument is NOT a fieldname; it is a logical expression which is
TRUE if the record counts toward the limit, and FALSE if it does not.

It will look SOMETHING like

"[Fieldname] = " & [someotherfieldname]

where [Fieldname] is a field in MYSUBTABLENAME - the foreign key field in the
one to many relationship, typically - and [someotherfieldname] is the name of
a field containing the value of the current main form record's key value.
These may well be the same, but my telepathy is really on the blink tonight,
and *I don't know your form or table structure* so I don't know what the
correct fieldnames should be.

 

[Arvin Meyer [MVP]]

So copy and paste this exact code into your subform's code window and
compile the code (Debug >>> Compile):

Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount > 8 Then
Me.DefaultEditing = 4
Else
Me.DefaultEditing = 2
End If
End Sub

If you get any error, you may have to set a reference to DAO. Do that by
going to Tools >>> References and finding Microsoft DAO 3.6 Object Library.
Check the box next to it and everything should work fine.

 

[Dirk Goldgar]

So copy and paste this exact code into your subform's code window and
compile the code (Debug >>> Compile):

Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount > 8 Then
Me.DefaultEditing = 4
Else
Me.DefaultEditing = 2
End If
End Sub

Arvin, what is DefaultEditing? I'm not familiar with this property, and
can't find it in the A2003 online help. I was thinking the AllowAdditions
property would be useful in this case.

 

[Arvin Meyer [MVP]]

Arvin, what is DefaultEditing? I'm not familiar with this property, and

can't find it in the A2003 online help. I was thinking the AllowAdditions
property would be useful in this case.

DefaultEditing is now a Hidden Property (turn it on in the Object Browser)
which has been replaced with AllowEditing and AllowAdditions, etc. I've been
using it forever, and since it still works I never bothered to change it.
The code I posted works fine (and has been for many years). I suppose it
would be better to start using the new forms.

 

[Dirk Goldgar]

DefaultEditing is now a Hidden Property (turn it on in the Object Browser)
which has been replaced with AllowEditing and AllowAdditions, etc. I've
been using it forever, and since it still works I never bothered to change
it. The code I posted works fine (and has been for many years). I suppose
it would be better to start using the new forms.

Very interesting. I see it there in the Object Browser. What are the
values for DefaultEditing that are associated with the various states of the
Allow... properties?

 

[sobeit via AccessMonster.com]

it is working perfectly on an edit mode of the subform but when i added new
record on my main form
my sub cannot add record
please help

thanks arvin

So copy and paste this exact code into your subform's code window and
compile the code (Debug >>> Compile):

Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount > 8 Then
Me.DefaultEditing = 4
Else
Me.DefaultEditing = 2
End If
End Sub

If you get any error, you may have to set a reference to DAO. Do that by
going to Tools >>> References and finding Microsoft DAO 3.6 Object Library.
Check the box next to it and everything should work fine.

thanks for the immediate response

[quoted text clipped - 15 lines]

 

[AccessVandal via AccessMonster.com]

If your subform record source is a query, use "Select Top 9.....". Or in the
query properties, "Top Values" set it to 9.

 

[Arvin Meyer [MVP]]

IIRC, it's these

1 - Data Entry
2 - AllowEdits AllowAdditions
3 - NoEdits NoDeletions NoAdditions
4 - NoAdditions

I'm not positive of 3, but I think it's correct.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Dirk Goldgar]

IIRC, it's these

1 - Data Entry
2 - AllowEdits AllowAdditions
3 - NoEdits NoDeletions NoAdditions
4 - NoAdditions

I'm not positive of 3, but I think it's correct.

Thanks, Arvin. I'll file that away for future reference.

 

[brlubman]

it is working perfectly on an edit mode of the subform but when i added new
record on my main form
my sub cannot add record
please help

thanks arvin

So copy and paste this exact code into your subform's code window and
compile the code (Debug >>> Compile):

Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount > 8 Then
Me.DefaultEditing = 4
Else
Me.DefaultEditing = 2
End If
End Sub

If you get any error, you may have to set a reference to DAO. Do that by
going to Tools >>> References and finding Microsoft DAO 3.6 Object Library.
Check the box next to it and everything should work fine.

thanks for the immediate response

[quoted text clipped - 15 lines]

how can i limit a number of record in a continous subform