Limit Datasheet to ONE entry

[CedarTree]

Upon opening a subform (datasheet), is there a quick way using on_current event to determine if no rows have been added? I want to turn allowadditions to be true or false to allow the addition of one row maximum. Thanks!

 

[theDBguy]

You could try using DCount().

 

[CedarTree]

Thanks. Trying that, but I'm learning that if there are NO records in the subform, On_Current doesn't get called at all! Any tricks?

 

[bob fitz]

Trying that, but I'm learning that if there are NO records in the subform, On_Current doesn't get called at all!

Are you sure?

 

[CedarTree]

Yeah - put a break-line - it only goes to Form_Current when there's at least 1 row.

 

[CedarTree]

I could of course run an recordset query but was trying to see if there was an easier way.

 

[bob fitz]

Have you tried using the sub form controls on enter event and the after update event to test or an entry

 

[bob fitz]

I believe it can be done by setting the AllowAdditions property of the subform using the events that I spoke of in post#7 and also in the main forms on current event. See attached

 

[Gasman]

So wouldn't a Dcount() also work in Form Load as well as current event, for exactly that situation?

 

[CedarTree]

Form Load only works ONE time - but not if you point to a different master record. It's okay, I'll just do a recordset record count. Thanks!

 

[CedarTree]

Wait... duh... I stupidly put my form_current coding on the subfom, and not the main form. Dcount should work nicely!

 

[Gasman]

Form Load only works ONE time - but not if you point to a different master record. It's okay, I'll just do a recordset record count. Thanks!

Yes, you only need it one time, do you not?, just when there are no records for the current event to fire. Once you have a record, the current event will control your limits, when not, the Load event will control the limit.

 

[Pat Hartman]

Use the BeforeInsert event. It doesn't run until the user types the first character in the subform and it only runs ONCE per record. It is also cancellable. In this event use a dCount() to determine if there is an existing record. If there is, then

Cancel = True ''cancels the insert
Me.Undo ''backs out the typing so the record is no longer dirty
Msgbox "You may not add a new record at this time", vbOKOnly
Exit Sub

But, the larger question is what is the point of having a separate table if all you will allow is a single record?

 

[The_Doc_Man]

I'm going to take a different viewpoint. Is that subform a continuous form? Make it NOT continuous and then prevent it from being navigated once it is dirty, and make it "Me.AllowEdit=FALSE" in the AfterUpdate routine, and use the main form to re-enable AllowEdit. Similar arguments apply to Me.AllowAdditions, which would pretty much lock it all down. Then the only trick is to find the right moment to enable those options.

I'm basing my comment on your talk of "at least one row" - which sounds like a continuous form. May take up some extra room to make a pretty sub-form - but gives you the absolute opportunity to block subsequent additions and edits. If control is what you'r after, that might be more along the lines of what you want. Just a crack from the peanut gallery, I wouldn't be upset if you said it wasn't what you wanted.

 

[gemma-the-husky]

Upon opening a subform (datasheet), is there a quick way using on_current event to determine if no rows have been added? I want to turn allowadditions to be true or false to allow the addition of one row maximum. Thanks!

I think the only way you would have no active controls is if the form has no records, AND the query is non updateable. If the query is updateable you would see the controls for the first (blank) row.

In the case of the non updateable query I describe all the controls as being undefined.

Now you can test that by something like this, probably in the from load event, rather than the current event

If not myform.control("some control") is nothing
Then
  'A record exists
Else
'No record
End if

If there is a control, this will return true but if there is no defined control it will return false, and you know there is no record.

you could also count the records in the form load event, and probably the form open event.

dcount(me.recordsource)

Does this help?

I think I would either
a. Prepopulate a dummy record, and not allow additions or deletions, but allow edits, then you would never have a problem

b. Alternatively, if the record count is one, just prevent a new record being added by cancelling the before insert action, with a suitable warning.

 

[ebs17]

I want to turn allowadditions to be true or false to allow the addition of one row maximum.

That sounds like it's a bound form. In addition to all the gimmicks in the form, you can and should define important rules in the table and their definition - simply because, in addition to a bound form, there are other options for creating new records (=> recordsets, action queries, standard imports, manual operation). At the table level, protection and measures are more comprehensive and secure.

So you could use a one-to-one relationship, a unique index or even a check constraint.
Remember: the form only displays records. Really they lie in tables.

 

[Pat Hartman]

I ask again, If you only ever want to allow ONE record, WHY are you using a separate table? 1-1 relationships are extremely rare in the real world and most Access developers create them for the wrong reason.

As I mentioned, the On current event is the wrong event to use. What you want to do is to prevent entry of a second record, I told you how to do that by using the BeforeInsert event. There is no need to lock the subform. If you use the correct form level events, you can easily accomplish your goal.