Testing for blank records in a subform

[Bretona10]

Hi all, I have a form with a subform. I dont want records saved when the
subform is empty. I open the form in data entry mode. The master and sub are
related in a one to many relationship
Any ideas how to do this best?

Thanks,
Bret

 

[tina]

well, when you move the focus from the mainform into the subform, the
mainform record is automatically saved, whether a new record or changes to
an existing record. the only way to stop that is to add code to the
mainform's BeforeUpdate event procedure to ask the user if the record should
be saved, and if not, to cancel the update - which will also cancel the move
into the subform, by the way.

hth

 

[Allen Browne]

Can't do. It's a chicken'n'egg problem.

Normally the main form is bound to a table, and the subform to a related
table. You cannot create a *related* record in the related table until after
the main record is in the main table (so it has something to relate to.)
Consequently, you cannot require that a related record exists before you
allow the main record to be created.

 

[Bretona10]

Thanks for the input, isnt there code or command I can use to test for a
blank or null record in the subform? I can call it in the beforeupdate
procedure of the main form.

 

[Allen Browne]

Sure you can do that, and block the record in the main form.

As explained, you can't create a related record in the subform until the
record in the main form exists. And now you can't create the main form
record either until you have a record in the subform. Therefore you can't
create any records.

 

[tina]

i think you're not quite getting what Allen and i are telling you, hon. yes,
you could check for subform records before updating a mainform record. you
might have a valid business reason to do that before *changing an EXISTING
record in the main form*. but there is no point checking for subform records
before saving a NEW record in the mainform - there won't be any subform
records, period. well, okay, unless the subform records aren't related to
the mainform records at the table level OR the form level. but it would be
really, really odd to have a mainform/subform setup to enter unrelated
records. how about giving us some details of what you're trying to
accomplish, rather than just the narrow parameters of the immediate
question?

hth

 

[Bretona10]

Hi Again, here is more info. I dont want the master forms record saved, if
the user did not put any information in the subform fields.
Hmm I have a one to many relationship between the two tables underlying the
forms with referential integrity checked, I thought that would help stop this
situation, but I guess it only works in the opposite way when deleteing
records in the "one" side table.

Does this help explain better?

Thanks,

 

[Bretona10]

Oh wait, so maybe its better to make the check while in the subform record?
But for that to work correctly I would need to make sure the user actually is
moved to the subform at some point. This must be a common issue where you
have master table records stored without any related sub table records via a
form, which is in essence a useless record. Think of it like a order entry
form with the detail table being left empty, ie no items to be ordered.

Thanks,

 

[tina]

This must be a common issue where you

have master table records stored without any related sub table records via a
form, which is in essence a useless record.

well, that's a pretty sweeping statement, hon, and not correct. there are
many applications where some records in a parent table have one or more
related records in a child table, while other records in the parent table
legitimately do not.

for your scenario, i suppose if you can control the user's movement to the
next/previous/new record in the main form, or the user's closure of the main
form, allowing these actions *only* from command buttons on the form, you
could then test for records in the subform before allowing the movement. for
instance,

If Me!SubformName.Form.RecordsetClone.RecordCount < 1 Then
Exit Sub
Else
' put here the code that would perform the close or move action
End If

but i have to say, it's not easy to restrict movement in a form from record
to record, because there are so many ways for a user to move between
records - including the Tab button, the keyboard arrow keys, the keyboard
page up/down keys, a mouse scrollwheel, and probably more i'm not even
thinking of.

hth

 

[Mike Painter]

This must be a common issue where you

The very concept of a master record precludes this.
It contains information that is common to any related records if they exist.
A "worse case" woud be a situation where an invoice was created, but then
abandoned.
In that case the master record would be deleted.
But that is a function of the end user and not the database.

If the master record is dependant on the subforms then there is something
wrong with the design.

for your scenario, i suppose if you can control the user's movement
to the next/previous/new record in the main form, or the user's
closure of the main form, allowing these actions *only* from command
buttons on the form, you could then test for records in the subform
before allowing the movement. for instance,

If Me!SubformName.Form.RecordsetClone.RecordCount < 1 Then
Exit Sub
Else
' put here the code that would perform the close or move action
End If

The problem with this is that the master record will be saved as soon as
they enter a subform.