Problem with subform data entered before main form

[Jeff Conrad]

Hi,

Using Access 97 here.

I redesigned a couple of tables and added a couple of many-
to-many relationships. I added a tab control to my form so
the first tab would hold the main table's data and the
second tab would hold the two subforms. Each subform is
one-to-many with the main table. Everything works fine and
the data is populated correctly.

The problem I have found is when I go to a new record and
attempt to enter data into any of the two subforms BEFORE
entering data into the main table (form). I naturally get
the Access error saying:

"The field tblVendorOrderDates.VendorID can't contain a
null value because the Required property for this field is
set to True. Enter a value in this field."

Now this is not good. To my low-tech users this might as
well be ancient Greek. The possibility of them doing this
is quite real so I need to find a way around this.

I tried to trap for Error 3314 (Null key) in EACH subform
AND the main form's Form_Error event, but the Access error
comes up first!! After you click OK then my custom message
comes up. Not good.

What is the best way to handle this? Should I hide the
second tab if they are on a new record? When and how would
I make it visible again? Which event?

Any ideas are most welcome.
Thanks,
Jeff Conrad
Bend, Oregon

 

[Dirk Goldgar]

Hi,

Using Access 97 here.

I redesigned a couple of tables and added a couple of many-
to-many relationships. I added a tab control to my form so
the first tab would hold the main table's data and the
second tab would hold the two subforms. Each subform is
one-to-many with the main table. Everything works fine and
the data is populated correctly.

The problem I have found is when I go to a new record and
attempt to enter data into any of the two subforms BEFORE
entering data into the main table (form). I naturally get
the Access error saying:

"The field tblVendorOrderDates.VendorID can't contain a
null value because the Required property for this field is
set to True. Enter a value in this field."

Now this is not good. To my low-tech users this might as
well be ancient Greek. The possibility of them doing this
is quite real so I need to find a way around this.

I tried to trap for Error 3314 (Null key) in EACH subform
AND the main form's Form_Error event, but the Access error
comes up first!! After you click OK then my custom message
comes up. Not good.

What is the best way to handle this? Should I hide the
second tab if they are on a new record? When and how would
I make it visible again? Which event?

Any ideas are most welcome.
Thanks,
Jeff Conrad
Bend, Oregon

Jeff -

You could certainly follow your proposed course by using code in the
main form's Current and AfterInsert events, hiding the tab in the
Current event if Me.NewRecord is True, and showing it in the AfterInsert
event. However, that almost requires you to put a Save button on the
form to cause the record to be saved and the AfterInsert event to fire,
so that the tab can be made visible again. An alternative approach is
to have AfterUpdate event procedures for all required fields on the main
form, each procedure checking that all required fields (not just the one
that was updated) contain valid data. If they do, make the tab visible.

An alternative approach, and one that I have used, is to leave the tab
visible but use the Enter event of the subform control to test whether
the Link Master Fields on the parent form are Null. If they are, then
display a message "Please fill out the main fields first!", and set the
focus back to an appropriate field on the first, "main" tab. That way
the user is never able to attempt to enter data on the subform until the
necessary main record has been created.

I don't like this latter method quite as much as one that never lets the
user even *see* a subform that can't be filled out yet, but the coding
is simpler.

 

[Jeff Conrad]

Hi Yoda!

Thanks for your time and help.
Comments below.

You could certainly follow your proposed course by using
code in the main form's Current and AfterInsert events,
hiding the tab in the Current event if Me.NewRecord is
True, and showing it in the AfterInsert event. However,
that almost requires you to put a Save button on the
form to cause the record to be saved and the AfterInsert
event to fire, so that the tab can be made visible again.

Not to sure I like the idea of having to add a save
button. This could become counter-productive and the
second tab could easily be missed. Information in the
second tab subforms are not necessary, but most likely
will be filled in.

