check qry on form close for null entrys

[Chad]

Hello, I don’t know how to go about writing the Vba for this and needed some
assistance. I have a form called frmMainDB and its control source is tblMain.
On this form I have a few controls I would like to be checked when the form
closes and have a MsgBox display for an informative fetcher only. The problem
is I need it to display for the Date() or today so whatever records were
entered in for the current day then it will check only those records. I
thought using a query would be the way to go and just enter Is Null in the
criteria sections of each field and this would show all blank entries. What
would I do in the forms on close that could trigger the query and MsgBox?
Thanks!

 

[Klatuu]

If I understand your question, you want to check all records created today to
see if any of the required fields have Nulls before you allow the form to
close. If you find Null records, you want to present a message box, then
cancel the close and present the records will Nulls in the form.

Usually you do this before you allow the record to be created. That is done
in the Form Before Update event. It can be canceled by putting a line in
your code Cancel = True if any control is left NUll:

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.txtLastName) Or IsNull(.txtContactPhone) Then
MsgBox "Required Fields Missing"
Cancel = True
End If
End With

Now, if this is a case where you are not entering data, but are viewing
imported data and you want to be sure the use has corrected all records
before closing the form, use the form UnLoad Event. It can also be canceled
and the form will not close. Create a recordset that filters for records
with Null fields. Then open the recordset and see if there are any records
returned. If there are, then cancel the event and show the message:

Set rst = Currentdb.OpenRecordset("qselCheckNulls")
If rst.RecordCount <> 0 Then
MsgBox "Records with missing Fields Found"
Cancel = True
' Here you will filter your form for these records.
End If

 

[Chad]

Klatuu, You have most of it right! I want to be able to close the form and
show the MsgBox and not restrict the person from closing. Its just to let
them know there are blank records that isnt filled out. Now, I have tried
your first code before posting this and its showing or it will only show the
last record. I have several records for each day and I need to chek for nulls
for all those records as well in the current day. I tried setting up a query
that was related to the table of the form and using all the fields I want to
show as Null and in thier critera I put "Is Null" and in the date field I put
in its criteria "Date()" I then piurposily leave out or empty some of the
records then run the query and it gives me no results? I also put in my forms
on close event to give me the Msg box. What am I doing wrong? Thanks!

If DCount("*","My Query") > 0 Then
Msgbox "There's records with no data"
End If

 

[Klatuu]

The first code I posted is for checking the current record, not for looking
at all records.
If you are using the query builder, don't put Null for each field in the
criteria on the same line. That creates an And condition which means all the
fields have to be null. Put each Null criteria on a different line. That
creates an Or condition, so any one Null field will cause it to be included
in the result:

Field1 Field2 Field3

Null
Null
Null

 

[Chad]

OMG! I didnt know that... One more thing, I cant fit any more than 9 criteria
rows down and I have 12 fields I need to add the Is Null to the criteria? How
do I get more than 9 down? Thanks!

 

[Chad]

I found it! insert-->Rows gives me another row. I cant get it to give me
results for the current date. I have a date for each record and the field
name is txtDaysdate. I used this in my query as one of the fields and in the
criteria I just put Date() but it still gives me dates from way back. any
sugestions? Thanks!

 

[Klatuu]

You have to put the Date() on all Criteria rows. Remember, everything on one
row is an AND and on different rows it is an OR. So to include the Date as
an AND for each OR condition, it needs Date on each row.