On close check for blank entries

[Chad]

Hello, I have a frmMain that requires txtEmployeeTime, txtMinutes,
txtDTRegular and txtFootage to have an entry in it. The way we enter info
into each record is like this. We have 7 machines that run on a shift so for
each shift there are 7 records (One for each machine) The Supervisor who
enters the info always enters the basic info for each of the seven records
and through out the day they continue to enter the remaining info as it
happens for each machine. What happens sometimes is they will forget to fill
out a control on one of the 7 records and it messes things up. Is there a
way to give an MsgBox when the form is closed telling me which boxes went
filled out on what record? Thanks!

 

[John W. Vinson]

Hello, I have a frmMain that requires txtEmployeeTime, txtMinutes,
txtDTRegular and txtFootage to have an entry in it. The way we enter info
into each record is like this. We have 7 machines that run on a shift so for
each shift there are 7 records (One for each machine) The Supervisor who
enters the info always enters the basic info for each of the seven records
and through out the day they continue to enter the remaining info as it
happens for each machine. What happens sometimes is they will forget to fill
out a control on one of the 7 records and it messes things up. Is there a
way to give an MsgBox when the form is closed telling me which boxes went
filled out on what record? Thanks!

I presume there's a Subform with the seven records? How are the tables related
- by the date, or what?

You'll basically need some VBA code in the main form's Close event to use
DCount() to count how many records are in the child table for that day's
entries, with a criterion for a non-null field. If that count is anything
other than 7 cancel the close and issue an error message. Without more info
about the tables' structures it's hard to be more specific.

 

[Chad]

John, I came up with the code below and it works to a point! It shows the
empty controls in an message box but when I click the OK button to close the
Msg Box it brings up anothe Msg Box saying "The close action was canceled" I
then click Ok to close ect. Long story short it wont let me close untill all
fiels are filled. This is not what I want I want to be able to close the form
wether they are filled in or not. What am I missing im my code thats cxausing
this? Thanks!


Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
strMsg = "The following information must be entered..."

If IsNull(Me.txtTotalFootage) = 0 Then
strMsg = strMsg & vbCr & "Total Footage"
Cancel = True

End If
If IsNull(Me.txtMinutes) = 0 Then
strMsg = strMsg & vbCr & "Minutes"
Cancel = True

End If
If IsNull(Me.txtDTRegular) = 0 Then
strMsg = strMsg & vbCr & "Down Time Regular"
Cancel = True
End If

If Cancel = True Then
MsgBox strMsg
End If

End Sub

 

[DavidBoyle via AccessMonster.com]

You dond't say what things are messed up, but presumably reports or other
decision support information is incomplete or unavailable.

It would be helpful before accessing the "messed up" data to create a review
query which identifies the offending incomplete records. You could then base
a form on this and be able to identify the problem items and refer the
supervisors to them for update.

Build a new query in design view and then bring the information fields down
and enter "is null" in the criteria for the info you're interested. You will
have to enter each Is null criterion on a separate line.This will return all
the incomplete records in one view for editing.

 

[John Spencer]

If all you want is a warning message then eliminate the lines that read
Cancel = True and test the length of the strMsg

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

If IsNull(Me.txtTotalFootage) = 0 Then
strMsg = strMsg & vbCr & "Total Footage"
End If

If IsNull(Me.txtMinutes) = 0 Then
strMsg = strMsg & vbCr & "Minutes"
End If

If IsNull(Me.txtDTRegular) = 0 Then
strMsg = strMsg & vbCr & "Down Time Regular"

End If

If Len(StrMsg) > 0 Then
strMsg = "The following information must be entered..." & strMsg
MsgBox strMsg
End If

End Sub

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chad]

David, The code I came up with only works for the current record or last
record when I close the form so it wont be of any use to me. The query would
be the way to go but I really dont know how to go about doing it. I
understand the Is Null in the criteria and it will show all fields that are
empty is what I want but how do I get this to work in the forms on close to
check a query and if fields are null then give me a MsgBox telling me what
fields are null and closes the form. I just want this to inform the person
entering the info I dont want to restrict them from closing the form. How
would I go about doing this? Thanks!

 

[DavidBoyle via AccessMonster.com]

The first step is to create the query that identifies your exception records,
then in code you can do checks on that recordset using the dlookup function.

The best thing to do to get started with query design is to open up a new
select query in design view and experiment with using the Is Null criteria in
the grid.

Choose "Queries" in the object list on the left.

In the middle click new query.

Click "Design View" then OK.

Select the table that you wish to build the query on.

Click Add

Close the show table dialog

Drag the border of the table field list in the top left so that you can see
the contents and select the fields you want to work with and double click
them. If you bring one down by mistake right click it and then hit delete.

Each criteria line represents a different OR condition, so you want isnull
under each potential null value on seperate lines. The purple exclamation
button runs the query so you can experiment with data.

Once you have a query that shows the records with null values thenw e can
look at writing a dependent function which fires on your form close.

I'll check back soon for an update.

 

[Chad]

Ok query is done but one thing. I cant get it to show me null values for the
current day. I have a field named Date and In its criteria I used Date() and
it doesnt show me the current date it shows me all my null fields even from
the DB begining to now insted of just now. Any ideas?