Counting returned rows

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

Hi all,

I have a form that will eventually allow me to add, modify and delete
certain elements of tables. I am working on the adding function at the
moment, and I dont want the user to be able to add a department name if
that name already exists.

I am thinking that I just use a select statement where the department
name is equal to what they have tried to add, and if there are no
records, then it is fine.

Is there a better way to do this, and if not, how can I count the rows
returned?

Thanks

Patrick
 
Patrick said:
Hi all,

I have a form that will eventually allow me to add, modify and delete
certain elements of tables. I am working on the adding function at the
moment, and I dont want the user to be able to add a department name if
that name already exists.

I am thinking that I just use a select statement where the department
name is equal to what they have tried to add, and if there are no
records, then it is fine.

Is there a better way to do this, and if not, how can I count the rows
returned?

Thanks

Patrick
Click to expand...

Patrick,

There are several ways to achieve what you want, depending on how you've
designed your form, but your suggestion will work - I've used that technique
many times myself. Use your SELECT statement to populate a Recordset, then
check the RecordCount property of the recordset. If there are no matching
records, the RecordCount property will be zero; if there are any matches,
the RecordCount property will be greater than zero. This assumes you're
using DAO; I'm not certain about the syntax for ADO but the concept should
be similar.

HTH,

Carl Rapson
 
You can put a unique index on the Department Name to keep it unique.

You can use DCount function to in the after update event of the control.

Something like the following

If DCount("*","YourTableName","[Department Name] =""" & me.SomeControlName &
"""") >0 then
MsgBox Me.SomeControlName & " is already saved as a department name."
End If
 
Back
Top