Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test if record has been modified (2000 sr 1)

    Edited by HansV to make subject meaningful for later searches and to provide link to post - see <!help=19>Help 19<!/help>

    Refered this <post#=431284>post 431284</post#>
    Great...
    I have solved the problem with your suggestion with a button to call the update procedura it work wanderfull...
    I have named this UPDATE it put the new value only if this is pressed, but a dubt if the usaer after various modify not prerss the button?
    I have and idea... follow me
    In the txtbox18 the old valu is 1 and the user insert 2 and not press a buton UPDATE appear a msgbox" YUO HAVE CHANGED SOME, PRESS UPDATE"
    Insert this controol for every txtbox 34,35,38...
    Is a good idea?

    Private Sub CommandButton13_Click()
    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test if record has been modified (2000 sr 1)

    You can take this one step further. I will outline the basic idea, but you must write the actual code yourself.

    At the top of the module for the Indirizzario form, define a variable

    Private IsModified As Boolean

    In the After Update event of the text boxes, insert a line

    IsModified = True

    and in the Change event of ScrollBar1, insert this code

    If IsModified Then
    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39
    IsModified = False
    End If

    Also insert this code into the Click event of the Esci command button. The IsModified variable keeps track of whether the text boxes have been edited by the user; when the user scrolls to another record or closes the form, the code checks whether IsModified is true, and if so, writes a record in the database and resets IsModified for the next record.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if record has been modified (2000 sr 1)

    What I did in a recent project of mine was to have an array of booleans:

    Dim bChanged(1 to 75) as Boolean

    In my Userform I had change event code on each control (well, I did it with a single class module, but let's not confuse things here) that would set the appropriate index of that variable to true:

    Private Sub Textbox1_Change(.....)
    bChanged(1)=True
    Label1.Font.Bold=True
    End Sub

    To indicate the changed controls to the user the code also made their labels Bold text.

    In the event code that gets fired when the user tries to close the form I added a check to see whether any control had its changed "property" set to true and if so, ask for confirmation to update the changes.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if record has been modified (2000 sr 1)

    But the line:
    Private IsModified As Boolean
    in wath event activate or inizialize of userform?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test if record has been modified (2000 sr 1)

    It should be at the top of the userform module, not in a sub or function, but in a separate line before all subs and functions.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if record has been modified (2000 sr 1)

    ... in this case i really not understand:-(

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test if record has been modified (2000 sr 1)

    You already have some declarations at the top of the form module:

    Option Explicit
    Const MSG = "DEVI INSERIRE UNA NOTA!"
    Private mScrolling As Boolean
    Private mPROVADatabase As ADODB.Connection
    Private mProvaRecordSet As ADODB.Recordset

    Sub VerticalCenterText(TextHolder As MSForms.Label, PlaceHolder As MSForms.Label)
    ...

    Insert the line

    Private IsModified As Boolean

    above the first Sub.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if record has been modified (2000 sr 1)

    Hi Hans, good evening, and welcome in my home....
    Dubt, but with Private IsModified As Boolean ecc... to press the button UPDATE, if i want to write the record is always required or not...?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test if record has been modified (2000 sr 1)

    No, if you put code in the Change event of ScrollBar1 and in the Click event of the Esci button, the user doesn't need to click the Update button. The record will be saved automatically if it has been modified.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if record has been modified (2000 sr 1)

    This is my click event ESCI where i put the line of code:
    And i insert alla line of this code in this event?

    If IsModified Then
    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39
    IsModified = False
    End If

    Private Sub CommandButton7_Click()
    ClosePROVADatabase
    Sheets("L0785_TOTALE").Select
    If Not (Range("T7:T65536").Find("ASS. CONT. A CDI 50") Is Nothing) Then
    Call ASS_CONT_A_CDI50
    Call COMPARE_DELETE_TOTALE
    Call ADO_CDI_50
    End If
    Indirizzario.Hide
    Range("A7").Select
    End
    End Sub

    in the event change scrollbar1 is correct?

    Private Sub ScrollBar1_Change()

    If IsModified Then
    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39
    IsModified = False
    End If

    ComboBox1 = ""
    TextBox1 = ""

    If Cells(Indirizzario.ScrollBar1.Value + 6, 4) = "C/C" Then
    MsgBox "INIZIO ELENCO, IMPOSSIBILE PROSEGUIRE!", , "ATTENZIONE..."
    Indirizzario.ScrollBar1.Value = 1
    Exit Sub
    End If
    ............

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test if record has been modified (2000 sr 1)

    Yes. Insert the lines at the beginning of CoomandButton7_Click:

    Private Sub CommandButton7_Click()
    If IsModified Then
    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39
    IsModified = False
    End If
    ClosePROVADatabase
    Sheets("L0785_TOTALE").Select
    ...

    The ScrollBar1_Change procedure looks OK.

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if record has been modified (2000 sr 1)

    In the user form i have this button is required this line or not is important?:

    If IsModified Then
    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39
    IsModified = False
    End If


    Private Sub CommandButton13_Click()
    ActiveWorkbook.Save
    End Sub

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test if record has been modified (2000 sr 1)

    It is not necessary to insert the code there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •