compare open record to recordset, was it changed

[vicsaccess]

I have a form that pulls records on an unbound form, what i'm looking for is a way to compare that group of unbound text boxes to the matching record to see if any changes were made and needs to be updated. My thought was to use a findfirst to find the record in the recordset and then do several if's like-

    if (txt1<>rs!1)then
        goto edit record
    Elseif (txt2<>rs!2)then
        goto edit record
     Elseif (txt3<>rs!3)then
        goto edit record
    End If

or something along those lines. is there a better way?

[John_G]

Why use an unbound form? It makes for a lot of extra code. It's a lot easier just to bind the form to your table, and move to the record you need to edit. If you decide not to keep changes, you can always use the .undo method to cancel them, provided you do so before moving to another record or closing the form.

[vicsaccess]

There's a few reasons for the unbound that in all other ways make the form work better. is there a good way to compare six text boxes to a record and see if it has been changed?

[ssanfu]

Your example code will not work... if you step through the code, you will see where it will not accomplish what you want.
Using "goto" is a very poor programming practice except in error handling code. "Goto" has been depreciated a long time ago. A lot of spaghetti has been written in BASIC, MS BASIC, GW BASIC, BASIC in general because if the "Goto" command. (from personal experience - a real PITA to debug for sure!!)


Lets assume that you are working with one table - tblEmps - that has 3 fields:
Emp_PK (autonumber)
FirstName (Text)
LastName (Text)

Lets start out with one record.
Since you are using an unbound form, you will be loading the data into text boxes. Since you have 3 fields, there will be 5 unbound text boxes on the form.
One text box will be named tbPK. It will be hidden.
a text box will be FN1... visible
a text box will be FN11... hidden
a text box will be LN1... visible
a text box will be LN11... hidden

When you load the data into FN1, you also load it into FN11. Since FN1 is visible and FN11 is not visible, you will be able to edit only FN1.
Same goes for LN1 & LN11.
Then you can check it the pairs of text boxes have the dame values.

There is a button on the form to save changes.

Sub BtnSaveUnBoundForm()
    Dim r As DAO.Recordset
    Dim sSQL As String

    If (Me.FN1 <> Me.FN11) Or (Me.LN1 <> Me.LN11) Then
        'open a recordset on the table.
        sSQL = "SELECT FirstName, LastName"
        sSQL = sSQL & " FROM tblEmps"
        sSQL = sSQL & " FWHERE [Emp_PK] = " & Me.tbPK

        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not (r.BOF And r.EOF) Then
            r.Edit
            r!FirstName = Me.FN1
            r!LastName = Me.LN1
            r.Update
        End If
        r.Close
        Set r = Nothing
    End If
    
    MsgBox "Saved!"
End Sub

This saves all fields. You can write a lot of code to save just the changed data..... but it will be a LOT of code.


NOTE: This is just an idea and hasn't been tested. You have been warned!!!

[vicsaccess]

Awesome Steve, i knew that someone here would have an ideal of how to do a comparison. I just couldn't figure out a good way to compare the original load of text to when the save button is pushed, the duel text boxes is the "BOMB" as my kid says. I'm not worried about the length of code, its already been wrote and being used for another purpose. i have a qualifier sub that will decide whether to send the data to a add data sub, edit data sub or clear the text boxes(no harm will be done since no record has changed). i just needed a was to compare. If i haven't said it thanks.

[Micron]

visaccess, I'm curious. Does the same recordset get dumped into each set of textboxes and the aim is to see if any changes are made to one of them?
Or was the goal to load two recordsets and see if there was a difference anywhere? Like comparing two tables or query results?

[vicsaccess]

Hi Micron, the same recordset populates all the text boxes that need to be compared. The user has 3 options that i foresee with the text boxes, they can bring up a blank record to start a new product, bring up an existing record and edit its details in the text boxes, or use the record as he originally entered it displayed in the text boxes. Its these Last two possibilities that i need help with, how do i tell if the record loaded in the text boxes is as was originally loaded or if he had to adjust it. Do you have a different way?

I did have another thought last night after i had stopped for the night kind of like Steve's concept but maybe simpler. What if i use the on-change event. I haven't tested or tried it yet but my thought was if i put a hidden checkbox on the screen, then added to the on-change event of each of the text boxes to make the checkbox "true". Something like-

Private Sub txtLength_Change()
    Me.ckrecord = True
End Sub

Then when the user tries to change to a new record i could have something like this to direct the record-

Private Sub recordqualify()
    
    If txtproductID = "" Then
        recordaddnew '-------------its a new record
    ElseIf Me.ckrecord = True Then
        recordedit '---------------its an existing record that has been adjusted
    Else
        '--------------------------GoTo next record,no changes were made to this one
    End If
End Sub

Not sure what you may have had in mind as another option but I would love to hear it. And i did use some of your code in this project, the text boxes are being validated with your "HasNoData" function, Thanks for posting it, its working like a charm.

[Micron]

I had a solution in mind but thought it too simple. After reading your reply, I figure I must not be understanding exactly what it is you are trying to do because it still seems too simple. If the form opens empty and a record is created, or if the form opens loaded and a record is changed, If Me.Dirty = True ought to tell you an edit was made. If false, then no changes or additions. With respect to OnChange, note that this event fires for each keypress while the user is in the control - probably not what you want.

If it is a form/subform situation, leaving one to go to the other can make the Dirty property False (I believe the recordset of the form is saved even though the record has not been saved). So I set a module level variable to hold the fact that either the form or subform had changes.

If the project allows, I try to reuse forms for record operations. In one case, depending on the situation, some controls had to be locked or visibility altered. The menu bar selection to alter or create dictated if the form was to open in new or edit mode, and this was passed to the form via the OpenArgs property. The open event used that argument to set up the form, including properties such as AllowAdditions.

[ssanfu]

In my testing, on unbound forms, the Dirty property doesn't change. Nor does the dirty property change for unbound controls (on an unbound form).
Apparently, a form must be bound to a record set to be able to have the Dirty property change. (I could be wrong)


The change event fires on every keystroke, not if the value have been changed. I don't think it will be useful in this instance.

[Micron]

ssanfu, you are absolutely correct about the property on unbound forms. Guess I have never been in that situation.
The change event fires on every keystroke, not if the value have been changed . I don't know what you mean by that. It certainly does fire after every character added or even removed.

So maybe I'd use the AfterUpdate event for each control and set my module level bolIsDirty variable to True. If one wants to get more robust, use a function to return the control name to another module level variable as well and you would know which ones were changed. To know what was changed, the OldValue property cannot be used for unbound controls (AFAIK), but you could store the value in the control tag and compare, say in the BeforeUpdate event or variables related to each control that needed to be monitored. You would not need dual controls with this approach, let alone have to compare them. Then again, that level of sophistication was not asked for - I'm rambling.

[ssanfu]

but you could store the value in the control tag and compare

I don't use the Tag property much and tend to for get about it.
I can see where using the Tag property might be easier than keeping track of a 2nd control. But the 2nd control could be made visible to be able to see the original value. 6 of one, half a dozen of the other.........


All depends on the needs of vicsaccess.....

[vicsaccess]

Sorry for not replying sooner and marking this solved. What i ended up with was a little from both of you, the extra control on the form from steve and a trigger event from Micron. I used a check box that on record load gets set to False, then used the After_update event of each text box to change the check box to True. So far all my testing is working great. I ended up with the After_update because like Steve stated, unbound forms don't work with the dirty method and the Current event fires with each key stokes, the After_update reacts similar to the dirty method but also works with unbound data. Now on my record change i have a If Then statement that checks for the existance of a PK loaded with the record and then checks the state of the checkbox to decide how to proceed. Thanks guys for the direction and if you see any problems with my thought process please let me know.