Validation to assure there is a subform record

[BruceM]

I have a standard sort of Purchase Order system, with a main form based on
the PO table and a subform based on a LineItems table. I would like to
prevent users from navigating away from a Purchase Order record before at
least on LineItem has been entered, but if I try to use the main form's
Before Update event it of course fires as soon as the user clicks into the
subform control to add a LineItem record. There are some other times when
Before Update is triggered as well, so my question is whether there is a
reasonable strategy for validating when the user attempts to go to another
record (or close the form). One possibility is that I am using custom
buttons for navigation and to close the form, so perhaps I could call
validation code from those buttons.
In a related question, The PO system is tied in with a Vendor database. A
PO is started by selecting a vendor from a combo box list (VendorID is the
bound field; the vendor name is for display only). The Vendor table and the
PO table are related through VendorID, by the way (typical setup). The rest
of the vendor information (address, phone, etc.) appears in a subform. I
use a subform because the Vendor table has a related Phone table. If the
Phone table is included in the PO form's Record Source query (for display
only; I am only adding and updating data in the PO table, not in the Vendor
or Phone table) the query becomes non-updatable. The simplest solution was
to put all Vendor information, including from the related Phone table, into
a subform which can then be locked to prevent changes to the Vendor
information.
Anyhow, in order to see the rest of the Vendor information (on the subform)
I have been using Me.Refresh in the combo box After Update event (this is
the aforementioned combo box from which I select the Vendor to whom the PO
is being written). Refresh triggers the form's Before Update event (which I
may not be using for data validation anyhow), but I wonder if this is the
best way to get the rest of the Vendor information to appear. Also, there
is a button on the PO form to open the Products form. This is done if it is
discovered that an item is missing from the Product Description combo box on
the Line Items subform.
After entering the item into the Products form I need it to show up in the
Product Description combo box list. How should I go about forcing it to
appear? I can't seem to discover the method (maybe because I'm getting
tired) other than to navigate away from the PO record and back to it.

 

[Rick Brandt]

I have a standard sort of Purchase Order system, with a main form
based on the PO table and a subform based on a LineItems table. I
would like to prevent users from navigating away from a Purchase
Order record before at least on LineItem has been entered, but if I
try to use the main form's Before Update event it of course fires as
soon as the user clicks into the subform control to add a LineItem
record. There are some other times when Before Update is triggered
as well, so my question is whether there is a reasonable strategy for
validating when the user attempts to go to another record (or close
the form). One possibility is that I am using custom buttons for
navigation and to close the form, so perhaps I could call validation
code from those buttons.

That is one way, but you have to disable the NUMEROUS ways that a user can leave
a record without using your buttons.

PageUp
PageDown
Apply a filter (numerous places)
Remove a filter (numerous places)
Edit - Go To in the menu
Close the form
Close your app
Close Access
Close Windows
Yank the plug

Best you can do is check for recors with no children in multiple logical places
and alert the user when any are found. There is no way for the table or form
structure to force this requirement.

In a related question, The PO system is tied in with a Vendor
database. A PO is started by selecting a vendor from a combo box [snip]
Anyhow, in order to see the rest of the Vendor information (on the
subform) I have been using Me.Refresh in the combo box After Update
event (this is the aforementioned combo box from which I select the
Vendor to whom the PO is being written). Refresh triggers the form's
Before Update event (which I may not be using for data validation
anyhow), but I wonder if this is the best way to get the rest of the
Vendor information to appear. [snip]

If you set the subform's MasterLink property to the ComboBox's name it should
change without any refresh code. If for some reason it doesn't then you don't
need to refresh the whole form, just Requery the subform...

Me.SubformControlName.Requery

 

[BruceM]

Thanks for the reply. It seems then that there is no simple way of assuring
that there is at least one subform record. Form-level validation is out of
the question, it seems, since any attempt to enter the subform control saves
the main record. I suppose I could set a Boolean to True in the form's
Current event, then set it to false as the first line of code for the
navigation buttons, the Close button, and so forth, and run the form's
Before Update event only if the Boolean is False, or something like that.
There is no menu, and I intend to make it so that the only way of closing
the form or Access is with a command button. I can accept the possibility
of no subform records (which is a nuisance, but not much of a problem) in
case of PageUp, PageDown, a manually applied filter (right click menu), or
shutting down the computer. However, I think I'll wait to see if it's
necessary. I will almost without doubt be vexed by things I haven't yet
considered, so unless there proves to be a need for that sort of validation
I won't make the attempt. I had hoped there would be a relatively simple
solution.
Thanks for the pointer about setting the MasterLink property. I was a bit
puzzled at first, as the control wasn't on the subform control's property
sheet listing of choices for that property, but I typed in the control name
and it seems to work. The ChildLink property is still set to VendorID
(previously both had been set to VendorID). I also tried requerying the
subform control, which also worked, and which no doubt will prove to be a
useful piece of information in the future.
I am going to be away from my office and the computer until after the new
year begins, so if you reply to this posting please don't think me rude if I
don't respond right away.

I have a standard sort of Purchase Order system, with a main form
based on the PO table and a subform based on a LineItems table. I
would like to prevent users from navigating away from a Purchase
Order record before at least on LineItem has been entered, but if I
try to use the main form's Before Update event it of course fires as
soon as the user clicks into the subform control to add a LineItem
record. There are some other times when Before Update is triggered
as well, so my question is whether there is a reasonable strategy for
validating when the user attempts to go to another record (or close
the form). One possibility is that I am using custom buttons for
navigation and to close the form, so perhaps I could call validation
code from those buttons.

That is one way, but you have to disable the NUMEROUS ways that a user can
leave a record without using your buttons.

PageUp
PageDown
Apply a filter (numerous places)
Remove a filter (numerous places)
Edit - Go To in the menu
Close the form
Close your app
Close Access
Close Windows
Yank the plug

Best you can do is check for recors with no children in multiple logical
places and alert the user when any are found. There is no way for the
table or form structure to force this requirement.

In a related question, The PO system is tied in with a Vendor
database. A PO is started by selecting a vendor from a combo box [snip]
Anyhow, in order to see the rest of the Vendor information (on the
subform) I have been using Me.Refresh in the combo box After Update
event (this is the aforementioned combo box from which I select the
Vendor to whom the PO is being written). Refresh triggers the form's
Before Update event (which I may not be using for data validation
anyhow), but I wonder if this is the best way to get the rest of the
Vendor information to appear. [snip]

If you set the subform's MasterLink property to the ComboBox's name it
should change without any refresh code. If for some reason it doesn't
then you don't need to refresh the whole form, just Requery the subform...

Me.SubformControlName.Requery