form problems

[Guest]

I have a main form with a subform. The subform is in datasheet view.
Basically what I am trying to prevent is a user from submitting their
information when one of the fields in the subform has a certain value (call
it aaa for ease). So, if they hit submit and the field that I am trying to
not let have aaa in it has it, they won't be able to move on. The problem I
am having is that it easy to test this for the last record inputted in the
datasheet, but not for previous ones (e.g. if they had entered two or more
records in the subform). So, I have been trying to play around with the
subform and the submit button that is on the mainform, but with no success!
It would be helpful if there was a Before On Current event for a form (e.g.
before they go to the next record, test if aaa is there), but I don't think
this event exists. Any help would be appreciated. Probably would be easiest
to put code in the submit button on the mainform, but don't know if its
possible to say "if any of the records in the datasheet view (not past
records already stored in the table, just ones they have just entered and are
still currentely showing in the subform) have aaa, then...."

 

[Lynn Trapp]

have you considered putting a field level validation on the table for that
field -- <> "aaa"

 

[Guest]

There are two such events
Before Update occurs before a record is updated.
Before Insert occurs be fpre a new record is added.
Either can be canceled with the statement

Cancel = True

 

[Guest]

I can't do that. I probably didn't explain the situation well enough. In my
subform (datasheet view), I have four cascading dropdowns. When one of the
previous dropdowns is modified, the latter ones will have this "aaa" placed
in them to help the user remember to modify their inputs since the dropdowns
before it has been changed. So, when this is displayed, the Required property
is satisfied so they can move on even though aaa in the dropdowns after then
one having been modified is not a valid entry for its field....I don't want
this to happen.

 

[Guest]

I tried this, but with no success. As I have just replied to Lynn, here is a
better background:

In my subform (datasheet view), I have four cascading dropdowns. When one of
the previous dropdowns is modified, the latter ones will have this "aaa"
placed in them to help the user remember to modify their inputs since the
dropdowns before it has been changed. So, when this is displayed, the
Required property is satisfied so they can move on even though aaa in the
dropdowns after then one having been modified is not a valid entry for its
field....I don't want this to happen.

 

[Guest]

Okay, so the basic problem is you want to know if a field in any record in
the subform contains the value "aaa".

Here is how you can test for that.

Dim rst As Recordset

Set rst = Me.MySubForm.Form.RecordsetClone

rst.FindFirst "SomeField = 'aaa'"
If Not rst.NoMatch Then
MsgBox "aaa alread exists"
End If
Set rst = Nothing

The above assumes the code would be executed in the Submit button you
mentioned.

 

[Guest]

So, would this only look for aaa in the subform data that is displayed on the
screen right now, or also in all of the records that this subform is linked
to (in its table that it writes into).

Thanks for your help and patience!

 

[Guest]

It will only include records in the subform form's recordset. So it would be
filtered to what you see.

 

[Guest]

Could I use this if I had more than one subform? How would the code change?

 

[Guest]

The logic is identical. The only changes would be the field name you are
using in the FindFirst and the correct delimiters for the data type of that
field.

 

[Guest]

Haha. You all make it sound and look so easy! That worked great, thanks so
much
One more question along these lines. These three subforms are used to
allocate certain number of parts to go certain places. I want to make sure
that they allocate at least 1 (e.g. make sure they don't forget to put in
some quantities). The twist is there are different quality of these parts.
For instance, out of 100 parts from inspection, they might get 30 green
(new), 40 blue (useable) and 30 red (scrap). Thus, I can't limit the field to
being greater than zero because they aren't guarenteed each color (for
instance it could have been all blue, 50 green 50 blue, etc.). So, is there a
way of saying that if for any given record in the three subforms they are
working with in this form, if the sum of the colors (for that record only) is
zero, then give them an error message? I think the type of code will be
somewhat similar?!??! I just haven't been able to figure this out! Thanks for
the help again