Popup Form updates record, sometimes (1 Viewer)

[chuckcoleman]

Hi, I have a form called "Detail Form" with a text box on it. The underlying field is a Long Text. The text box is formatted as Rich Text. If I double click on the text in the text box, it opens a popup form that is based on the same table as the previous form that holds the text box. The popup form has a text box on it that is simply larger in size than the one on the Detail Form to facilitate easier editing/entry. When I add text to the popup form, in most cases as soon as I close the popup, the Detail From text box is updated. It appears that when there is a lot of text in that field, it doesn't update the Detail Form's text box even though the popup is based on the same table as the Detail Form. I added to the popup vba code to tell me the number of characters in the text box in the popup form. The MsgBox indicates 2,908 characters which I believe is much smaller than the limitation of that field, (Long Text type). If I try a different record on the Detail Form where the text box has a smaller number of characters, it works. For instance, on a record where the text box has 1,869 characters, it works fine and updates the underlying field.

I've tried adding Modal to the popup form but it still doesn't update the field if the number of characters is 2,908. I haven't tried guessing at what size below that makes it work. Note: when the popup form closes it does a Recalc of the text box on the Detail Form.

Any ideas?

 

[theDBguy]

We may have to see your code, but try saving the record both ways: before and after you open and close the popup form.

Oh, and you may have to refresh the detail form.

 

[arnelgp]

you need to Requery your "Detail Form".
Remember, what you have is a Snapshot of your record.
it does however got a "message" from your system that
the record in "Detail Form" is out of synch with that
in the table when you did a modification on the pop-up.

this situation having same record on two different form is a No-No.

what you can do is make the pop-up form Unbound and just make a copy
of the content of the textbox on the Detail form.
Before the pop-up closes you check the Value and OldValue of the textbox
if they are different, copy the Value to the textbox in Detail Form.

 

[chuckcoleman]

OK, both suggestions have been helpful. Based on dbg's suggestion I added a Command button on the popup form to save the record. That's all it does, I wanted to see if it could save it before I closed. it. When the text box had a small number of characters, it saved it without any problems. If the text box had the large number of characters, 2,862, I got a vba error, 3188, Could not update; currently locked by another session on this machine. They may be what arnelgp was referring to. Access must be doing something internally if the number of characters is above a certain number. I'll follow arnelgp's suggestion and see if I can get that to work. Feedback shortly.

 

[theDBguy]

OK, both suggestions have been helpful. Based on dbg's suggestion I added a Command button on the popup form to save the record. That's all it does, I wanted to see if it could save it before I closed. it. When the text box had a small number of characters, it saved it without any problems. If the text box had the large number of characters, 2,862, I got a vba error, 3188, Could not update; currently locked by another session on this machine. They may be what arnelgp was referring to. Access must be doing something internally if the number of characters is above a certain number. I'll follow arnelgp's suggestion and see if I can get that to work. Feedback shortly.

Looks like you only followed one half of my suggestion. I also said to save the record before you open the popup form.

 

[chuckcoleman]

Looks like you only followed one half of my suggestion. I also said to save the record before you open the popup form.

Sorry dbg, I do that before I open the popup.

 

[theDBguy]

Sorry dbg, I do that before I open the popup.

Guess you'll have to show us your code, so we can help you.

 

[chuckcoleman]

OK, I got it working. When I double-click on the text box in the Detail Form I used TempVars to create a snapshot of the text in the text box. When i'm in the popup form, and when I start to close the form, I compare the TempVars value from the Detail Form to the text box in the popup form. If they are the same, I just close the popup. If they are different, I said, Forms![Detail Form]![popupformtext], and then close the popup. When the Detail Form GotFocus, I checked if it was Dirty and if so, I then saved the record.

 

[chuckcoleman]

Follow on question. When I make a change in the popup it does make the same/updates the text box on the Detail Form. When that change is made, that form is "Dirty', it isn't saved. I've tried various things but I can't figure out once I close the popup how to save the Detail Form. I know the Detail Form would save the changes if I closed it but I'm just trying to be a little cautious.

 

[arnelgp]

the code for the "checking" and assigning should be done on the Pop-up's Unload event:

Private Sub Form_Unload(Cancel As Integer)
    Dim subfrm As Form
    ' get the subform
    Set subfrm = Forms!MainFormName!SubFormName.Form
    If Me!theTextboxOnPopUp <> frm!OriginalTextbox Then
        frm!OriginalTextbox = Me!TtheTextboxOnPopUp
        frm.Dirty = False
    End If
    Set frm = Nothing
End Sub

 

[chuckcoleman]

the code for the "checking" and assigning should be done on the Pop-up's Unload event:

Private Sub Form_Unload(Cancel As Integer)
    Dim subfrm As Form
    ' get the subform
    Set subfrm = Forms!MainFormName!SubFormName.Form
    If Me!theTextboxOnPopUp <> frm!OriginalTextbox Then
        frm!OriginalTextbox = Me!TtheTextboxOnPopUp
        frm.Dirty = False
    End If
    Set frm = Nothing
End Sub

Thanks arnelgp. I found that by putting in the "Close" event for the popup form, "Forms!MainFormName!Requery", it saves the record. It works but do you feel there's an issue doing it this way?

 

[Pat Hartman]

When you open a popup form that is going to update the current form's current record, you don't always "see" the update immediately. Doesn't mean that it doesn't happen. What you do need to understand when doing something like this is that modifying controls using VBA does NOT run ANY of the control level events. It does however dirty the record so that the form's BeforeUpdate event will still fire. So as long as you put your validation code into the form's BeforeUpdate event, all will be well. If you have made the mistake of relying on control level events to perform validation, your validation code will NEVER run

That said, assuming you have a save/close button for the popup, that event would save the data to the control on the calling form --
Forms!MainFormName!SubformName.Form!ControlName = Me.ControlName.

In the event that opened the popup form, code execution stops at the line where the dialog form opens. Therefore, the next line of code can refresh the current form. WARNING - if the OpenForm does not open the form in dialog mode, code execution does NOT stop it continues to the end of the procedure.
Me.Refresh.

Also, I remind you that whenever formA opens formB, the line of code immediately prior to the OpenForm code should save the current record. Use
DoCme.RunCommand acCmdSaveRecord
Or the trick:
Me.Dirty = False
but always document what this code does.

 

[chuckcoleman]

When you open a popup form that is going to update the current form's current record, you don't always "see" the update immediately. Doesn't mean that it doesn't happen. What you do need to understand when doing something like this is that modifying controls using VBA does NOT run ANY of the control level events. It does however dirty the record so that the form's BeforeUpdate event will still fire. So as long as you put your validation code into the form's BeforeUpdate event, all will be well. If you have made the mistake of relying on control level events to perform validation, your validation code will NEVER run

That said, assuming you have a save/close button for the popup, that event would save the data to the control on the calling form --
Forms!MainFormName!SubformName.Form!ControlName = Me.ControlName.

In the event that opened the popup form, code execution stops at the line where the dialog form opens. Therefore, the next line of code can refresh the current form. WARNING - if the OpenForm does not open the form in dialog mode, code execution does NOT stop it continues to the end of the procedure.
Me.Refresh.

Also, I remind you that whenever formA opens formB, the line of code immediately prior to the OpenForm code should save the current record. Use
DoCme.RunCommand acCmdSaveRecord
Or the trick:
Me.Dirty = False
but always document what this code does.

Pat, that's excellent and helpful feedback! Thank you for taking the time to write this up for me. You do an awesome job, amongst many others. I appreciate it!