Assistance with subform VB

[Rick]

I am trying to write code to prevent an individual from
exiting a form without filling in all of the required data
and I have most of it working except the part relating to
the sub form.

What happens is both the last name and first name
have to be filled in on the main form (this part works).
In the subform, the status (inbound/assigned) needs to be
filled in as well but it seems to be stuck in a loop
because when I update the "Code" field, it still sees it
as null. After selecting OK, enter the data in the
subform, it returns the same msgbox and reverts back to
the code field.

Here is what I have done:

Private Sub Command18_Click()
On Error Resume Next

If (IsNull([Member_Last_Name])) Then
MsgBox "You must enter the individuals last name
of this record.", vbCritical
Me.Member_Last_Name.SetFocus

ElseIf (IsNull([Member_First_Name])) Then
MsgBox "You must enter the individuals first
name of this record.", vbCritical
Me.Member_First_Name.SetFocus

ElseIf (IsNull(Forms![subfrmAddIntro]!

)) Then
MsgBox "You must fill members status, inbound
or assigned."
Forms![subfrmAddIntro]![Code].SetFocus
Else: DoCmd.Close
Form.Refresh
Forms![frmMain]![Code] = "58 SOW CSS
Database"
End If
end sub

Any assistance is greatly appreciated.  Thanks in advance!

 

[Van T. Dinh]

1. At which stage in the data-entry process do you expect to execute the
Command18_Click Event Procedure? (BTW, you should name it with some more
meaningful name. At least, potential respondents can try to guess what you
are trying to do when the code is incorrect).

2. From the code you post, I don't think you are aware of the "AutoSave"
feature on the Form / Subform combination. Basically, when the Focus moves
from the MainForm to the Subform, the CurrentRecord is *automatically
updated* into your Table. The reverse (the Focus moves from the Subform to
the MainForm) is also true, i.e. the CurrentRecord on the SubForm is
*automatically update*.

This is necessary to ensure the Referential Integrity on the One (Record on
MainForm)-to-Many (Records on SubForm) relationship is satisfied. However,
this creates problems as to us, the whole data-entry of "One" Record and
"Many" Records is treated as a single process while the AutoSave divides
this into 2 separate processes. For example, you may enter data on the
MainForm and as soon as you enter the Subform, you can no longer "undo" the
Record on the MainForm.

The same with your code which looks like you are checking data-entry on the
"One" Record and the "Many" Records as a single process while the AutoSave
would probably have jumped in and updated the Record(s) without you being
aware of its action. Hence, your code probably doesn't work as you
expected. Since I am aware of the AutoSave separating the process into 2
distinct processes, I can't see how you can check all of these Fields in one
Event Procedure, hence my question in point 1.

--
HTH
Van T. Dinh
MVP (Access)

I am trying to write code to prevent an individual from
exiting a form without filling in all of the required data
and I have most of it working except the part relating to
the sub form.

What happens is both the last name and first name
have to be filled in on the main form (this part works).
In the subform, the status (inbound/assigned) needs to be
filled in as well but it seems to be stuck in a loop
because when I update the "Code" field, it still sees it
as null. After selecting OK, enter the data in the
subform, it returns the same msgbox and reverts back to
the code field.

Here is what I have done:

Private Sub Command18_Click()
On Error Resume Next

If (IsNull([Member_Last_Name])) Then
MsgBox "You must enter the individuals last name
of this record.", vbCritical
Me.Member_Last_Name.SetFocus

ElseIf (IsNull([Member_First_Name])) Then
MsgBox "You must enter the individuals first
name of this record.", vbCritical
Me.Member_First_Name.SetFocus

ElseIf (IsNull(Forms![subfrmAddIntro]!

)) Then
MsgBox "You must fill members status, inbound
or assigned."
Forms![subfrmAddIntro]![Code].SetFocus
Else: DoCmd.Close
Form.Refresh
Forms![frmMain]![Code] = "58 SOW CSS
Database"
End If
end sub

Any assistance is greatly appreciated.  Thanks in advance!
[/QUOTE]

 

[Rick]

Mr. Dinh,

First off, thanks for the reply.

Here is what I am trying to do. The command18_click event
procedure is actually my command button for closing the
Add New Member form. Here is what is going on:

