Validating data on a subform

Hello....

I have a subform with a header, detail and form footer.
The form Header contains labels identifying the fields in the detail
row.
The Detail section contains 10 data fields (text boxes, combo boxes, and
checkboxes.)
The Footer section contains nothing.

This subform is shown in "continuous forms" mode, showing several rows
from table.

When I tab from "Field10 of row 1" to "Field1" of the next row, I need
to check to see if any data in any of the 10 fields was modified. If
data was changed, I need to pop a message asking, "Do you want to accept
the changes or cancel?"
If yes, accept the changes, if not, cancel the changes.

I know there is an "On Dirty" event for each field that is used when the
data changes, but I cannot find a "On Dirty" event when the user tabs
from one row to the next row.

Via VBA, is there a way to loop through the fields in the detail section
(regardless of object type), or do I have to loop through each field in
the detail section and put code in the "On Dirty" event of each
individual data object?

Thank you.



*** Sent via Developersdex http://www.developersdex.com ***

Re: Validating data on a subform

Use the BeforeUpdate event of the *form* (not that of the controls.)
Access fires that event just before saving the record.

Compare the Value of the control to its OldValue.
If they are the same, do nothing. Otherwise, give you message.
(Note that this logic handles changes from null, or to null.)

This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

'Don't warn when adding a new record.
If Not Me.NewRecord Then
'Substitute your field names here
strMsg = strMsg & BuildWarning(Me.[Field1])
strMsg = strMsg & BuildWarning(Me.[Field2])
'etc

If strMsg <> vbNullString Then
strMsg = strMsg & vbCrLf & "Save?")
If MsgBox(strMsg, vbOkCancel, "Confirm changes) <> vbOk Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

Private Function BuildWarning(ctl As Control, strMsg As String) As String
If ctl.Value = ctl.OldValue Then
'do nothing
Else
BuildWarning = ctl.Name & " changed from " & _
Nz(.OldValue, "Null") & " to " & Nz(.Value, "Null") & vbCrLf
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"RLN" wrote in message
news:1201280403_1181@sp12lax.superfeed.net...
>
> Hello....
>
> I have a subform with a header, detail and form footer.
> The form Header contains labels identifying the fields in the detail
> row.
> The Detail section contains 10 data fields (text boxes, combo boxes, and
> checkboxes.)
> The Footer section contains nothing.
>
> This subform is shown in "continuous forms" mode, showing several rows
> from table.
>
> When I tab from "Field10 of row 1" to "Field1" of the next row, I need
> to check to see if any data in any of the 10 fields was modified. If
> data was changed, I need to pop a message asking, "Do you want to accept
> the changes or cancel?"
> If yes, accept the changes, if not, cancel the changes.
>
> I know there is an "On Dirty" event for each field that is used when the
> data changes, but I cannot find a "On Dirty" event when the user tabs
> from one row to the next row.
>
> Via VBA, is there a way to loop through the fields in the detail section
> (regardless of object type), or do I have to loop through each field in
> the detail section and put code in the "On Dirty" event of each
> individual data object?
>
> Thank you.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Re: Validating data on a subform

Allen,


1. Private Function BuildWarning(ctl As Control,
2. strMsg As String) As String
3. If ctl.Value = ctl.OldValue Then
4. 'do nothing
5. Else
6. BuildWarning = ctl.Name & " changed from " & _
7. Nz(.OldValue, "Null") & " to " & Nz
8. (.Value, "Null") & vbCrLf
9. End If
10. End Function

-------
Per the paragraph above, I am getting a compile error on line #7. On
this compile error, ".OldValue" are the only characters highlighted and
the compile error message is: "invalid or unqualified reference".

I have not seen this error before. I don't know if this would have
anything to do with this error, but I went to "Tools/References". Here
is what was listed in this order:
1-Visual Basic for Applications.
2-Microsoft Access 11.0 Object Library
3-Microsoft Excel 11.0 Object Library
4-OLE Automation
5-Microsoft Visual Basic for Applications Extensibility 5.3
6-Microsoft DAO 3.6 Object Library

I'm not sure of why the "invalid or unqualified reference" is occuring.

Thanks for any other input you might have.




*** Sent via Developersdex http://www.developersdex.com ***

Re: Validating data on a subform

Sorry: needs ctl before the dot, i.e.:
Nz(ctl.OldValue, "Null) & " to " & Nz(.Value, "Null") & vbCrLf

(Note that 6, 7, and 8 are one logical line.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RLN" wrote in message
news:1201633226_79@sp12lax.superfeed.net...
>
>
> 1. Private Function BuildWarning(ctl As Control,
> 2. strMsg As String) As String
> 3. If ctl.Value = ctl.OldValue Then
> 4. 'do nothing
> 5. Else
> 6. BuildWarning = ctl.Name & " changed from " & _
> 7. Nz(.OldValue, "Null") & " to " & Nz
> 8. (.Value, "Null") & vbCrLf
> 9. End If
> 10. End Function
>
> -------
> Per the paragraph above, I am getting a compile error on line #7. On
> this compile error, ".OldValue" are the only characters highlighted and
> the compile error message is: "invalid or unqualified reference".

Re: Validating data on a subform

>>needs ctl before the dot, i.e.: Nz(ctl.OldValue, "Null) & " to " & Nz(.Value, "Null") & vbCrLf <<


Thank you for your help here, Allan. I'm new to the 'Nz' function and
will take a look at it in more detail, as I have not used this before.

RLN

Re: Validating data on a subform

Okay, Nz() converts a null value to something else - typically a zero or a
zero-length string. In this case, we converted it to the word null (for the
message box.)

The reason for using Nz() here is that nothing matches null. More info about
working with nulls:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:45150fab-1e44-4df5-bd99-99588b00a34a@e10g2000prf.google groups.com...
>>>needs ctl before the dot, i.e.: Nz(ctl.OldValue, "Null) & " to " &
>>>Nz(.Value, "Null") & vbCrLf <<
>
>
> Thank you for your help here, Allan. I'm new to the 'Nz' function and
> will take a look at it in more detail, as I have not used this before.
>
> RLN