Results 1 to 8 of 8
  1. #1
    jscriptor09 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    60

    form re-fresh

    Hi folks



    I have a form that holds 'activity' data. There are about 5000 'activities' in the table.

    In a separate table, I have a comment field. Bascially, it allows the user to associate a comment related to a specific activity with the activity. A unique key ties the two tables when I need to run a query.

    Not every activity has a comment entry. Some do and some don't

    In the activity form, I added the small comment table as a subform. I use the o_Current event to make sure that when the user navigate from one activity to the other, the subform and the activity form stay in sync.

    to add a new comment related to a specifc case, the user clicks a button on the activity form, a small modal form pops up and the user enter the new comment. On close, the new comment data is written to the comment table along with the unique key. Sometime, I do append only.

    Question:
    After the user enters the comment in the modal form and hits the OK button, the modal form closes and the comment is written on to the comment table. To see the comment in the current Activity subform, the user must navigate one record forward or backward and then back to the record.

    Is there a way to refresh the Comment subform iside the Activity form with the new comment info as soon as the user clicks the OK button on modal comment entry form?

    After hours of fighting "Write Conflict Error" I settled on this schema described above. Seems to work fine, but the user would like the new comment entered appear in the main form right away.

    Thanks for you help/suggestions.

  2. #2
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by jscriptor09 View Post
    Hi folks

    I have a form that holds 'activity' data. There are about 5000 'activities' in the table.

    In a separate table, I have a comment field. Bascially, it allows the user to associate a comment related to a specific activity with the activity. A unique key ties the two tables when I need to run a query.

    Not every activity has a comment entry. Some do and some don't

    In the activity form, I added the small comment table as a subform. I use the o_Current event to make sure that when the user navigate from one activity to the other, the subform and the activity form stay in sync.

    to add a new comment related to a specifc case, the user clicks a button on the activity form, a small modal form pops up and the user enter the new comment. On close, the new comment data is written to the comment table along with the unique key. Sometime, I do append only.

    Question:
    After the user enters the comment in the modal form and hits the OK button, the modal form closes and the comment is written on to the comment table. To see the comment in the current Activity subform, the user must navigate one record forward or backward and then back to the record.

    Is there a way to refresh the Comment subform iside the Activity form with the new comment info as soon as the user clicks the OK button on modal comment entry form?

    After hours of fighting "Write Conflict Error" I settled on this schema described above. Seems to work fine, but the user would like the new comment entered appear in the main form right away.

    Thanks for you help/suggestions.
    In the "OK" button right before the code the close the modal form, put in the following:

    Forms!YourMainFormName.Requery

  3. #3
    jscriptor09 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    60
    Hi Toyman

    Many thanks for the feedback. I seem to have a problem applying the proposed solution and I am hoping you may be able to recognize the problem.

    The form name as I see it in ACCESS Forms object list (ob the left side where you see a list of table, queries, forms ...) is Activities_ReviewForm.

    Therefore, I added the following line to the OK button code as you proposed above:

    Forms!Activities_ReviewForm.Requery

    I get the following message
    Run time error 2450
    DatabaseName cannot find the referenced form 'Activities_ReviewForm'

    Is there something special about naming the form?

    Thanks for you help.

  4. #4
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by jscriptor09 View Post
    Hi Toyman

    Many thanks for the feedback. I seem to have a problem applying the proposed solution and I am hoping you may be able to recognize the problem.

    The form name as I see it in ACCESS Forms object list (ob the left side where you see a list of table, queries, forms ...) is Activities_ReviewForm.

    Therefore, I added the following line to the OK button code as you proposed above:

    Forms!Activities_ReviewForm.Requery

    I get the following message
    Run time error 2450
    DatabaseName cannot find the referenced form 'Activities_ReviewForm'

    Is there something special about naming the form?

    Thanks for you help.
    Is the comment subform inside the form, "Activities_ReviewForm"? Is the comment entry form launched from a command button in the subform. Finally, if I got it right, your want the comment entered into the comment form to update immediately in the comment subform upon the clicking of the "OK" button in the comment form? The error you described was because the form in which you are trying to requery was not opened.

    Here is the code:

    Forms!Activities_ReviewForm!YourSubFormName.Form.R equery

  5. #5
    jscriptor09 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    60
    Hi Toyman

    You were right, the form was closed and that why I was running into the error.

    The only problem I see with the requery is that it put me back to the first record. In another word, the user enters a comment for activity in record 60, after the requery, he is back to record one. Is there a way to get back to same record?

    Thanks

  6. #6
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by jscriptor09 View Post
    Hi Toyman

    You were right, the form was closed and that why I was running into the error.

    The only problem I see with the requery is that it put me back to the first record. In another word, the user enters a comment for activity in record 60, after the requery, he is back to record one. Is there a way to get back to same record?

    Thanks
    Did you use this code instead of the previous one?

    Forms!Activities_ReviewForm!YourSubFormName.Form.R equery

    The first one I gave you requeries the main form which would then bring you back to the first record. If your sub form is a continuous list form inside the main form, then the second code I gave you will only requery the subform. This way, after the "OK" button is click and the entry from closes, the subform containing a list of comments relating to the main record will requery bring in the new comment entered. Also, somehow the word "Requery" came out as "R equery" at the end of the code. But it is "Requery"

  7. #7
    jscriptor09 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    60
    Hi Toyman

    Thanks for the reply.

    here is what I have

    Forms!navigationForm!navigationsubform.Form.Requer y

    -The Activity_ReviewForm is setting inside an Access2010 Navigation Form control
    -The CommentForm (a single record form display, not a table display) is a subform inside the Activity_ReviewForm as a 3 by 3 small window displaying one record at a time. it is syncronized to the Activity_ReviewForm via a vba code.
    -There is a button in the Activity_ReviewForm that the user clicks to add a comment to the comment table.
    -I added the code:
    Forms!navigationForm!navigationsubform.Form.Requer y
    to the close event of the comment modal.

    The problem seems to be that because the modal form is open until close, the
    Forms!navigationForm!navigationsubform.Form.Requer y
    -This command does the update but sets the Activity_ReviewForm back to record 1.

    the poblem seems to be that since the model form is open and has focus until the close event finishes, Therefore this set of code
    Private Sub Form_Close()
    pr = Forms!navigationForm!navigationsubform.Form.Curren tRecord
    Forms!navigationForm!navigationsubform.Form.Requer y
    DoCmd.GoToRecord acDataForm, Forms!navigationForm!navigationsubform.Form.Name, acGoTo = pr
    End Sub

    is returning an error that the Activity_ReviewForm is not open. I di check in the debugger that Forms!navigationForm!navigationsubform.Form.Name returns Activity_ReviewForm.

    Any thought !

    Many thanks

  8. #8
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by jscriptor09 View Post
    Hi Toyman

    Thanks for the reply.

    here is what I have

    Forms!navigationForm!navigationsubform.Form.Requer y

    -The Activity_ReviewForm is setting inside an Access2010 Navigation Form control
    -The CommentForm (a single record form display, not a table display) is a subform inside the Activity_ReviewForm as a 3 by 3 small window displaying one record at a time. it is syncronized to the Activity_ReviewForm via a vba code.
    -There is a button in the Activity_ReviewForm that the user clicks to add a comment to the comment table.
    -I added the code:
    Forms!navigationForm!navigationsubform.Form.Requer y
    to the close event of the comment modal.

    The problem seems to be that because the modal form is open until close, the
    Forms!navigationForm!navigationsubform.Form.Requer y
    -This command does the update but sets the Activity_ReviewForm back to record 1.

    the poblem seems to be that since the model form is open and has focus until the close event finishes, Therefore this set of code
    Private Sub Form_Close()
    pr = Forms!navigationForm!navigationsubform.Form.Curren tRecord
    Forms!navigationForm!navigationsubform.Form.Requer y
    DoCmd.GoToRecord acDataForm, Forms!navigationForm!navigationsubform.Form.Name, acGoTo = pr
    End Sub

    is returning an error that the Activity_ReviewForm is not open. I di check in the debugger that Forms!navigationForm!navigationsubform.Form.Name returns Activity_ReviewForm.

    Any thought !

    Many thanks
    can you post a sample db here and I will take a look

Please reply to this thread with any new information or opinions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums