What is Best Method to Limit Number of Records

[Dalan]

I have created a table and form for allowing the input of a unique
customer number (not an ID autonumber number) which a customer would
enter once after installing the database. I'm using DLookup to obtain
the value which works. What I need assistance with is the best method
to use to ensure that only ONE customer number can be entered. A quick
attempt to structure a piece of code is shown below. Any assistance
will be appreciated. Thanks, Dalan

Private Sub Form_AfterUpdat e() ? or ?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RecordCount As String
RecordCount = "CustomerNo "
Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblCustomerN o")
With rs
.MoveFirst
If RecordCount = 0 Or 1 Then
MsgBox "Only one Customer Number is allowed to be entered"

' Need code to limit the number of records to only 1

End If
.Close
End With
Set rs = Nothing
Set db = Nothing

End Sub

[Fletcher Arnold]

"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...

I have created a table and form for allowing the input of a unique
customer number (not an ID autonumber number) which a customer would
enter once after installing the database. I'm using DLookup to obtain
the value which works. What I need assistance with is the best method
to use to ensure that only ONE customer number can be entered. A quick
attempt to structure a piece of code is shown below. Any assistance
will be appreciated. Thanks, Dalan

Private Sub Form_AfterUpdat e() ? or ?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RecordCount As String
RecordCount = "CustomerNo "
Set db = CurrentDb()
Set rs = db.OpenRecordse t("tblCustomerN o")
With rs
.MoveFirst
If RecordCount = 0 Or 1 Then
MsgBox "Only one Customer Number is allowed to be entered"

' Need code to limit the number of records to only 1

End If
.Close
End With
Set rs = Nothing
Set db = Nothing

End Sub

If you are talking about having a table to contain things like MyCompany,
MyAddress, MyPhone, etc so there is only ever one record in the database,
then I would enforce it at table-level. Just put a validation rule in the
field so that ID=1, that way the no code is involved and it can't be
by-passed.

Fletcher

[Trevor Best]

If you just want a table with one record in then create a field in it
as follows:

Name: OnlyOneRecord
Type: Number, Long
Indexed: Yes (No Duplicates)
Validation Rule: =1
Default Value: 1

--
A)bort, R)etry, I)nfluence with large hammer.