Filtered Records and Data Entry

[Pwyd]

I have a database that when opened filters to the user's set of records.
When the recordset is empty however, i get a blank, empty form with no
controls on it. I was wondering, what is the standard procedure for querying
if a recordset does not contain any records for a person?

 

[KARL DEWEY]

Create a dummy record for each person. Use a DLookup as criteria so the dumm
record is not pulled if there are other records.

 

[Allen Browne]

The first thing is to understand why the form's Detail section goes blank.
See:
Why does my form go completely blank?
at:
http://allenbrowne.com/casu-20.html

You can then decide on the appropriate course of action, such as setting
AllowAdditions to No and cancelling BeforeInsert instead, or changing the
source query so it is updatable, or cancelling Form_Open if the
WhereCondition yields no records, or allowing it to open anyway and crafting
any expressions in the Form Header or Footer so they don't error.

 

[Pwyd]

I was doing just that. I'm trying to prepare for the day (in about a year)
when i leave here, and leaving the database in such a way so that they
wouldn't have to have much knowledge in order to add and remove people. I
have a seperate linked database that holds the names of the people and their
access levels. works great. The problem right now is, the data-entry page is
a command button off the main recordset. The main recordset won't show the
command button if the person has no records in the set. So what i'm left
with doing now, is having them add the names in the namelist database, then
build a single blank record so that the filter comes up with a value besides
null (a null recordset, when filtered, shows a single blank white page, with
nothing on the form, no buttons, etc) which will allow them to process as
designed. Is this making much sense?

 

[Pwyd]

Yes, thank you, i'm perfectly aware of why it occurs. I had done it on
purpose.

I want to have it determine if the person has zero records, and have it add
a single blank record on its own. I don't know how to do that,
programatically.

 

[Ken Sheridan]

You can't "have it add a single blank record on its own". At least not
unless the table allows Nulls in every column, which of course means amongst
other things that it has no primary key.

If you put something along these lines in the form's Open event procedure it
will not allow new records to be inserted if there is at least one existing
record for the user in question, but will do so if there are no records for
that user:

Me.AllowAdditions = (Me.RecordsetClone.RecordCount = 0)

Is that what you want?

Ken Sheridan
Stafford, England

 

[Pwyd]

It does allow nulls in all fields.

I'm not sure. All i'm looking to do is, if they do not have any records in
the record set, it should create one single blank record, so that when it
runs the filter on the form, they'll get something other than a blank page.
I do know WHY its getting the blank page. Its done that way on purpose. I
just need to have it add one record if none exist for that person. Is that
what the procedure you've written will accomplish?

 

[Ken Sheridan]

Putting the code I suggested in the form's Open event procedure should give
you what you are looking for. It doesn't actually insert an empty record
into the table, but shows the form at a 'new record', i.e. un unsaved
record. Saving an empty record to the table is not advisable, and
unnecessary. The user will see the form with all the controls in place, but
empty. This also means you don't need to allow Nulls in all fields, so you
can set the Required property of relevant fields to true, and set their
DefaultValue property where appropriate. Most importantly it means you can
define one or more fields as the primary key.

The question then arises as to whether you want the users to be able to
enter data into the empty new record. If they do it will 'Dirty' the form,
i.e. initiate a new record, which will then be saved if the user closes the
form, moves to another new record or otherwise explicitly saves the record.

If you don't want the users to be able to enter data into the new record,
but only see the empty unsaved record rather than the completely empty form
you can disable all the controls by putting the following code in the form's
Load event procedure:

Dim ctrl As Control

On Error Resume Next
For Each ctrl In Me.Controls
ctrl.Enabled = Not Me.NewRecord
ctrl.Locked = Me.NewRecord
Next ctrl

Note that this will disable and lock ALL controls on the form, so if you
have any controls you still want active, e.g. a command button to close the
form, you'd need to exclude them. The best way to do this is to set the Tag
property of any controls you want to remain active to:

KeepMeActive

You can then amend the code:

Dim ctrl As Control

On Error Resume Next
For Each ctrl In Me.Controls
If ctrl.Tag <> "KeepMeActive" Then
ctrl.Enabled = Not Me.NewRecord
ctrl.Locked = Me.NewRecord
End If
Next ctrl

Ken Sheridan
Stafford, England

 

[Pwyd]

Can you explain to me why an autonumber primary key would not function in
such a case? The database opens up to a filtered set of records for just
that user. Adding a new record was actually put onto a seperate form through
a command button, as i wanted to restrict them from editing old records. Why
would saving a single empty record cause any particular harm?