Check if records added to subform on a subform

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Re Access 2003
I have a form with a subform (#1). The subform has a subform (#2) and I need
to check that if the user moves off the current record on #1, #2 must have
least one record and, if not, cancel the update and move the focus to the
control on #2.

On the subform #1, I have tried using:-

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!frmAssignedCategories.Form!txtCategoryID) Then
MsgBox "You must assign at least 1 category before leaving this
record.", _
vbCritical, "Attention"
Cancel = True
Me!frmAssignedCategories.Form!txtCategoryID.SetFocus
End If

but this just throws up the warning message every time I try to move focus
to the #2 subform to enter at least on category. If I don't let cancel = True
and the users moves off the current record, that record is then left without
any categories on #2.

Does anyone have a solution to this?
Many thanks.
End Sub
 
Pete said:
Re Access 2003
I have a form with a subform (#1). The subform has a subform (#2) and I need
to check that if the user moves off the current record on #1, #2 must have
least one record and, if not, cancel the update and move the focus to the
control on #2.

On the subform #1, I have tried using:-

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!frmAssignedCategories.Form!txtCategoryID) Then
MsgBox "You must assign at least 1 category before leaving this
record.", _
vbCritical, "Attention"
Cancel = True
Me!frmAssignedCategories.Form!txtCategoryID.SetFocus
End If

but this just throws up the warning message every time I try to move focus
to the #2 subform to enter at least on category. If I don't let cancel = True
and the users moves off the current record, that record is then left without
any categories on #2.
Click to expand...


THere may be a solution to whatever your overall objective
happens to be, but there is no logical way to do it the way
you are trying to do it. A subform (#2) record can not be
started/created until the parent form (#1) record has been
saved. This is because subform records are expected to be
related to the parent form records and without an existing
parent form record you can not do anything to a related
subform record.

OTOH, if your subform (#2) records are not related to a
parent form (#1) record, then you do not really need to make
#2 a subform of #1.
 
The two forms are related by PhotoID: the bound table on #2 has PhotoID &
CategoryID which combine to make the primary key for #2
As soon as the user selects a photo on #1, an Autonumber is created for the
PhotoID.

I can make the mdb available if this would help in understanding the issue.
This cuurently has test data only. The only aside is that bmp's and jpegs
will be needed for testing.

Many thanks.
 
I understand the issue. The point is that #1's BeforeUpdate
event will be and must be done BEFORE the focus can even
move to subform #2 (long before any attempt is made to move
to another record in #1). So there is no point in trying to
get it to check if a #2 record already exists. If #1 is a
new record, then #2 can not have any records until the new
record in #1 has been saved.

It sounds like #2's table is a "junction" table to implement
a many to many relationship between photos and categories.
If that's so, then there is no logical reason why #2 must
have a record before #1 can save a record.
 
Your understanding is exactly right Marsh.

I have got the app to display a message that there should be at least one
category assigned to the photo in question, but I can't find a way to stop
the user moving off the record until at least one category is selected.
 
That's right and you will also get that message when you try
to add the first category. #1's BeforeUpdate event just
can't do it. You are searching for the non-existent before
record navigation event.

Short of some convoluted approach involving removing #2 and
you creating a bunch of code to simulate a bound subform, I
don't see a way to do what you want. I can not recommend
going to such lengths just to impose a restriction that
seems more than a little dubious.

If having a category fo every photo is so important, maybe
you should change the way the data is entered by making the
#1 form the category selection form and #2 where the user
adds/selects a photo??? This way, users must know and
specify a category before adding a new photo.
 
The main purpose (reason) for the app is so that users can find photos which
fit into the selected catefgories on the criteria selection form. So I need
to make sure that each photo has at least one category, otherewise those
photos will never be found.

Marshall Barton said:
That's right and you will also get that message when you try
to add the first category. #1's BeforeUpdate event just
can't do it. You are searching for the non-existent before
record navigation event.

Short of some convoluted approach involving removing #2 and
you creating a bunch of code to simulate a bound subform, I
don't see a way to do what you want. I can not recommend
going to such lengths just to impose a restriction that
seems more than a little dubious.

If having a category fo every photo is so important, maybe
you should change the way the data is entered by making the
#1 form the category selection form and #2 where the user
adds/selects a photo??? This way, users must know and
specify a category before adding a new photo.
--
Marsh
MVP [MS Access]

Your understanding is exactly right Marsh.

I have got the app to display a message that there should be at least one
category assigned to the photo in question, but I can't find a way to stop
the user moving off the record until at least one category is selected.
Click to expand...
Click to expand...
 
Ok, that seems reasonable. Did you consider selecting the
category before adding the photo?
 
No - because the user needs to see the Photo - sometimes taken by someone
else - before they can assign the appropriate category(s).

Marshall Barton said:
Ok, that seems reasonable. Did you consider selecting the
category before adding the photo?
--
Marsh
MVP [MS Access]

The main purpose (reason) for the app is so that users can find photos which
fit into the selected catefgories on the criteria selection form. So I need
to make sure that each photo has at least one category, otherewise those
photos will never be found.
Click to expand...
Click to expand...
 
Sounds like you have a catch22, You want to add a category
before a photo record is saved, but you can't add a category
until after the photo is added.

The only way I can see to break that deadlock is to not use
a subform for new photos. Instead you would need to use an
unbound combo box or whatever on the photo form to pick the
category. Then you could use code to insert the junction
table entry in the photo form's AfterUpdate event. The
BeforeUpdate event would only be used to make sure that
something had been selected in the category combo box.

Since a new photo is guaranteed to not have any existing
categories, the subform need not be visible and you can
place the unbound category combo box in the same place it
would be if the subform were visible. This scenario is easy
enough to do in the form's Current event:

Me.sfmCategory.Visible = Not Me.NewRecord
Me.fakesubformcombo.Visible = Me.NewRecord
 
I missed most of this but if the default catagory was "NONE" the photo could
be added and a new one assigned. I would use a continuos form and color code
"NONE" to show up red.
 
Tried a different tack - now working.

I am still using the same subform setup, but am disabling the navigation
buttons and manually checking if there are any related records in the
junction table in the close button's Click event. If there aren't any, I am
manually calling the before update of the form, displaying an appropriate
message, and setting Cancel = True.

The app now pauses for required data.
Many thanks for all your comments - especially you Marsh!

Marshall Barton said:
Sounds like you have a catch22, You want to add a category
before a photo record is saved, but you can't add a category
until after the photo is added.

The only way I can see to break that deadlock is to not use
a subform for new photos. Instead you would need to use an
unbound combo box or whatever on the photo form to pick the
category. Then you could use code to insert the junction
table entry in the photo form's AfterUpdate event. The
BeforeUpdate event would only be used to make sure that
something had been selected in the category combo box.

Since a new photo is guaranteed to not have any existing
categories, the subform need not be visible and you can
place the unbound category combo box in the same place it
would be if the subform were visible. This scenario is easy
enough to do in the form's Current event:

Me.sfmCategory.Visible = Not Me.NewRecord
Me.fakesubformcombo.Visible = Me.NewRecord
--
Marsh
MVP [MS Access]

No - because the user needs to see the Photo - sometimes taken by someone
else - before they can assign the appropriate category(s).
Click to expand...
Click to expand...
 
I think that could cover your common situations, but using a
close button's event can easily be bypassed by devious users
as can the nav buttons. You should be ok for users that do
not deviate from the expected behavior, but keep my
alternative in mind in case you run into an nonconformist
;-)
 
Back
Top