This topic is locked

Limit amount of data in an Access table

11/18/2006 10:41:35 AM
ASPRunnerPro General questions
author

I was wondering if there is a simple way to limit the amount of records

allowed into a table.
I would like the user to receive a message that they are at capacity

for this table.
An example would be if a student was signing up for a class and the class was at its limit...they would receive a message.
Anything would be great.
Thanks

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3896&image=1&table=forumtopics' class='bbc_emoticon' alt=':D' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3896&image=2&table=forumtopics' class='bbc_emoticon' alt=':D' />

jmager
Ok, I think I found my answer....searching is a powerful weapon <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3896&image=3&table=forumtopics' class='bbc_emoticon' alt=':D' />

C
clig 11/18/2006

I was wondering if there is a simple way to limit the amount of records

allowed into a table.
I would like the user to receive a message that they are at capacity

for this table.
An example would be if a student was signing up for a class and the class was at its limit...they would receive a message.
Anything would be great.
Thanks

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=13003&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=13003&image=2&table=forumreplies' class='bbc_emoticon' alt=':D' />

jmager
Ok, I think I found my answer....searching is a powerful weapon <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=13003&image=3&table=forumreplies' class='bbc_emoticon' alt=':D' />


Something like this in Before record added and a backend of SQL Server (DCOUNT maybe better for MSAccess):
Function BeforeAdd(dict)
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Add form represented as 'Field name'-'Field value' pair
'** Custom code ****

' put your custom code here
set CN = server.CreateObject("ADODB.Connection")

CN.Open "DSN=NTSS;UID=sa;PWD=xxx"

set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT COUNT(Action_Item_ID) AS RecordCount FROM dbo.Action_Items", CN

If int(rs("RecordCount")) > 20 Then

Response.Write "Too Many Records: " & rs("RecordCount") & " - Max of 20 records allowed."

Exit Function

Else

End If

rs.Close
BeforeAdd = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
End Function

501274 11/19/2006

Thank you, that worked great. I found this on a search of the asprunner forum. Will this work with MSAccess? Thanks again for the help <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=13010&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
"You could have a table named "class_names" with fields called "class_id", "class_name" and "class_amount" which the "class_amount" field will hold the total amount of students allowed, in this case 16.
Then when a new record is added using a "look up dropdown box", subtract 1 off the total until 16 records have been added, once this reaches zero you could have it that the class name vanishes from the "drop down list" so its not available for selection.


Add Page

CODE

Function BeforeAdd(dict)
strSQLInsert ="UPDATE class_names SET class_amount=(class_amount-1) WHERE class_name ='"&dict("YourDropdownField")&"'"
dbConnection.Execute strSQLInsert
BeforeAdd = True

End Function
List Page

CODE

Function BeforeDelete(where)
str = "select Battle_Date from class_names where" & where

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
strSQLInsert ="UPDATE class_names SET class_amount=(class_amount+1) WHERE class_name ='"&rsTemp("YourDropdownField")&"'"

dbConnection.Execute strSQLInsert

BeforeDelete = True

End Function
Your drop down box would need something like this in the "Where" option.

CODE

"class_amount>=1"


This is only a sample. "



Something like this in Before record added and a backend of SQL Server (DCOUNT maybe better for MSAccess):
Function BeforeAdd(dict)
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Add form represented as 'Field name'-'Field value' pair
'** Custom code ****

' put your custom code here
set CN = server.CreateObject("ADODB.Connection")

CN.Open "DSN=NTSS;UID=sa;PWD=xxx"

set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT COUNT(Action_Item_ID) AS RecordCount FROM dbo.Action_Items", CN

If int(rs("RecordCount")) > 20 Then

Response.Write "Too Many Records: " & rs("RecordCount") & " - Max of 20 records allowed."

Exit Function

Else

End If

rs.Close
BeforeAdd = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
End Function

Sergey Kornilov admin 11/19/2006

Yes, this will work with MS Access.

501275 11/19/2006

Yes, this will work with MS Access.


Thanks, I got it working....how can I make this happen with 3 drop downs on the same add page. All three drop downs will be pulling from 3 different course tables.
Again, this forum and your support is awesome.
jm