An alternative approach is to have AfterUpdate event
procedures for all required fields on the main
form, each procedure checking that all required fields
(not just the one that was updated) contain valid data.
If they do, make the tab visible.

Yikes, that sounds like a lot of coding and also a lot
of 'stuff' happening in the background all the time.

An alternative approach, and one that I have used, is to
leave the tab visible but use the Enter event of the
subform control to test whether the Link Master Fields on
the parent form are Null. If they are, then
display a message "Please fill out the main fields
first!", and set the focus back to an appropriate field
on the first, "main" tab. That way the user is never
able to attempt to enter data on the subform until the
necessary main record has been created.

I like the sound of this and tried this out instead of
hiding the second tab. I did an IsNull test on txtVendorID
(the hidden autonumber PK field on the main tab) in the
Enter event of the subform *control* itself. Is that what
you were talking about? I put a nice message box up and
then immediately return focus to the Vendor Name field on
the main tab. This seems to work perfectly. No Access
error messages are raised at all. Sweet! I do like this
better than hiding the tab itself and it was a LOT less
coding I'm sure. The chances of the users doing this are
slim, but I still wanted to account for the possibility.

I don't like this latter method quite as much as one that
never lets the user even *see* a subform that can't be
filled out yet, but the coding is simpler.

Are you saying you don't like the idea of hiding/unhiding
the tab or you don't like the solution I tried above??

Thanks,
Jeff Conrad
Bend, Oregon

 

[Dirk Goldgar]

Hi Yoda!

Thanks for your time and help.
Comments below.

And mine, also inline.

Not to sure I like the idea of having to add a save
button. This could become counter-productive and the
second tab could easily be missed. Information in the
second tab subforms are not necessary, but most likely
will be filled in.

I don't like save buttons, either, so we'll forget that aproach.

Yikes, that sounds like a lot of coding and also a lot
of 'stuff' happening in the background all the time.

It could be, but you *could* write a single function in the form's
module and set various controls' AfterUpdate event properties to call
it, using the expression syntax "=MaybeShowSubforms()".

I like the sound of this and tried this out instead of
hiding the second tab. I did an IsNull test on txtVendorID
(the hidden autonumber PK field on the main tab) in the
Enter event of the subform *control* itself. Is that what
you were talking about? I put a nice message box up and
then immediately return focus to the Vendor Name field on
the main tab. This seems to work perfectly. No Access
error messages are raised at all. Sweet! I do like this
better than hiding the tab itself and it was a LOT less
coding I'm sure. The chances of the users doing this are
slim, but I still wanted to account for the possibility.

Very good, and it fits in better now that I know that "Information in
the second tab subforms are not necessary, but most likely will be
filled in."

Are you saying you don't like the idea of hiding/unhiding
the tab or you don't like the solution I tried above??

What I was saying was that I prefer in principle not to let users try to
do things that I know in advance I'm not going to let them do. Thus,
hiding or disabling the subforms until it's legal to fill them in is
attractive. Against that you can set the relative undesirability of
having user-interface elements "pop in and out of existence", from the
user's point of view, and the more complex code involved, which is thus
more fragile. So while I'd prefer a solution that kept users out of the
subforms completely until they're allowed to fill them in, I'm pretty
happy with your solution.

Hmm, since these subforms are on a second tab of a tab control on the
form, it might be that you can put your code into the Change event of
the tab control. That is, you can keep users from even changing to that
tab until you're willing to let the users fill in the subforms.

 

[Jeff Conrad]

Hi Yoda!

I don't like save buttons, either, so we'll forget that
approach.

I do have a few save buttons, but they are primarily for
unbound forms. Didn't really want them here.

It could be, but you *could* write a single function in
the form's module and set various controls' AfterUpdate
event properties to call it, using the expression
syntax "=MaybeShowSubforms()".

I see. I thought it would require lots of code in each
procedure. I keep forgetting the big picture.

Very good, and it fits in better now that I know
that "Information in the second tab subforms are not
necessary, but most likely will be filled in."

I suppose I could have explained the purpose for the
subforms. One is for Vendor Order Days and the other is
Vendor Delivery Days (food service situation). You can
enter what days you have to call-in/make your orders and
what days they deliver. Each is a many-to-many
relationship between tblVendors and tblWeekDays. The
tblWeekDays only has seven records like:
1 Sunday
2 Monday
....etc
So the other two subforms on the second tab feed
tblVendorOrderDays and tblVendorDeliveryDays. Only the
number is saved into the joining tables. Make sense?

Not all vendors are food suppliers so not every vendor in
the database will have order days and delivery days. So
the subforms will not be needed in all situations.

Using this table information I was able to make a SWEET
calendar-style report listing the seven day
orders/deliveries. I used Duane Hookom's samples as a
guide. I'm getting many frequent flyer miles with those
samples!

What I was saying was that I prefer in principle not to
let users try to do things that I know in advance I'm not
going to let them do.

Oh Ok, I follow you now.

Thus, hiding or disabling the subforms until it's legal
to fill them in is attractive.

Understandable. I actually do that in another part of the
database, which coincidentally you helped me fix!

Against that you can set the relative undesirability of
having user-interface elements "pop in and out of
existence", from the user's point of view, and the more
complex code involved, which is thus more fragile. So
while I'd prefer a solution that kept users out of the
subforms completely until they're allowed to fill them
in, I'm pretty happy with your solution.

You're very wise Master!

I think this solution will work OK here. In order to
trigger the nice message box you have to use the
navigation buttons to get to a new record and then go to
the second tab. Still possible to do, but slim. If
however, this message box was popping up constantly the
users would get pretty annoyed! I do think that's what
you're trying to say and that would of course necessitate
an interface change.

Hmm, since these subforms are on a second tab of a tab
control on the form, it might be that you can put your
code into the Change event of the tab control. That is,
you can keep users from even changing to that
tab until you're willing to let the users fill in the
subforms.

OK, I tried this, but the first solution works much
better!! Here's why.
I commented out the previous code I just put in.
I put the same code in the Change event of the Tab
Control. That's where you wanted the code right?
If I go to the second tab on a new record I can see the
subforms (which is no different than the previous test)
and the message box comes up immediately. I click OK,
focus goes to the vendor name field on the first tab, and
the message box comes up again! Why? Because the Change
event fires again!!

Now THAT would definitely get annoying for the users!!

I think I'll go with the first solution since it seems to
work quite well given the circumstances.

Again, thanks for all the help (head bowed).
Jeff Conrad
Bend, Oregon

 

[Dirk Goldgar]

I suppose I could have explained the purpose for the
subforms. One is for Vendor Order Days and the other is
Vendor Delivery Days (food service situation). You can
enter what days you have to call-in/make your orders and
what days they deliver. Each is a many-to-many
relationship between tblVendors and tblWeekDays. The
tblWeekDays only has seven records like:
1 Sunday
2 Monday
...etc
So the other two subforms on the second tab feed
tblVendorOrderDays and tblVendorDeliveryDays. Only the
number is saved into the joining tables. Make sense?
Yep.

Not all vendors are food suppliers so not every vendor in
the database will have order days and delivery days. So
the subforms will not be needed in all situations.

I understand.

Using this table information I was able to make a SWEET
calendar-style report listing the seven day
orders/deliveries. I used Duane Hookom's samples as a
guide. I'm getting many frequent flyer miles with those
samples!

Very nice. Duane will be proud.

OK, I tried this, but the first solution works much
better!! Here's why.
I commented out the previous code I just put in.
I put the same code in the Change event of the Tab
Control. That's where you wanted the code right?
If I go to the second tab on a new record I can see the
subforms (which is no different than the previous test)
and the message box comes up immediately. I click OK,
focus goes to the vendor name field on the first tab, and
the message box comes up again! Why? Because the Change
event fires again!!

Now THAT would definitely get annoying for the users!!

