By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

What is Best Method to Limit Number of Records

P: n/a
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_AfterUpdate() ? or ?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RecordCount As String
RecordCount = "CustomerNo"
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblCustomerNo")
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
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_AfterUpdate() ? or ?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RecordCount As String
RecordCount = "CustomerNo"
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblCustomerNo")
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
Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.