Yes, I think you are right, John. It most likely has to do with the
structure you are using.
There almost certainly is another way to design the tables, so you have many
records in a related table instead of so many (repeating) fields across this
table (and the one-to-one tables.) As it is, you are stuck with a
chicken'n'egg problem: you can't require the information in the related
table until the main record is saved, and you're trying to force data in the
related tables when the main record is saved.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
This is not going to work reliably.
You are using the BeforeUpdate event procedure of the main form to try to
force a value in the subform. This won't work because:
a) The main form's event doesn't fire when an entry is made in the
subform.
b) Assuming a normal one-to-many connection between main and subform, the
subform won't have a record when you create a new one in the main form
(which is one case when the main's Form_BeforeUpdate does fire.)
c) Each form has its own focus. Setting focus to something in the subform
makes it the active control there, but does not make the subform control
the
active one on the main form.
d) Even if you did make the control active, there's still no guarantee
that
the user will enter anything, or that the control will retain focus or the
form won't be closed while it has the focus.
news:9d1cf09d-43f6-4dbb-ba61-080d32c2748e@r66g2000hsg.googlegroups.com....
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([Forms]![frmPKMiscellaneous]![sfrmPKPKMSPhysicalAttributes]..
[Form]![ProfileType]) = True Then
Beep
If MsgBox("Type is required!", vbOKOnly + _
vbQuestion) = vbOK Then
Me.sfrmPKPKMSPhysicalAttributes.Form!ProfileType.SetFocus
End If
End If
End Sub
The first If works however the SetFocus doesn't - the main form goes
to a new record.
Does anyone see what's wrong?
Another consideration is that sfrmPKPKMSPhysicalAttributes is in a tab
control. Even with the tab control open to this subform the SetFocus
doesn't go to it.
Do I need something to open the correct tab control and then include
the tab control page in the SetFocus string?
Thanks!!!- Hide quoted text -
Well, I did some quicker thinking than I expected. Obviously, I was
going about this all wrong.
The subform is in a one-to-one relationship with the main form.
Nothing I can/want to do about that. The PK in the main form has LOTS
of related data therefore due to table/form field limitations I've had
to break up the data into several tables and forms that are on-to-one
with the PK.
I figure that a simple OnExit event will do the trick however this
isn't behaving as expected, either:
Private Sub ProfileType_Exit(Cancel As Integer)
If IsNull([ProfileType]) = True Then
Beep
If MsgBox("Type is required!", vbOKOnly + _
vbQuestion) = vbOK Then
Cancel = True
End If
End If
End Sub
When I created a new record in the main form this event fires. When I
tab to exit [ProfileType] the event works but when I mouse click into
the main form it doesn't.
I suspect this has to do with the one-to-one relationship and/or the
problem of focus...? I'm not too familiar with focus so I'm not sure
how to fix it if that's the problem.
Any guidance you might have is greatly appreciated oh, Access guru
from Down Under!- Hide quoted text -
- Show quoted text -