It would, but that's not exactly what I had in mind. What I had in mind
was something like this:

'------ start of example code ------
Private Sub tabMyTab_Change()

If Me.tabMyTab = 1 Then
' We've moved to the second tab page.
' Is the VendorID on the first page still Null?
If IsNull(Me.VendorID) Then
' Go right back to the first page, effectively
' disabling the second tab until the first page
' has been filled out.
Me.tabMyTab = 0
End If
End If

End Sub
'------ end of example code ------

I think I'll go with the first solution since it seems to
work quite well given the circumstances.

And that's fine, too. I'm just thinking out loud here.

 

[Fred Boer]

Well, thanks a lot, you two!

As I was reading this thread I started thinking to myself... hmm.. *I've*
got a main form with three tabs holding subforms with many-to-many
relationships, too! But I never encountered this before.. So I tried it.
Well, I never encountered it because I never tried doing it! When I tried
opening a new record and then immediately attempting to enter data in one of
the subforms....yeah... there was an error message... Well, ignorance *was*
bliss...

Funny thing is that I have two student library helpers and they've been
entering data for me for some time now, and they've never done this either.
A tribute to my good training perhaps! <g>

Ok, so Monday at lunch time I'll have to dig into this new problem! Luckily,
I've got this thread to guide me!

Cheers!
Fred

 

[Jeff Conrad]

Hi Yoda!

It would, but that's not exactly what I had in mind.
What I had in mind was something like this:

'------ start of example code ------
Private Sub tabMyTab_Change()

If Me.tabMyTab = 1 Then
' We've moved to the second tab page.
' Is the VendorID on the first page still Null?
If IsNull(Me.VendorID) Then
' Go right back to the first page, effectively
' disabling the second tab until the first page
' has been filled out.
Me.tabMyTab = 0
End If
End If

End Sub
'------ end of example code ------

Ok, I tried this to see what would happen. I only had to
make some very minor changes to the control names, but I
got a different error. Going to a new record and then
clicking on the second tab produced Error #2448, "You
can't assign a value to this object." Strange.

I did verify that the page indexes of the tab control are
0 and 1 respectively so I'm not sure where the problem is.
Any idea what the problem may be here?

Thanks,
Jeff Conrad
Bend, Oregon

 

[Jeff Conrad]

Hi Fred,

Well, thanks a lot, you two!

As I was reading this thread I started thinking to
myself... hmm.. *I've* got a main form with three tabs
holding subforms with many-to-many relationships, too!
But I never encountered this before.. So I tried it.
Well, I never encountered it because I never tried doing
it! When I tried opening a new record and then
immediately attempting to enter data in one of
the subforms....yeah... there was an error message...
Well, ignorance *was* bliss...

You'd be surprised all the things a user *could* do
navigating through an application! That's why I
continually test and test and test and.....

Funny thing is that I have two student library helpers
and they've been entering data for me for some time now,
and they've never done this either.
A tribute to my good training perhaps! <g>

Naturally.

......wait, are you implying?..... ;-)

Ok, so Monday at lunch time I'll have to dig into this
new problem! Luckily, I've got this thread to guide me!

Job security Fred, job security.

Jeff Conrad
Bend, Oregon

 

[Jeff Conrad]

Hi Yoda,

OK, figured out a solution.

Here is the revised code:

If Me.tabMyTab = 1 Then
' We've moved to the second tab page.
' Is the VendorID on the first page still Null?
If IsNull(Me.txtVendorID) Then
' Go right back to the first page, effectively
' disabling the second tab until the first page
' has been filled out.
Me.pgDetails.SetFocus
MsgBox "Yadda yadda"
Me.txtVendorName.SetFocus
End If
End If

End Sub

Now this is quite interesting!
Using this technique the user never even sees the other
subforms because they are taken back to the first tab in
the blink of an eye! Cool, now I just need to decide which
option I want to use.

