Limit Records in table

[Joe Williams]

I am trying to find a way to limit the number of records in a table. I
figured I would just put some code behind the ADD button on a form that ran
some SQL to count the number of records in the table. If the number was
greater than desired, the user would get an error message. Simple enough.

The problem I am having is returning the value of the number of records fomr
the table. The following code represents the way I am trying to do this:

Dim strSQL As String
Dim intRecordCount As Integer
strSQL = "SELECT COUNT(*) AS NumRecords FROM POLICYTYPE"
intRecordCount = DoCmd.RunSQL(strSQL)

When I run this code i get a message "Compile Error: Expected Function or
Variable"

How can I accomplish what I am trying to do? Thanks

Joe

 

[MikeC]

Joe,

I can think of few ways that might work for you:

1) Open the table in design view.
2) Right mouse-click in the grid area and select Properties from the popup
menu.
3) For the table's Validation Rule property, enter "[YourID] <= 1".
Assuming you are using AutoNumber and have not already entered more than one
record in the table. You can also use this technique to limit the record ID
(not literally the "record count") to any integer. You can also enter some
validation text for the rule.
4) Close the property sheet and save the design changes. Now you try
adding a record and see what happens.

2) If you want to allow the user to create up to one record and you are
okay with letting the form enforce the record limiting rule for the table,
then you can set the form's properties as follows:

a) Default View = Single Form
b) Navigation Buttons = No
c) Cycle = Current Record
d) Allow Filters = No (Otherwise, the user can filter-out the record
and add a new one.)
e) Allow Additions = Yes

3) Another way would be to just set form's Allow Additions property to "No"
and just let the user edit the existing record.

 

[Rick Brandt]

I am trying to find a way to limit the number of records in a table. I figured
I would just put some code behind the ADD button on a form that ran some SQL to
count the number of records in the table. If the number was greater than
desired, the user would get an error message. Simple enough.

The problem I am having is returning the value of the number of records fomr
the table. The following code represents the way I am trying to do this:

Dim strSQL As String
Dim intRecordCount As Integer
strSQL = "SELECT COUNT(*) AS NumRecords FROM POLICYTYPE"
intRecordCount = DoCmd.RunSQL(strSQL)

When I run this code i get a message "Compile Error: Expected Function or
Variable"

How can I accomplish what I am trying to do? Thanks

RunSQL is for *action queries* that insert, update or delete records. You can't
use it for a SELECT query. You either need to open a Recordset object using
your strSQL or (simpler) just use DCount("*","POLICYTYPE")

 

['69 Camaro]

Hi, Joe.

In addition to Rick's suggestions on how to get the record count from the
table, the reason you received the compile error is because the syntax you
used requires that DoCmd.RunSQL be a function, which returns a value.
DoCmd.RunSQL is a subroutine, so it won't return a value to store in the
intRecordCount variable. In the future, even if you use an action query
with the RunSQL subroutine, don't try using it to return a value.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)