Limit Subform to one record (1 Viewer)

jharding08

Member
Local time
Yesterday, 20:58
Joined
Feb 16, 2021
Messages
52
I have a subform that has three fields - Start Date, End Date and Duration. It comes from a table that has many records, so the recordset is Select Top 1 (although there should only be one).

This subform should be able to allow for new entries, but just one, so I have to set the Allow Additions to Yes, right? If I set Allow Additions to No and there isnt a current record, it doesnt show the fields. I just want one record for this subform, but when I enter new or edit the current record and hit enter, it goes to a new record. Is there a way to limit this on New/Edit?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:58
Joined
May 21, 2018
Messages
6,356

Me.allowadditions = (me.recordset.recordcount = 0)
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:58
Joined
Sep 21, 2011
Messages
10,531
Check the form recordset count?
If zero, set AllowAdditions, else leave it disabled. Check again after record entered.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:58
Joined
May 21, 2018
Messages
6,356
In the sub on current event.
 

jharding08

Member
Local time
Yesterday, 20:58
Joined
Feb 16, 2021
Messages
52

Me.allowadditions = (me.recordset.recordcount = 0)
I'm running into an issue where if I create a new parent record, this logic isnt working because I use the link master/child field property in Access, so the recordset may be 1, but for that parent record it is 0.

Could I update the code to account for this second filter?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:58
Joined
Feb 19, 2002
Messages
36,284
You would never use the Load event for something like this.

The question is why if there can be only ONE related record, why is it in a separate table. One-to-one relationships in the real world are extremely rare. to make this work without code, set the PK to include Two fields. The FK to the parent table and a RecID field with a default value of 1 and a validation rule of =1 to prevent other values. That will prevent more records since you can't add a different PK.
 

Users who are viewing this thread

Top Bottom