By the way, I just happened to find another problem on
this form through extra testing. Sigh... :-(
Should I start a new thread or continue on?

Your help is very much appreciated,
Jeff Conrad
Bend, Oregon

 

[Dirk Goldgar]

Hi Yoda!


Ok, I tried this to see what would happen. I only had to
make some very minor changes to the control names, but I
got a different error. Going to a new record and then
clicking on the second tab produced Error #2448, "You
can't assign a value to this object." Strange.

I did verify that the page indexes of the tab control are
0 and 1 respectively so I'm not sure where the problem is.
Any idea what the problem may be here?

Not offhand. It works just fine in my simple test. You don't have the
form's AllowEdits property set to no, by any chance? Would you care to
post more details of the form, controls, and code?

One thing I should mention is that you're going to also want to have the
form's Current event set the tab to the first page. Otherwise, if a
user is on the second tab page for an existing record and then goes to
fill out a new record, they'll find themselves on the second tab page
for that record, too.

 

[Dirk Goldgar]

Hi Yoda,

OK, figured out a solution.

Here is the revised code:

If Me.tabMyTab = 1 Then
' We've moved to the second tab page.
' Is the VendorID on the first page still Null?
If IsNull(Me.txtVendorID) Then
' Go right back to the first page, effectively
' disabling the second tab until the first page
' has been filled out.
Me.pgDetails.SetFocus
MsgBox "Yadda yadda"
Me.txtVendorName.SetFocus
End If
End If

End Sub

Yes, that would work fine -- in fact, I imagine it would work fine even
like this:

If Me.tabMyTab = 1 Then
If IsNull(Me.txtVendorID) Then
Me.txtVendorName.SetFocus
MsgBox "Yadda yadda"
End If
End If

-- but I've never had any trouble changing pages on a tab control just
by setting the value of the control. I think something odd is going on
in your form.

By the way, I just happened to find another problem on
this form through extra testing. Sigh... :-(
Should I start a new thread or continue on?

Is it related to this problem? If not, start a new thread, but post the
subject and newsgroup here in this one, so I'll know where to look for
it.

 

[Jeff Conrad]

Hi Yoda!

Not offhand. It works just fine in my simple test. You
don't have the form's AllowEdits property set to no, by
any chance?

AllowEditions is Yes.

Would you care to post more details of the form,
controls, and code?

Got it fixed already as you noticed.

One thing I should mention is that you're going to also
want to have the form's Current event set the tab to the
first page. Otherwise, if a user is on the second tab
page for an existing record and then goes to
fill out a new record, they'll find themselves on the
second tab page for that record, too.

Good call Master!!!
I did not even notice that until you said something. Sure
enough the problem existed. Added the code to the Current
event as instructed and now it works perfect.

Thanks again for all your help and patience.

Jeff Conrad
Padawan Learner
Bend, Oregon

 

[Jeff Conrad]

Hi Yoda!

Yes, that would work fine -- in fact, I imagine it would
work fine even like this:

If Me.tabMyTab = 1 Then
If IsNull(Me.txtVendorID) Then
Me.txtVendorName.SetFocus
MsgBox "Yadda yadda"
End If
End If

Right as usual; it works just fine with that code.

-- but I've never had any trouble changing pages on a tab
control just by setting the value of the control. I
think something odd is going on in your form.

No, if anything is odd around here it's just me.

Is it related to this problem? If not, start a new
thread, but post the subject and newsgroup here in this
one, so I'll know where to look for it.

No, an unrelated problem with using an unbound combo box
to find records in the form's header. Problem came when on
a new record (clean untouched record) and then trying to
use the combo box. Code elsewhere said you needed to enter
a vendor name. Didn't like that. Worked through the
problem by adding some code check like so:

If Me.Form.NewRecord = True Then

Never have used the NewRecord property before, but was
able to solve the problem by using that code.

All is well now!
Thanks again Yoda, you da Man!
Jeff Conrad
Bend, Oregon