How do I ensure an entry is made on the subform?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

When a user creates a new record in the main form, at least one record is
required in the subform.

How can I enforce this?

For example, if my main form is an order and my subform contains line items
(parts), how can I ensure that a user does not create an order without at
least one associated part?
 
This is a chicken and egg problem.

You cannot create a record in the subform until AFTER the main form record
has been created. It follows that you cannot require that a subform record
is created BEFORE you accept the record in the main form.

If it is absolutely essential that your rule is enforced, you will have to
accept the records into a pair of temporary tables, and only permit them to
be written to the real tables programmatically and within a transaction if
both are present. In most cases the work to create and manage this is
unjustified. Especially if you will be allowing users to simultaneously edit
and delete existing records, the process of managing the temporary buffers
is involved.

Another workaround is to use the AfterInsert event of the main form to
programmatically create the first record in the subform. While it meets the
requirement of getting a subform record, the data is completely
meaningless--probably worse than not having it.

If you are trying to work around the fact that the main form record don't
show in a query if there are no subform records, use an outer join in the
query. In query design view, double-click the line joining the 2 tables.
 
Thanks Allen for making that clear.

I guess what I need is a "BeforeCurrent" event that would fire when the user
tried to move off the current record. I could then check to ensure a
"complete" order was being entered.

As it stands, I must go either do some complex coding or simply live with
the possibility that users can create incomplete orders (a header without
any line items). But, as you pointed out, these orders will not get picked
up in a query unless I do an outer join. Still, incomplete orders in the
database does make me a little uncomfortable.

Thanks again for your help.
Mike
 
-----Original Message-----
Thanks Allen for making that clear.

I guess what I need is a "BeforeCurrent" event that would fire when the user
tried to move off the current record. I could then check to ensure a
"complete" order was being entered.

As it stands, I must go either do some complex coding or simply live with
the possibility that users can create incomplete orders (a header without
any line items). But, as you pointed out, these orders will not get picked
up in a query unless I do an outer join. Still, incomplete orders in the
database does make me a little uncomfortable.

Thanks again for your help.
Mike



and only permit them
to to simultaneously
edit subform. While it meets
the at least one record
is create an order without
at


.
Click to expand...
I don't know if this would be of interest to you, but you
might try setting the visible property of the subform to
false when the user add a new record in the main form.
Then set the visible property of the subform to true only
after the appropriate (requires data) has been entered
into the main form.

Just a suggestion.

Byron
 
Dear Mike:

Once upon a time I posted with a similar concern. In my case, I wanted to
ensure when a book was entered into my library database, an author *had* to
be entered for that book in a subform. The event that I needed was
"OnRecordExit", which isn't available (although the help file might say it
is...).

At the time, Dirk Goldgar was kind enough to work with me to create a
process for ensuring that a subform contained a value whenever a record was
created. It was some time ago, and I am not a good enough programmer to
recreate this code for myself, but if you care to follow the original thread
you will see what Dirk came up with.

The thread is in microsoft.public.access.forms, and if you do a Google
groups search of this thread with the search term "Repost: Data
validation..." you should find it.

or..

Here is the link.. watch for line wrapping..

http://groups.google.ca/groups?hl=e...Search&meta=group%3Dmicrosoft.public.access.*

If you have questions, post back here and I will try to answer them... I
*might* be able to find a sample of my application with this code, and I
would be happy to send it to you, if you like.

HTH
Fred Boer
 
Mike,

Though I totally agree with Allen Browne's remarks about the chicken and egg
of subforms and forms, it strikes me that the problem, if somewhat reposed,
can be solved.

Consider...

If you are building and order submission system, do you have an explicit
event where the order becomes "submitted"?

If so, do you want your users to be able to save orders without "submitting"
them? Maybe they like to "compose" them over the course of a few sessions?

If so, will you encode the status of the order in a field (Maybe they have
an [order status] that "advances" from "Editting" to "Submitted"?)

If so, will you have some sort of button (say, cmdSubmit) to set the [order
status]?

If so, then, put your test for subform records as a precondition to
[cmdSubmit]! Something like:

If 0 = Me.subfrm_lineitem.Form.Recordset.RecordCount Then
MsgBox Me.subfrm_lineitem.Form.caption & " can not be empty! Submit
Cancelled"
else
[order status] = "Submitted"
end if

What think you?
 
Fred Boer said:
Dear Mike:

Once upon a time I posted with a similar concern. In my case, I
wanted to
ensure when a book was entered into my library database, an author
*had* to
be entered for that book in a subform. The event that I needed was
"OnRecordExit", which isn't available (although the help file might
say it
is...).

At the time, Dirk Goldgar was kind enough to work with me to create a
process for ensuring that a subform contained a value whenever a
record was
created. It was some time ago, and I am not a good enough programmer
to
recreate this code for myself, but if you care to follow the original
thread
you will see what Dirk came up with.

The thread is in microsoft.public.access.forms, and if you do a Google
groups search of this thread with the search term "Repost: Data
validation..." you should find it.

or..

Here is the link.. watch for line wrapping..

http://groups.google.ca/groups?hl=e...Search&meta=group%3Dmicrosoft.public.access.*

If you have questions, post back here and I will try to answer
them... I *might* be able to find a sample of my application with
this code, and I
would be happy to send it to you, if you like.
Click to expand...

I'm glad you posted that, Fred. Mike's question rang a bell, but I
wasn't sure I could lay my hands on the code we worked out.
 
Mike, You could use the all or nothing approach. If the user opts not to
add parts then don't save the main record. Design a delete query based on
orders table you use for your main form. Set the primary id field criteria
equal to the ID field on your form (i.e.. =[Forms]![OrderForm]![OrderID]).
Now on the unload event Check to see if the Parts subform has records. If
not give the user a chance to add some or lose everything.


Private Sub Form_Unload(Cancel As Integer)
If Not (Me![sfrmParts].Form.RecordsetClone.RecordCount > 0) Then
If MsgBox("Missing data. Do you want to add it now?" & vbCrLf & _
"If you select no this invoice will be deleted!", _
vbYesNo + vbInformation, "Missing Data") = vbYes Then
Cancel = True
Me![sfrmParts].SetFocus
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelInvoice", , acEdit
DoCmd.SetWarnings True
End If
End If

End Sub
 
Back
Top