Check for edits on form close 1

[glgcag]

I was wondering if there was a better way to check for edits on an unbound form? I have about 15 fields that can be edited and I want to check for changes made when the user exits the form.

Currently, when I load the form, I populate the field and it's tag property with the same data. On Close of the form, I compare the two and if they aren't equal I create the edit. To do this though, I have to compare every field and set a boolean bEdit to true if there has been an edit. If so, then I open the recordset and update every field in the record because I don't know which of the fields were edited, just that at least ONE was edited.

Is there a better way to do this? I'm concerned that I'm wasting a lot of code when I could just do some function that handles it for me. Plus, every form that I have to check for edits on takes FOREVER to code if I have a lot of fields I need to compare. And then have to update everything in the record . . .

I imagine if there was a function I could create to check each field/check box for an edit and associate that field with a field in the table, I could create a generic function that would pass in any edited fields and then loop through and create a query based on the fields that need updating. Would this be possible? (I can't think how to compose the function . . .)

Thanks for the help in advance!

 

[Remou]

Are you looping through the controls collection? For example:

For each ctl in Me.controls
  If ctl.Tag<>"" Then
    If ctl.Tag<>ctl.Value Then
       blnEdited=True
    End If
  End If
Next

 

[glgcag]

Remou,

If I include the "if ctl.tag<>"" . . ." then what about the case that the field WAS empty but the user added something?

In my case, I have some hidden fields and I only want to check one tab of my form, so here's my code:

For Each ctl In Me.pgMain.Controls
        If (ctl.ControlType = acTextBox) And ctl.Visible = True And ctl.Tag <> ctl.Value Then
            bEdit = true
ElseIf (ctl.ControlType = acCheckBox) And ctl.Visible = True And ctl.Tag <> ctl.Value Then
            bEdit = true
        End If
Next ctl

Which works fine because I only have editable text boxes or check boxes. But once this is done and if bEdit = true, now I have to get the edited fields into the record. I have been in the habit of actually updating every field in the record even if the value hasn't changed, but is there a way to update the record with just the edited fields instead of going through every field and updating it in the record even if it hasn't been edited?

Thanks again for the help!

 

[TheAceMan1]

How are ya glgcag . . .

Out of curiosity my first question would be: [blue]Whats wrong with a bound form in this secnario?[/blue]

In any case you say:

[blue]On Close of the form, I compare the two and if they aren't equal I create the edit . . .[/blue]

Your indication is that on the first non-compare you setup the rcordset and append all data! If this is [blue]the extent of your validation[/blue] (the 1st non-compare) then [blue]Remou[/blue] has you in tow!

Yet you show a concern for the amount of code that has to be written. My point is bound forms would save you all of the code and you've shown no reason why they wouldn't work for you! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Remou]

glgcag
The code I posted was intended to be an example of what I meant by looping, it was not finished code.

I guess the controls are named in a consistent way, so it seems to me that the main problem is determining the data type, if you have that, it should be simple to build a query string to update only the relevant fields.

A very rough sketch of this idea:

For Each ctl In Me.pgMain.Controls
        If (ctl.ControlType = acTextBox) And ctl.Visible = True And ctl.Tag <> ctl.Value Then
      Select Case Left(ctl.Name,3)
          Case "str"
             Delim=Chr(34)
          Case "dte"
             Delim=Chr(35)
          Case Else
             Delim=""
      End Select
      strUpd=strUpd & Mid(ctl.Name,3) & " = " & Delim & ctl.Value & Delim & ","
            bEdit = true
ElseIf (ctl.ControlType = acCheckBox) And ctl.Visible = True And ctl.Tag <> ctl.Value Then
            bEdit = true
        End If
Next ctl

The above is by no means finished code, it is a sketch for discussion.

 

[glgcag]

TheAceMan1, I use unbound forms on all my programming now because, to be honest, I'm going to start learning SQL Server 2005 in order to use Access as my front-end and SQL Server 2005 as my back end. I have a gargantuan job of consolidating all the Access databases in my office as they are all related and need to share information, but were developed as needed over the last ten years. Furthermore, the database sizes are getting so large that Access really isn't the optimal solution considering all the corruption I have to deal with. In the end, I'll have all unbound forms using pass-through queries to retrieve and update data on the SQL Server. This is all theoretical at this point though . . .

Remou, sorry to come off as critical, wasn't my intent- I'm just new to this "edit gathering" and I'm having a bit of trouble with the specifics. Your code is really helpful, because I hadn't thought yet of how I'd handle strings, dates, etc. so your Delim variable is perfect.

I have had experience putting together a WHERE clause dynamically, which is probably similar to how I would put together an update query to update only the edited records, right? If so, I would somehow have to have the name of the field in the table associated with the name of the field on the form so that as the code is cycling through the ctl's and finds one that is edited, it can store the edit AND the field the edit should be inserted into in the table, right? In which case, should I make the form field names the same as the table field names (with the naming convention of str or dte, etc. appended to the front of it) and then add to your code a parse that pulls the field name out and put it in the update code?

I really appreciate all the help!

 

[Remou]

That's the way I would see it. What I do is to create the form allowing Access to assign control names, which means that the controls have the same names as the fields. In Access, it is then possible to use code to rename the fields according to the data type.

 

[glgcag]

OK, I think I'm getting it now- you determine the delimiter by the first 3 characters of the field name and the table field name to update from everything AFTER the first 3 characters. In strUpd you strip out the first 3 characters and that is the field name that the edit will be updated to! Very cool. Though I'm going to have to remember this before I create any new forms so that I name the form fields properly!

(On a side note, is this the way that I should be looking to get parameters for SQL pass-through queries when I move the back end to SQL Server 2005?)

This is going to help me a great deal- thank you!

 

[Remou]

I have only read about using SQL server with Access, and from what I have read this should be fine, after all, an SQL string is an SQL string and can be used in all sorts of ways.