How do I limit a table to one row in Access

[Guest]

I have a form to update one cell in a table. This table is the place-holder
for a monthly meeting date that a number of reports refer to. On the form, I
want only the one cell to appear as available for updating.

Can I 1: have the table only ever contain one record, or 2: have the form
only disply the first record in the table?

Benno

 

[Douglas J. Steele]

There are ways of limiting, but the second request (only displaying the
first record) is easier to achieve.

Assuming you have a way of sorting the records so that the row you want is
the first row returned, change the Top Value property of the query to 1. The
SQL will look like:

SELECT TOP 1 Field1, Field2
FROM Table
ORDER BY Field1

 

[Larry Linson]

Can I 1: have the table only ever contain one record,
or 2: have the form only disply the first record in the table?

Just for grins, here is one way to limit the Table to a single record. Use
an identification field of type AutoNumber (set as Primary Key) and include
a validation code of =< 1. Once the first Record is created, adding a second
one will fail. You might need to include some code so it will fail
"gracefully" and not disturb the user who tried to add a record. You can
also disable the Navigation Buttons.

You can do the same thing with any numeric data type and similar validation.

Of course, do not give the user access to this Field, or sooner or later,
some user will decide to modify it and cause you some irritation.

Larry Linson
Microsoft Access MVP

 

[RobFMS]

I am hoping that the code sample here will illustrate what you need to do
for your form.
Let me know how this works for you.

Private Sub VerifyStatus()
Me.AllowAdditions = (Me.RecordsetClone.RecordCount = 0)
End Sub

' If you delete the record
Private Sub Form_AfterDelConfirm(Status As Integer)
' Once the record is deleted, allow entry of new record.
Me.AllowAdditions = True
End Sub

' After you add the record
Private Sub Form_AfterUpdate()
' Once a new record is added, do not allow any additions.
Me.AllowAdditions = False
End Sub

Private Sub Form_Current()
VerifyStatus
End Sub

Private Sub Form_Load()
VerifyStatus
End Sub

Rob

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/