Text box get erased when saving record

[RainLover]

Can you post the code behind the form here.

please post all of it not just part and don't forger to enclose it in

 tags.
  
 [CODE]This is a sample of something enclosed in Code tags

 

[Andy74]

I have removed the save button and the related code, but the issue is still there, i.e. the textbox get erased when you move to another record. As before this happens only when you stay idle for a some time before moving to another record (1 min or more). Thanks for helping and sorry for the poor code format.

Option Compare Database


Private Sub cmbuser_AfterUpdate()   ' this is a combobox filter in the form header

    Dim strSql As String
    Dim blWasFilterOn As Boolean

    ' Save the FilterOn state. (It's lost during RecordSource change.)
    blWasFilterOn = Me.FilterOn

    ' Change the RecordSource.
    If IsNull(Me.cmbuser) Then
        If Me.RecordSource <> "elencoeventiqry" Then
            Me.RecordSource = "elencoeventiqry"
        End If
    Else
        strSql = "SELECT elencoeventiqry.*, elenco_eventi_attendance.attendant " & _
        "FROM elencoeventiqry INNER JOIN elenco_eventi_attendance ON elencoeventiqry.ID = elenco_eventi_attendance.idevent " & _
        "WHERE elenco_eventi_attendance.attendant='" & Me.cmbuser & "' " & _
        "ORDER BY id;"
        Me.RecordSource = strSql
    
    End If
    ' Apply the filter again, if it was on.
    If blWasFilterOn And Not Me.FilterOn Then
        Me.FilterOn = True
    End If
    
    DoCmd.GoToRecord , , 3
    Me.cmbuser = Me.cmbuser
exit_lab:
    Exit Sub
Err_lab:
    Resume exit_lab
End Sub


Private Sub cmdmyvisit_Click()   ' this is a command button in the form footer
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSql As String
    Dim blflag As Boolean
    Set db = CurrentDb
   
         
    If Me.RecordSource = "elencoeventiqry" Then
    
        Me.RecordSource = "SELECT elencoeventiqry.*, elenco_eventi_attendance.attendant FROM elencoeventiqry " & _
                    "INNER JOIN elenco_eventi_attendance ON elencoeventiqry.ID = elenco_eventi_attendance.idevent;"
        blflag = True
    End If
    Call CreateQry("temp1", Me.RecordSource)
        strSql = "TRANSFORM Count(temp1.ID) AS ConteggioDiID " & _
            "SELECT temp1.nomecliente, temp1.[event type], temp1.elenco_eventi_attendance.attendant " & _
            "FROM temp1 " & _
            "WHERE temp1.[Event Date] > (date()-365) And temp1.[Event Type] = 'visit' " & _
            "GROUP BY elenco_eventi_attendance.attendant, temp1.nomecliente, temp1.[event type] " & _
            "ORDER BY temp1.nomecliente " & _
            "PIVOT Format([Event Date],'yyyy-mm');"
        
    Call CreateQry("Visit Summary", strSql)

    Call openquery("Visit Summary")

       
    If blflag = True Then
    
        Me.RecordSource = "elencoeventiqry"
        Me.Requery
        DoCmd.GoToRecord , , 3
    
    End If
    
    
    Set qdf = Nothing
    Set db = Nothing
End Sub


Private Sub customer_AfterUpdate()
    Me.txtcustomerorig = Me.customer.Column(1)
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.salesman = CurrentUser
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)  ' this is to avoid unwanted changes in a record
   Dim strMsg As String
   Dim iResponse As Integer

   ' Specify the message to display.
   strMsg = "Do you wish to save the changes?" & Chr(10)
   strMsg = strMsg & "Click Yes to Save or No to Discard changes to this record."

   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
      
   ' Check the user's response.
   If iResponse = vbNo Then
       ' Undo the change.
       DoCmd.RunCommand acCmdUndo
       ' Cancel the update.
       Cancel = True
   End If
   
End Sub


Private Sub Form_Load()
    
    DoCmd.GoToRecord , , acLast
    Me.cmbcustomer.SetFocus
    
End Sub


Private Sub txt_idcl_Click()

On Error GoTo Err_lab
    
     Call openform("clienti", 0, "[idcliente] = " & Me!txt_idcl)
exit_lab:
    Exit Sub

Err_lab:
    MsgBox Err.Description
    Resume exit_lab
End Sub


Private Sub cmddsview_Click()
  DoCmd.RunCommand acCmdDatasheetView
End Sub



Private Sub ID_DblClick(Cancel As Integer)
  DoCmd.RunCommand (acCmdFormView)
End Sub


Private Sub cmdnewfile_Click()    ' this is to add an attachment to a subform

    On Error GoTo cmdnewfile_dblclick_err

    
    Dim fdialog As Office.FileDialog
    Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
    Dim nomefile As String
    Dim localfile As String   ' nome completo file con percorso
    Dim extension As String
    Dim db As Database
    Dim rst As Recordset2
    Dim newid As Integer
    Dim fld As Field
    Dim tdef As TableDef
    Dim strSql As String
    Dim doctype As String
    
           
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Choose a file"
        .Filters.Clear
        
        If .show Then
            nomefile = Right(.SelectedItems(1), Len(.SelectedItems(1)) - Len(.InitialFileName))
            localfile = .SelectedItems(1)
           
            extension = Right(localfile, Len(localfile) - InStrRev(localfile, "."))
        Else
            Exit Sub
        End If
    End With
    
    Set fdialog = Nothing
        
    Set db = CurrentDb
       
   
    Set rst = db.OpenRecordset("elencoeventiattach", dbOpenDynaset, dbSeeChanges)
    
    rst.AddNew
    
    rst!idsevento = Me.ID
    rst!nomefile = nomefile
    rst!extension = extension
    rst!usercr = CurrentUser
    'rst!typeofdoc = "Bundle"
    If modOLEBinary_LoadFromFile(rst!attachfilestream, nomefile) Then
        MsgBox "File Written"
        
    Else
        MsgBox "Written failed"
    End If
    
    rst.Update
    rst.Bookmark = rst.LastModified
    rst.Close
    Me.eventiattach.Requery
    Set rst = Nothing
    
    db.Close
    Set db = Nothing
    
   
    
cmdnewfile_dblclick_exit:
        Exit Sub
cmdnewfile_dblclick_err:
    MsgBox Err.Description & "    " & Err.Number
    Resume cmdnewfile_dblclick_exit
End Sub


Function FilterResults() As Variant             ' this is a filter in the form header
    Dim strCriteria As String
    strCriteria = ""
    
   
    If Nz(Me.cmbcustomer) <> "" Then
        If strCriteria <> "" Then
            strCriteria = strCriteria & " AND "
        End If
        
        If InStr(Nz(Me.cmbcustomer), "*") = 0 Then
        
            strCriteria = strCriteria & " [nomecliente]='" & Me.cmbcustomer & "'"
            
        Else
            
            strCriteria = strCriteria & " [nomecliente] LIKE '*" & Me.cmbcustomer & "*'"
        End If
        
    End If
        
    If strCriteria <> "" Then
        Me.Filter = strCriteria
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    DoCmd.GoToRecord , , acLast
    Me.cmbcustomer = Me.cmbcustomer
End Function

 

[vbaInet]

If I may add, Rain permitting , stop changing the RecordSource of your form at will and your problems should go away. I haven't looked at your code in depth but I see some things that are questionable too, i.e. OpenQuery and CreateQuery functions you created. Why do you need those?

Secondly, OpenQuery is a reserved keyword in Access and what you've done is override that the DoCmd.OpenQuery method.

 

[RainLover]

There is a lot of code here. Can you put some remarks so that I can follow what is going on.

Can you supply the details of the Record Source and also the name of the "Text Box"

Where and when does the text box get its value and where does it loose it.

Put some remarks in the appropriate place amongst the code.

Then repost the code.

Thanks

I need help if anyone can jump in.

 

[RainLover]

Thanks VBA

It certainly looks like too much code being used for a simple task which is not to have this Text Box go blank.

I feel that the Record Source is wrong.

Why would only one text box go blank and not the others.

VBA please hang in there.

 

[vbaInet]

I haven't read the whole thread, so I'm not entirely sure what you two were discussing prior to Andy's recent post (i.e. post #16) or what the task is.

All I know from reading post #16 and after, is that, the fields in the Record Source are continually inconsistent due to the hot swapping of Record Sources and I would imagine that it's the textbox bound to one of the fields not present in the "elencoeventiqry" table that's causing it. Andy74 just needs to cease swapping Record Sources around and re-think the whole strategy. Swapping the Record Source on forms in itself results in problems, not unless you know exactly what you're doing.

I'll just be in the background Rain!

 

[Andy74]

Hello VBA and Rainlover,

there is a lot of code but none is related to the famous text box, which is never mentioned there. The textbox is nothing special, no events, no code interacting with that.

Also the changing of recordsource is not related to that, I use that just when the user apply filters on fields which are in the subform and not in the main form. The createqry is actually needed in this. For the openqry you are right, the name is not right, I should change it.

I have removed all the useless code and the filters. Here is the simplified code.

Option Compare Database


Private Sub customer_AfterUpdate()  ' this write the customer number 
    Me.txtcustomerorig = Me.customer.Column(1)
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)  ' this write the current user in a text box

    Me.salesman = CurrentUser
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)  ' this is to avoid unwanted changes in a record
   Dim strMsg As String
   Dim iResponse As Integer

   ' Specify the message to display.
   strMsg = "Do you wish to save the changes?" & Chr(10)
   strMsg = strMsg & "Click Yes to Save or No to Discard changes to this record."

   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
      
   ' Check the user's response.
   If iResponse = vbNo Then
       ' Undo the change.
       DoCmd.RunCommand acCmdUndo
       ' Cancel the update.
       Cancel = True
   End If
   
End Sub


Private Sub Form_Load()
    
    DoCmd.GoToRecord , , acLast
    
End Sub

I have also upload a screenshot of the form with some explanation in it.

 

[vbaInet]

So where does the textbox get its value or what is it bound to?

There's another use of a reserved keyword again, "CurrentUser", not that it's related to your problem anyway but worth mentioning. Here's a good list of reserved keywords:

http://allenbrowne.com/AppIssueBadWord.html

 

[Andy74]

The recordsource of the form is a query based on a linked SQL table. The textbox is bound to a nvarchar(max) field in the query/table.

 

[vbaInet]

Ok, just had the liberty of going through your entire thread and it's worth mentioning that your original choice of NText was fine as that's the equivalent of a Memo field in Access.

The memo field is a field in the table "elencoeventiqry" which has other fields such as customer, date of meeting, etc right?

 

[RainLover]

So the Text Box is linked to the same table as everything else on that form.

Therefore it should be saved at the same time as every thing else is saved.

Also it is saved to the same table.

Am I correct.

 

[Andy74]

Yes you are both correct in all statements. The Ntext was chosen automatically when I did an upsize of the database to SQL server last year. Then I recently changed to nvarchar(max) because I read on a book that Ntext is an old type which will be soon replaced and I thought this was the cause. Anyway I noticed no change.
All the fields are saved as Rainlover said. The only thing I am thinking is the sequence of typing in the form: the user start filling the customer textbox, then meeting date, then the visit type and the he clicks on the sub form to selects the attendees. At that point the system ask for confirmation of save (before update event) and the record is correctly saved. Then the user fill the sub form and move to the remaining two text boxes on main form: the customer attendees and the meeting notes. Then if he types the notes but stay idle for some time the that text box will be erased and saved empty as soon as he gets out. Is it possible that you need to click out of a textbox after typing otherwise it looses its value? I mean before the before update event fires?

 

[vbaInet]

I think this could be again related to the swapping of Record Sources, but I would need to know at which point you perform this operation?

The other thing I suspect could be the issue is that, large text fields are special fields which are saved in chunks, so if there's a possible loss in connection (i.e. following a record source swap or an idle period) whilst the record is dirty, then SQL Server will do what it can to preserve data integrity.

Considerations/possible solutions:
1. Commit the data if after two minutes of being idle
2. Use an unbound textbox - On Current event, save the value into a variable, set the texbox Value to the variable.
3. Look into the refresh interval in Access Options.

But then again, I need to know at which stage you swap Record Sources.

 

[Andy74]

There is no recordsource swap in the last version I have tested, the code is exactly the one I posted (the second one). But the issue is still there.

I think your suggestion about the SQL server is the right one. In fact I moved the related tables to local access table, using the same form, the issue is disappeared. You can stay idle 10 min and the record will be saved correctly, unlike with SQL server.

Can you better explain how to apply your three possible solutions? I like the second one most, but can you suggest how to implement it?

 

[vbaInet]

So it will go something like this:
1. Make the textbox unbound (and for the purpose of the following explanation I'll call it txtMemo)
2. In the Current event of the form, get the value:

Me.txtMemo = Me![MemoField]

3. In the Before Update of the form, pass the new value back to the field:

Me![MemoField] = Me.txtMemo

... make sure that the textbox name is not the same as the field itself.

 

[Andy74]

Thanks, I have tested it now and it works fine! Just a remark: I had to put the code

Me![MemoField] = Me.txtMemo

in the txtMemo Before Update event and not on the Form Before Update event, otherwise if you edit only the txtMemo this will not fire the Form Before Update event because it's unbound and you will not be able to update the Memofield in a record unless you change also other fields.

Thanks for the support!

 

[vbaInet]

Yep, good call!

You're welcome! That's one less thing for RainLover to worry about now

 

[RainLover]

Success at last, Thanks VBA.

My SQL Server knowledge is very limited. I don't think I would have come up with that solution.

Best wishes for the balance of the project.

 

[vbaInet]

Success at last, Thanks VBA.

We do what we can to help.