G
Guest
Below is my original post on the Office Newsgroup, and a subsequent response
from a MS technician there. His ideas do not work. The bottom line is that
unless the subform loses focus, Access is not forcing the underlying SQL
table to be updated. This is a problem because with the custom menu bar I
have created for this ADP, if the user clicks on a Report choice from that
menu bar, anything the user has changed on the subform is not reflected in
the report. Clicking on a menu bar option does not cause the subform to lose
focus, so I cannot trap that event in code (as best as I can tell), thus the
SQL tables that the report pulls data from are not updated yet.
I do not want to tell my users "just click somewhere off the subform before
you try to click on a menu bar option to make sure the tables update
appropriately". Surely there is a way to force the SQL tables to get
updated...
Any ideas?
==================================================
I have an Access 2000 ADP on top of SQL Server 2000. My main form has a
collection of data items, then at the bottom of that main form is a subform
with its own set of data items. All fields are tied directly to SQL Server
tables. I also have a couple of Access reports that pull data from both the
main form and the subform.
My problem is that if I make a change to a field on the subform, but do not
click off the subform, and I choose to run a report from my custom pull-down
menu, the report does not show the latest changes made on the subform
(because the SQL Server table has not yet been updated). Even if I click off
of the field that I changed, onto another field on the same subform, it is
still not forcing the SQL table to be updated - I must click off the subform
completely.
I tried putting code in the "fsubPhase1_Dirty" event, with the following
line, but it did not help: DoCmd.Save acForm, "fsubPhase1". I also tried
referencing the main form name in this line of code, which did not help
either: DoCmd.Save acform, "frmEventCreate".
Please advise.
==================================================================================
Hello John, Thank you for your post! I understand that the change of a field
on a subform does not actually update backend table and you cannot see the
change in the report. If I'm off-base, please let me know.
Usually you shall change to the another record after you change field on one
record so that it could trigger a actual update. You may want to see the
orders form and Order details subform of NorthwindCS sample database to see
the details. If you want to update after you change from one field to
another, you may use a exit event of a field
Private Sub Quantity_Exit(Cancel As Integer)
Me.AllowEdits = True
DoCmd.RunCommand acCmdSaveRecord
End Sub
If you encounter error about saverecord, please try to refer to the topic
"The command or action 'SaveRecord' isn't available now." in following
article: The command or action 'SaveRecord' isn't available now.
http://www.granite.ab.ca/access/conversionproblems.htm
Actually Any time you move away from the current record (the one displayed
in your form) is is updated. You do not need to explicitly save the record.
The only issue you will need to address is whether all required fields have
data. This is typically done in the form's Before Update event. For example,
let's say that Shoe Size is a required field and the user tries to move to
another record or create a new record without entering the Shoe Size:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtShoeSize) Then
MsgBox "Shoe Size Is Required", vbInformation + vbOkayonly
Cancel = True
Me.txtShoeSize.SetFocus
End If
End Sub
Now if the user fails to enter the Shoe Size, they will get the above
message, the Update will be canceled, and the cursor will be positioned in
the Shoe Size control.
One other point. This code: DoCmd.RunCommand acCmdSaveRecord is not the best
way to do this. Most professionals would code it like this:
If Me.Dirty Then
Me.Dirty = False
End
If Please understand that we are not the best support resource for this kind
of question as our Partner Support newsgroups are geared towards break-fix
scenarios. Consulting requests are beyond what we can support in the
newsgroups. Therefore, the above information is just provided for your
reference and I hope it meet your requirements. Your understanding on this
will be greatly appreciated. You may want to post your question on the
Microsoft public newsgroups where other folks may have already experienced
this issue. You can find these newsgroups by connecting you news reader to
Publicnews.Microsoft.com. For more information, see: Q171164 OLEXP: How to
Configure Outlook Express for Internet -
http://support.microsoft.com/?id=171164. Some issues may require a bit more
in depth attention and may fall under the umbrella of Advisory Services.
Microsoft now offers short-term and proactive assistance for specific
planning, design, development or assistance with installing, deploying, and
general "how to" advice via telephone. For more information:
http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice If
you have furthter questions or concerns on the issue, please feel free to
let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support
from a MS technician there. His ideas do not work. The bottom line is that
unless the subform loses focus, Access is not forcing the underlying SQL
table to be updated. This is a problem because with the custom menu bar I
have created for this ADP, if the user clicks on a Report choice from that
menu bar, anything the user has changed on the subform is not reflected in
the report. Clicking on a menu bar option does not cause the subform to lose
focus, so I cannot trap that event in code (as best as I can tell), thus the
SQL tables that the report pulls data from are not updated yet.
I do not want to tell my users "just click somewhere off the subform before
you try to click on a menu bar option to make sure the tables update
appropriately". Surely there is a way to force the SQL tables to get
updated...
Any ideas?
==================================================
I have an Access 2000 ADP on top of SQL Server 2000. My main form has a
collection of data items, then at the bottom of that main form is a subform
with its own set of data items. All fields are tied directly to SQL Server
tables. I also have a couple of Access reports that pull data from both the
main form and the subform.
My problem is that if I make a change to a field on the subform, but do not
click off the subform, and I choose to run a report from my custom pull-down
menu, the report does not show the latest changes made on the subform
(because the SQL Server table has not yet been updated). Even if I click off
of the field that I changed, onto another field on the same subform, it is
still not forcing the SQL table to be updated - I must click off the subform
completely.
I tried putting code in the "fsubPhase1_Dirty" event, with the following
line, but it did not help: DoCmd.Save acForm, "fsubPhase1". I also tried
referencing the main form name in this line of code, which did not help
either: DoCmd.Save acform, "frmEventCreate".
Please advise.
==================================================================================
Hello John, Thank you for your post! I understand that the change of a field
on a subform does not actually update backend table and you cannot see the
change in the report. If I'm off-base, please let me know.
Usually you shall change to the another record after you change field on one
record so that it could trigger a actual update. You may want to see the
orders form and Order details subform of NorthwindCS sample database to see
the details. If you want to update after you change from one field to
another, you may use a exit event of a field
Private Sub Quantity_Exit(Cancel As Integer)
Me.AllowEdits = True
DoCmd.RunCommand acCmdSaveRecord
End Sub
If you encounter error about saverecord, please try to refer to the topic
"The command or action 'SaveRecord' isn't available now." in following
article: The command or action 'SaveRecord' isn't available now.
http://www.granite.ab.ca/access/conversionproblems.htm
Actually Any time you move away from the current record (the one displayed
in your form) is is updated. You do not need to explicitly save the record.
The only issue you will need to address is whether all required fields have
data. This is typically done in the form's Before Update event. For example,
let's say that Shoe Size is a required field and the user tries to move to
another record or create a new record without entering the Shoe Size:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtShoeSize) Then
MsgBox "Shoe Size Is Required", vbInformation + vbOkayonly
Cancel = True
Me.txtShoeSize.SetFocus
End If
End Sub
Now if the user fails to enter the Shoe Size, they will get the above
message, the Update will be canceled, and the cursor will be positioned in
the Shoe Size control.
One other point. This code: DoCmd.RunCommand acCmdSaveRecord is not the best
way to do this. Most professionals would code it like this:
If Me.Dirty Then
Me.Dirty = False
End
If Please understand that we are not the best support resource for this kind
of question as our Partner Support newsgroups are geared towards break-fix
scenarios. Consulting requests are beyond what we can support in the
newsgroups. Therefore, the above information is just provided for your
reference and I hope it meet your requirements. Your understanding on this
will be greatly appreciated. You may want to post your question on the
Microsoft public newsgroups where other folks may have already experienced
this issue. You can find these newsgroups by connecting you news reader to
Publicnews.Microsoft.com. For more information, see: Q171164 OLEXP: How to
Configure Outlook Express for Internet -
http://support.microsoft.com/?id=171164. Some issues may require a bit more
in depth attention and may fall under the umbrella of Advisory Services.
Microsoft now offers short-term and proactive assistance for specific
planning, design, development or assistance with installing, deploying, and
general "how to" advice via telephone. For more information:
http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice If
you have furthter questions or concerns on the issue, please feel free to
let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support