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

 

[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.)