I have two tables for this form/subform. One is the
Member table (main form) and the other one is an Inbound
table(subf form). They are connected by the primary key
in the Member table, MemberID and the forign key in the
Inbound table, MemberID. In the Inbound table/subform, I
have a field called Code. If a member is inbound (not
currently employed but recently hired) I want to force the
user to select "inbound" for the new record. This way I
can track all inbound personnel and ensure we are
assisting them in getting moved and so on. When the
member arrives in the company, the user changes the code
field to "Assigned."

My problem is this. If a user doesn't update the code
as "Inbound" then the new member doesn't show up in the
inbound management form and I can't track/manage their
move. On the flip side, if that field doesn't show
Assigned, the member won't show up in the manage assigned
members form either. In reality, the newly added member
ends up in limbo because their record doesn't show up in
either the manage inbound form or manage assigned members
form (both are tied to a query). I need to ensure the
field is updated with the correct info. The only way I
can think of doing this is by forcing the user to input
data into the field before they can close the form.

Hope this makes sense.

-----Original Message-----
1. At which stage in the data-entry process do you expect to execute the
Command18_Click Event Procedure? (BTW, you should name it with some more
meaningful name. At least, potential respondents can try to guess what you
are trying to do when the code is incorrect).

2. From the code you post, I don't think you are aware of the "AutoSave"
feature on the Form / Subform combination. Basically, when the Focus moves
from the MainForm to the Subform, the CurrentRecord is *automatically
updated* into your Table. The reverse (the Focus moves from the Subform to
the MainForm) is also true, i.e. the CurrentRecord on the SubForm is
*automatically update*.

This is necessary to ensure the Referential Integrity on the One (Record on
MainForm)-to-Many (Records on SubForm) relationship is satisfied. However,
this creates problems as to us, the whole data-entry of "One" Record and
"Many" Records is treated as a single process while the AutoSave divides
this into 2 separate processes. For example, you may enter data on the
MainForm and as soon as you enter the Subform, you can no longer "undo" the
Record on the MainForm.

The same with your code which looks like you are checking data-entry on the
"One" Record and the "Many" Records as a single process while the AutoSave
would probably have jumped in and updated the Record(s) without you being
aware of its action. Hence, your code probably doesn't work as you
expected. Since I am aware of the AutoSave separating the process into 2
distinct processes, I can't see how you can check all of these Fields in one
Event Procedure, hence my question in point 1.

--
HTH
Van T. Dinh
MVP (Access)

I am trying to write code to prevent an individual from
exiting a form without filling in all of the required data
and I have most of it working except the part relating to
the sub form.

What happens is both the last name and first name
have to be filled in on the main form (this part works).
In the subform, the status (inbound/assigned) needs to be
filled in as well but it seems to be stuck in a loop
because when I update the "Code" field, it still sees it
as null. After selecting OK, enter the data in the
subform, it returns the same msgbox and reverts back to
the code field.

Here is what I have done:

Private Sub Command18_Click()
On Error Resume Next

If (IsNull([Member_Last_Name])) Then
MsgBox "You must enter the individuals last name
of this record.", vbCritical
Me.Member_Last_Name.SetFocus

ElseIf (IsNull([Member_First_Name])) Then
MsgBox "You must enter the individuals first
name of this record.", vbCritical
Me.Member_First_Name.SetFocus

ElseIf (IsNull(Forms![subfrmAddIntro]!

)) Then
MsgBox "You must fill members status, inbound
or assigned."
Forms![subfrmAddIntro]![Code].SetFocus
Else: DoCmd.Close
Form.Refresh
Forms![frmMain]![Code] = "58 SOW CSS
Database"
End If
end sub

Any assistance is greatly appreciated.  Thanks in advance!
[/QUOTE]


.
[/QUOTE]

 

[Van T. Dinh]

1. Don't make it too hard for the user to close the Form. He/she may get
frustrated and uses Ctrl + Alt + Del which can corrupt your database.

2. ***Important***: "Inbound" and "Assigned" seem to be the status, i.e. a
Property of the Member, meaning the Status should be a Field in the
tblMember. Thus I am not sure why you have the tblInbound and the
Form/Subform combination (which indicate the One-to-Many relationship
between tblMember and tblInbound) to store the Member's status. Note that I
could be wrong here since I don't know your tblInbound!

Have you checked your Table Structure and ensure that it follows the
Relational Database Design Principles?

3. As I pointed out in my previously reply, there are processes running
behind the Form/Subform combination so you simply have to work out your
steps to work with the underlying processes (which cannot be avoided). So,
use bound Subform only when it is necessarily to do so.

4. Your code seem to indicate there a Field (or Control) "Code" on the
MainForm which was not mentioned in your description.