# Detecting empty subforms - problem solved



## DrChocolate (Aug 21, 2012)

I've received significant help from searching this forum, but wanted to take this chance to contribute something back:

In a closed forum there was the following solution for the ageold question, how do I detect an empty subform using VBA (Access 2010 in this case). Pay attention to the last line.

========
You can some extra VBA code in the Button's vba code to check if the Fields have any data.
If isnull(me.[fieldname]) then msgbox "please fill in all the fields"
Where fieldname is the name of your field. If it is on a subform and the button is the mainform then the name of the Subform needs to be in statement.
_If isnull(me.[Subformname].[fieldname]) then msgbox "please fill in all the fields"_
========

My hat off to the writer, ah but alas, I believe there is one typo. The last line should replace a period with an exclamation mark, and, poof, it works just lovely. Thusly, use the following:

*If isnull(me.[Subformname]![fieldname]) then msgbox "please fill in all the fields"*

_"If only my computer would do I want rather than what I ask."
_


----------



## OBP (Mar 8, 2005)

DrChocolate, welcome to the forum.
That code only checks one field of course.
If you want to check all the fields you can loop through the form's Objects to do so.


----------



## DrChocolate (Aug 21, 2012)

Thank you OBP. Your note is very much to the point. I'm still learning VBA basics and the idea of looping through fields in a subform promotes the appearance of a big ? over my head including in part questions regarding correct reference syntax to the fields and how to determine that you're done looping through the field (that you've checked it all the fields in the subform). Would you please give me an example of VBA code? I very much appreciate your help.


----------



## OBP (Mar 8, 2005)

Ok, this code goes through a form's objects (which includes buttons and labels etc) and checks first if it is a field, combo or listbox, it then compares the current value with the previous value to see if it has changed, if it has it puts the changes in an Audit Trail table.
It is in a VBA Module so can be called from any form, but the calling VBA passes theForm's name to the module in a Variable.
This code can be used to test for lack of entries instead of changes to entries by using the Not Isnull() function

Sub TAuditTrail(frm As Form)
On Error GoTo Eventerror
Dim rs As Object, count As Integer, rstable As Object, db As Object, action As String
changes = ""
Set db = CurrentDb
Set rstable = db.OpenRecordset("Action Audit Trail")
If frm.NewRecord Then
action = "Added Record"
With rstable
.AddNew
!UserID = lngMyEmpID
!Form = frm.Name
!action = action
![Action Date/Time] = Now()
![Client] = myclient
.Update
.Bookmark = .LastModified
End With
If frm.Name = "CANS" Or frm.Name = "CANS_0_to_4" Then
changes = "Added 5 Assessment Records"
Else
changes = action
End If
Else
action = "Edited Record"
' MsgBox action
For Each ctl In frm.Controls
'MsgBox ctl.name
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Value <> ctl.oldvalue Or IsNull(ctl.oldvalue) And Not IsNull(ctl.Value) Or IsNull(ctl.Value) And Not IsNull(ctl.oldvalue) Then
With rstable
.AddNew
!UserID = lngMyEmpID
!Form = frm.Name
!FieldName = ctl.Name
!action = action
![Action Date/Time] = Now()
![Old Value] = ctl.oldvalue
![New Value] = ctl.Value
![Client] = myclient
.Update
.Bookmark = .LastModified
changes = changes & action & " " & ctl.Name & " - " & ctl.oldvalue & " becomes " & ctl.Value & Chr$(13)
End With
End If
End If
Next ctl
End If
rstable.Close
Set rstable = Nothing
MsgBox changes

Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub

end sub

The second section is checking for changes and the first section is looking added records.

PS you will get quicker responses to Access/Excel/Word questions on the Business Programs Forum, where you will find I have posted hundreds of of example databases with vba code in them.
I have also posted a word doc of useful VBA code as well.


----------



## DrChocolate (Aug 21, 2012)

OBP - Wow! Thank you very much for your quick response. I'll be studying your code -- it has way more than I thought about using and will be much more useful than I imagined. And thank you for sending to the right forum -- I'll look forward to seeing your posts over there in Business forum.

Take care.


----------

