Trigger an Insert on a Subform

[Eka1618]

Hello,

I have a tab control with 8 pages. Each page has its own subform who's
record source is the same (tblTest). Before I click the send button, I am
trying to determine if the form was completed correctly. There are other
subforms within the parent form, buf for some reason, I cannot trigger the
point at which the insert into this particular table is occuring.

If the form is filled out correctly, and the last subform a user enters data
into reside inside the tab control, the following code does not work:
....
ElseIf IsNull(DLookup("[TEST_ID]", "tblTest", "[REQUEST_NO] = " &
REQUEST_NO.Value)) Then
MsgBox "You must enter at least one test for this request."
Else
....

(please disregard that it says ElseIf, I have other things going on)

It does not work because it still says that a test has never been entered
when in fact it did. Based on my observation the insert into tblTest is
definitely occuring after I click the send button. but I want to prevent the
form from sending a "request" if either A: the sub form was not completed and
B: If no test was inserted at all.

The button (btnSend) is located on the parent form. Here is the code for
this button:

Public Sub btnSend_Click()
Dim teesEmail As String
Dim torsEmail As String
Dim varItem As Variant

strValid = CheckFormValues(Me)

If strValid <> vbNullString Then
MsgBox "There are items on this form that were left blank. Please
review and fill in missing value."
ElseIf IsNull(DLookup("[L_ID]", "tblLock", "[REQUEST_NO] = " &
REQUEST_NO.Value)) Then
MsgBox "You must enter at least one Lock/Fastner."
ElseIf lockTest = True And IsNull(DLookup("[K_ID]", "tblKey",
"[REQUEST_NO] = " & REQUEST_NO.Value)) Then
MsgBox "You must enter a Key for this request."
ElseIf lugTool = True And IsNull(DLookup("[K_ID]", "tblKey",
"[REQUEST_NO] = " & REQUEST_NO.Value)) Then
MsgBox "You have chosen to include a tool with the Lug and did not
enter Tool information for this request."
ElseIf lockTest = True And IsNull(DLookup("[REQUEST_NO]", "tblPattern",
"[REQUEST_NO] = " & REQUEST_NO.Value)) Then
MsgBox "You must enter Pattern information for this request."
ElseIf IsNull(DLookup("[TEST_ID]", "tblTest", "[REQUEST_NO] = " &
REQUEST_NO.Value)) Then
MsgBox "You must enter at least one test for this request."
Else
For Each varItem In Me.lboRequestee.ItemsSelected
teesEmail = teesEmail & Me.lboRequestee.Column(2, varItem)
Next varItem

For Each varItem In Me.lboRequestor.ItemsSelected
torsEmail = torsEmail & lboRequestor.Column(2, varItem)
Next varItem

Call SetRequesteeEmailProp(teesEmail) ' found in modProperties
Call SetRequestorEmailProp(torsEmail) ' found in modProperties

Call SendRequest(Me)
DoCmd.Close acForm, "frmSingleRequest", acSaveNo
End If
End Sub

If anyone knows what I can do, please let me know. It is vital that this
issue is solved quickly. Thank You!

 

[Marshall Barton]

I have a tab control with 8 pages. Each page has its own subform who's
record source is the same (tblTest). Before I click the send button, I am
trying to determine if the form was completed correctly. There are other
subforms within the parent form, buf for some reason, I cannot trigger the
point at which the insert into this particular table is occuring.

If the form is filled out correctly, and the last subform a user enters data
into reside inside the tab control, the following code does not work:
...
ElseIf IsNull(DLookup("[TEST_ID]", "tblTest", "[REQUEST_NO] = " &
REQUEST_NO.Value)) Then
MsgBox "You must enter at least one test for this request."
Else
...

(please disregard that it says ElseIf, I have other things going on)

It does not work because it still says that a test has never been entered
when in fact it did. Based on my observation the insert into tblTest is
definitely occuring after I click the send button. but I want to prevent the
form from sending a "request" if either A: the sub form was not completed and
B: If no test was inserted at all.

Any edits to a subform record are saved when the focus moves
out of the subform (in your case, when the button is
clicked). The record may not have made it to the table that
quickly, but by the time you get to the button, it's too
late to do any more about it except maybe(?) using DoEvents
to give the save a little more time.

 

[Eka1618]

Thanks for the response Marshall,

I think thats what I am going to have to do. Each subform in the tab control
may or may not be filled. So I am going to write code that basicaly says:

