Validating Data on form with sub form

[Lee Kennedy]

Hello all



I have a form (Orders) with a subform attached (CallOffs). The form is used
to record orders of stationery items with a supplier. When a new order is
created not all of the items ordered will be delivered straightaway but may
be 'called off' in batches of 3 or 4 over a period of a few months. At the
point of order, one call off batch must be requested.



I've set this up as:

Orders PK is OrderNumber

CallOffs PK is CallOffID, FK is OrderNumber



I am now having trouble validating the order process for new orders. I tried
to use the dcount function to determine whether there are any CallOff
records with the OrderNumber on the form and I added this to a 'Close Form'
button. Although this seemed to work initially, I found I could just move to
the next record and then close the form, bypassing the validation I'd set
up. Do I have to change any settings on the form to restrict the user moving
to a new or blank record? (How do I do this?) Or is it an event procedure
like BeforeUpdate on the Orders form that I need to use?



If anyone can offer any suggestions on how I proceed, I'd really appreciate
it!!



Thank you



Lee

 

[Steve Schapel]

Lee,

If you set the Navigation Buttons property of the main form to No, and
its Cycle property to Current Record, the user will not be able to move
to a new record. If you want them to be able to move to another record
or new record, you will need to provide this functionality yourself,
maybe via additional command buttons on the form... but then you will be
able to put your validation code on these buttons as well as the Close
Form button.

 

[PangoKen]

Hello Steve

I tried your suggestion, but I still seem to be able to navigate to the
next record using the page up/down buttons or the mouse wheel.

Can you offer any further advice?

thank you

Lee

 

[Steve Schapel]

Lee,

Well, I think there are ways around these. As regards the mouse wheel,
there is info here that will help...
http://www.lebans.com/mousewheelonoff.htm
As regards the PageUp and PageDown buttons, to be honest I wasn't aware
that this would happen, but on checking I see you are right. I have
heard that public floggings for anyone suspected of touching these
buttons can be effective. (Only kidding!) It would be possible to use
code to trap these keystrokes.

But this all starts to get messy. I recommend going back a step here,
and consider what you are doing with this form. You are only ever
working with one main form record at a time, correct? How can we set
things up so that only that record is available to the form? I guess it
partly depends how you are getting to that specific record in the first
place. Can the form be based on a query that only returns the required
record? Or a filter applied so that only the required record is shown.
THat way, there's no such thing as "another record", and if you set
the form's AllowAdditions property to No, neither is there any such
thing as a "new record".

Does that make any sense?

 

[PangoKen]

Hi Steve

hhmmm public floggings. sounds good i'll need to check with HR!!!

I've had a think about it and you're right, i needed to review how the
form would be used. There'll only ever be need to be the kind of
validation i discussed in my earlier post and that's when there is a
brand new order, one that hasn't been added to the 'Orders' table yet.
I'm just having trouble working out how i ensure a calloff record is
created against an order because if i trap the validation with
"BeforeUpdate" and open up a new form in dialog mode to enter the call
off details, I get an error telling me there must be a related record
in the 'Orders' table. if i try to use ''AfterUpdate" then there is no
way to guarantee that a calloff record would be created against the
order record unless i remove the control box on the form and don't give
the user an option to cancel (which i'll happily consider if need
be...).

The other circumstance will already have the order and a calloff added
so if i need to add another calloff record against the order, all I'll
need to ensure is that the call off quantity doesn't exceed the
original order quantity and i can do that using the 'BeforeUpdate'
event and dsum.

if i can't use "BeforeUpdate" and HR give me the thumbs down on public
floggings is there a way i could combine all the details i need for
both the new order and the 1st call off details on an unbound form and
update both tables seperately? or am i missing something *really*
obvious?

thanks again

Lee

 

[Steve Schapel]

Lee,

No, I don't think you are missing anything obvious. In fact, it's not
all that common a request, in my experience, to require a subform record.

There will be a way to achieve it. How to proceed depends a bit on your
other data requirements. For example, when creating a new record on the
main form, it may be possible to do this... On the After Update event
of the OrderNumber, you could use code to:
- force an initial save of the new Orders record
- change the Record Source of the Orders form to only that order
- set the form's AllowAdditions to False
That should take care of the PageUp key, if the floggings don't work.
In other words, we're back to a model of your command buttons being the
only way to leave the current Order, and hence you control this
according to the existence of a subform record. But there are
circumstances where this approach will be heavy duty, for example if the
Orders also has a lot of other Required fields, or Validation Rules, or
whatever, which means the new record can't be saved until a bunch of
data is entered.

Another idea is this... Do you really need to dong them as they try to
leave each new Order record? Maybe you could do your check at the end
of the session, so you could use the Unload event of the form, for
example. On the basis of a query to identify any Orders where there are
no CallOffs, and if there are, Cancel the unload, and throw up a
threatening MsgBox or form. I expect this would be effective, but
again, applicablilty depends on your business process.

Anything here you can use?

 

[PangoKen]

Hi Steve

I'm gonna go with the unload form option. That way i give them the
benefit of doubt and only force them with code when necessary.

thanks for all your help

Lee