Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How can i limit my subform to only allow only 24 records?
- Thread starter ruthcali
- Start date
TheInsider
Programmer
Hi,
Ironically, in Access you're not really supposed to think in terms of limited record amounts - even though it is quite common to. Access is designed for 1 or infinite, so you get the 1 to 1, 1 to many... relationships. You might have to base the subform purely on VBA code to do this. Use a recordset and continually count the records. Instead of using the Link Child/Master fields property of the subform you will need to referrence the appropriate field in the Parent form's record source. You may find that it will do more harm than good trying to limit the number of records, due to the work involved. [sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]
Ironically, in Access you're not really supposed to think in terms of limited record amounts - even though it is quite common to. Access is designed for 1 or infinite, so you get the 1 to 1, 1 to many... relationships. You might have to base the subform purely on VBA code to do this. Use a recordset and continually count the records. Instead of using the Link Child/Master fields property of the subform you will need to referrence the appropriate field in the Parent form's record source. You may find that it will do more harm than good trying to limit the number of records, due to the work involved. [sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]
Ruthcali,
set up a query in access that will show the appropriate
records. while in design view right click to
properties and change MAX RECORDS to the value you want.
this will return the first 25 records in the query if
you set MAX RECORDS to 25. so if your subform is linked
to the query you will only get 25 records on the subform [sig][/sig]
set up a query in access that will show the appropriate
records. while in design view right click to
properties and change MAX RECORDS to the value you want.
this will return the first 25 records in the query if
you set MAX RECORDS to 25. so if your subform is linked
to the query you will only get 25 records on the subform [sig][/sig]
- Thread starter
- #4
Jare,
Interesting, but i think that only works for ODBC databases. i don't think i have an ODBC database (How can i verify that by the way?) I thought that solution was too easy!
Rob,
Thanks for your information. Have you ever had a situation where you needed to limit records? i have pieces of equipment that can have a maximum of 24 systems connected to each one. that is why i want to limit my subform. i guess i can just put a label in the subform waring users that only 24 entries are allowed, but i would rather restrict them programmatically.
thanks,
ruth
[sig][/sig]
Interesting, but i think that only works for ODBC databases. i don't think i have an ODBC database (How can i verify that by the way?) I thought that solution was too easy!
Rob,
Thanks for your information. Have you ever had a situation where you needed to limit records? i have pieces of equipment that can have a maximum of 24 systems connected to each one. that is why i want to limit my subform. i guess i can just put a label in the subform waring users that only 24 entries are allowed, but i would rather restrict them programmatically.
thanks,
ruth
[sig][/sig]
TheInsider
Programmer
Hi,
I must admit that the "Max Records" property is new to me. I could not get it to work though. I fully agree that there are perfectly good reasons for wanting to limit record amounts in some cases, but I am still not convinced that this can easily be done in Access. [sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]
I must admit that the "Max Records" property is new to me. I could not get it to work though. I fully agree that there are perfectly good reasons for wanting to limit record amounts in some cases, but I am still not convinced that this can easily be done in Access. [sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]
- Thread starter
- #6
It does look like it doesn't work with access tables
(I always use SQL tables linked in access) You could
try a TOP 25 query but anything else would have to be
in VB.
OR you could set up an ODBC driver for ACCESS (I think
windows95 includes these.) And then link your tables
Via ODBC (You will need to split your DB) [sig][/sig]
Never overlook the obvious:
In the underlying table, create a field, set to number format, default value being 1.
On the subform, make this field hidden, but have a sum() box too that totals it up, when the sum reaches a preset value (eg 24) me.allowadditions = false
Garry
In the underlying table, create a field, set to number format, default value being 1.
On the subform, make this field hidden, but have a sum() box too that totals it up, when the sum reaches a preset value (eg 24) me.allowadditions = false
Garry
Garry's on the right track, though I'd go about it differently.
I'd make a recordset to check how many there are and run the code from the subform's BeforeInsert event. That means you don't have to store funny data, which might get changed or deleted at some point.
Also, in my example I set up a message to the user, instead of changing the allowAdditions property of the subform. Sort of arguable which is better. I suppose best would be to change the AllowAdditions and make visible some previously invisible text box on the main form that tells the user that no more records can be added to the subform.
In any case, here's the code I wrote:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Set db = CurrentDb
strSql = "SELECT Count(AccountID) as AccountCount " _
& " FROM tblAccount " _
& " WHERE BankID = " & Me.Parent!txtBankID
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
If rst!accountcount > 7 Then
Call MsgBox("There are already 7 Banks for this " _
& "account. That's all that are allowed.", _
vbInformation, "Data Conflict"
Cancel = True
End If
End Sub =============
Jeremy Wallace
Designing and building Access databases since 1995.
I'd make a recordset to check how many there are and run the code from the subform's BeforeInsert event. That means you don't have to store funny data, which might get changed or deleted at some point.
Also, in my example I set up a message to the user, instead of changing the allowAdditions property of the subform. Sort of arguable which is better. I suppose best would be to change the AllowAdditions and make visible some previously invisible text box on the main form that tells the user that no more records can be added to the subform.
In any case, here's the code I wrote:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Set db = CurrentDb
strSql = "SELECT Count(AccountID) as AccountCount " _
& " FROM tblAccount " _
& " WHERE BankID = " & Me.Parent!txtBankID
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
If rst!accountcount > 7 Then
Call MsgBox("There are already 7 Banks for this " _
& "account. That's all that are allowed.", _
vbInformation, "Data Conflict"
Cancel = True
End If
End Sub =============
Jeremy Wallace
Designing and building Access databases since 1995.
- Status
Similar threads
- Locked
- Question
- Locked
- Question