if tab1 is visible then save (subFrmTest1)

I have asked this question 2 other times in the past and I always seems to
get close to what I need, but in the end it never worked.

I still do not get why the events are not occuring as soon as I leave the
subform... It honstly makes no sense at all. I could fill in one subform (in
the tab) then go back and fill another subform on the parent form and it
would still tell me that there is no test entered in tblTest.

Thanks again

--
~Erica~

I have a tab control with 8 pages. Each page has its own subform who's
record source is the same (tblTest). Before I click the send button, I am
trying to determine if the form was completed correctly. There are other
subforms within the parent form, buf for some reason, I cannot trigger the
point at which the insert into this particular table is occuring.

If the form is filled out correctly, and the last subform a user enters data
into reside inside the tab control, the following code does not work:
...
ElseIf IsNull(DLookup("[TEST_ID]", "tblTest", "[REQUEST_NO] = " &
REQUEST_NO.Value)) Then
MsgBox "You must enter at least one test for this request."
Else
...

(please disregard that it says ElseIf, I have other things going on)

It does not work because it still says that a test has never been entered
when in fact it did. Based on my observation the insert into tblTest is
definitely occuring after I click the send button. but I want to prevent the
form from sending a "request" if either A: the sub form was not completed and
B: If no test was inserted at all.

Any edits to a subform record are saved when the focus moves
out of the subform (in your case, when the button is
clicked). The record may not have made it to the table that
quickly, but by the time you get to the button, it's too
late to do any more about it except maybe(?) using DoEvents
to give the save a little more time.

 

[Marshall Barton]

I think thats what I am going to have to do. Each subform in the tab control
may or may not be filled. So I am going to write code that basicaly says:

if tab1 is visible then save (subFrmTest1)

I have asked this question 2 other times in the past and I always seems to
get close to what I need, but in the end it never worked.

I still do not get why the events are not occuring as soon as I leave the
subform... It honstly makes no sense at all. I could fill in one subform (in
the tab) then go back and fill another subform on the parent form and it
would still tell me that there is no test entered in tblTest.

Try an experiment by adding a
MsgBox "subform whatever AfterUpdate"
in the subforms' AfterUpdate event so you can see when the
record is saved. the message box should appear when you
click anywhere outside the subform. If it doesn't appear,
then there is something wrong in the form. If it does
appear, then check the table to see if the Test value is in
the record. If it is in the table, then the MsgBox gave it
enough time to finish saving, If it's not there, check the
text box to make sure it's bound to the field.

 

[Eka1618]

Hi Marshall,

I tried using the msgbox and the afterupdate event is firing only when the
parent form closes or if I start to enter values into another form that is
inside the tab control. it does not fire when I move to the parent form. I
have checked if my form is bound correctly and I have checked my record
source and they seem to be correct.

If you have any ideas on why this is happeneing or what I can do, please let
me know. Thank You!

 

[Eka1618]

Hi Marshall,

I tried docmd.save acForm, "frmGeneral" during the button_click event and it
said that frmGeneral was not open, and it is open. frmGeneral is one of the
subforms in the tab control.

I have also tried me.dirty = false in certain subform event, but I cannot
seem to loose focus off these forms in the tabs unless I close the parent
form or if I enter data into another subform within the tab control.

I am having a hard time triggering event where the form will loose/get
focus, or insert/update.

If you have any thoughts about another menthod I could use please let me
know, Thank you!

 

[Eka1618]

I also have tried to use the following:

RunCommand acCmdSaveRecord

when I do this, it is triggering the record to be saved. I have some code in
the before update event of my subform that will highlight a control if it is
NULL. After running the save statement above, the BeforeUpdate event fires on
my subform, however, I also get an error that says there is no Current Record
in my subform.

I just do not get this...

 

[Marshall Barton]

I also have tried to use the following:

RunCommand acCmdSaveRecord

when I do this, it is triggering the record to be saved. I have some code in
the before update event of my subform that will highlight a control if it is
NULL. After running the save statement above, the BeforeUpdate event fires on
my subform, however, I also get an error that says there is no Current Record
in my subform.

I just do not get this...

I don't get it either, but I may not fully understand what
you are doing.

A stray thought, try using the subform control's Exit event:

With Me.[subform control].Form
If .Dirty Then .Dirty = False
End With

When testing, put a breakpoint in there and see if .Dirty is
ever True.

 

[Eka1618]

I tried your method just now and i am still having the same problem. It is
Error # 3021. Here is what I have for the exit evernt of this form:

Private Sub frmGeneral_Exit(Cancel As Integer)
msgbox "hi"
If Me.frmGeneral.Form.Dirty Then
Me.frmGeneral.Form.Dirty = False
End If
End Sub

I used the message box, to see when it was executing, but it is not
executing when I press button send on the parent form, or any other control
for that matter. It only executes when I select another tab page.

I think I might have to open these subforms in a new window. The forms need
to have the ability to stop a person from sending a request if there are no
tests associated with the request. If I can't trigger these events, then I
will have to use them as a pop up form.

If you come up with anything else, let me know. Thanks again!

--
~Erica~

I also have tried to use the following:

RunCommand acCmdSaveRecord

when I do this, it is triggering the record to be saved. I have some code in
the before update event of my subform that will highlight a control if it is
NULL. After running the save statement above, the BeforeUpdate event fires on
my subform, however, I also get an error that says there is no Current Record
in my subform.

I just do not get this...

I don't get it either, but I may not fully understand what
you are doing.

A stray thought, try using the subform control's Exit event:

With Me.[subform control].Form
If .Dirty Then .Dirty = False
End With

When testing, put a breakpoint in there and see if .Dirty is
ever True.

 

[Marshall Barton]

I tried your method just now and i am still having the same problem. It is
Error # 3021. Here is what I have for the exit evernt of this form:

Private Sub frmGeneral_Exit(Cancel As Integer)
msgbox "hi"
If Me.frmGeneral.Form.Dirty Then
Me.frmGeneral.Form.Dirty = False
End If
End Sub

I used the message box, to see when it was executing, but it is not
executing when I press button send on the parent form, or any other control
for that matter. It only executes when I select another tab page.

From that, I would have to suspect that the buttons are not
really command buttons. If they are really label controls
that kind of look like buttons, that would explain it.

Or maybe they are command buttons, but they're in the
subfome itself.

 

[Eka1618]

Hi Marshall,

I'm positive that I am using a button and the send button is not in a
subform. It is located on the parent form. I guess I am confused by what you
are saying...

I have decided to delete the tab control and all of those subforms. I have
now decided to create an option group. so when a person selects the type of
test they want, the test form will pop up in a new window.

Thank you anyway for your help!

 

[Marshall Barton]

Hi Marshall,

I'm positive that I am using a button and the send button is not in a
subform. It is located on the parent form. I guess I am confused by what you
are saying...

I have decided to delete the tab control and all of those subforms. I have
now decided to create an option group. so when a person selects the type of
test they want, the test form will pop up in a new window.

I'm trying to find a logical explanation for the behavior
you are descrbing. The illogical explaination is that you
have some kind of corruption, in which case all bets are
off.

 

[Eka1618]

Well I do appreciate the help Marshall,

I am not sure if I had a corruption with my former design. The records would
add to my tables just fine, however, they were being inserted at an odd time.
I have changed my design and the new design I came up with is actually the
design that was originally suggested by the users.

I tried using tabs, and they all liked it alot, but I need to be sure that
certain contingencies remain within the parent form. I could not do this with
the tab control and so I decided to change my design.

I am an intern and my internship will be ending within the next month. I
unfortunately just do not have to much time left to spend on some of these
issues that I have tried working out over the summer. I have come up with an
alternative, so that is all that matters.

Thanks again!

 

[Marshall Barton]

I am not sure if I had a corruption with my former design. The records would
add to my tables just fine, however, they were being inserted at an odd time.
I have changed my design and the new design I came up with is actually the
design that was originally suggested by the users.

I tried using tabs, and they all liked it alot, but I need to be sure that
certain contingencies remain within the parent form. I could not do this with
the tab control and so I decided to change my design.

I am an intern and my internship will be ending within the next month. I
unfortunately just do not have to much time left to spend on some of these
issues that I have tried working out over the summer. I have come up with an
alternative, so that is all that matters.

Well, I feel the need to emphasize that a tab control should
have nothing to do with anything about saving records.
There must be something else getting in the way.

At least you have something that works and can get on with
wrapping up your internship. Good luck in your future
endeavors.

 

[Eka1618]

Thank you!
--
~Erica~

Well, I feel the need to emphasize that a tab control should
have nothing to do with anything about saving records.
There must be something else getting in the way.

At least you have something that works and can get on with
wrapping up your internship. Good luck in your future
endeavors.