Data Valadation

[Jeff Garrison]

All -

I'm working on an error routine for one of my tables. What I'd like to be
able to do is to click a button and if one of the fields in the table
contains a null value, pop up a message box stating what record number
containes the null value....or better yet, a summary after all records have
been processed.

Example

ID Division City State
1176 301-0265 Detroit MI
1177 301-0270 Los Angeles CA
1178 Atlanta GA
1179 Tallahassee FL

I'd like the summary to be something like...

Valadation Errors
Missing Division
1178 Atlanta GA
1179 Tallahassee FL

If there are no errors, pop a message saying Data Valadation Succeeded.

Thanks.

Jeff

 

[Allen Browne]

Create a query that has the critiera you want, e.g.:
Is Null
under the relevant field(s.)
Save as (say) Query1.

Then put some code like this into the Click event procedure of your button:

If IsNull(DLookup("Division", "Query1") Then
DoCmd.OpenQuery "Query1"
Else
MsgBox "validation succeeded"
end If

If the goal is to ensure that the user can't save a record without the
Division entered in the first place, the easy solution would be to open the
table in design view, select the Division field, and set the Required
property to Yes (in the lower pane of table design.)

 

[Douglas J. Steele]

Create a query with an appropriate Where clause. For the case you've shown,
you could use something like

SELECT ID, "Missing Division" As Reason
FROM MyTable
WHERE Division IS NULL
UNION
SELECT ID, "Missing City" As Reason
FROM MyTable
WHERE City IS NULL
UNION
SELECT ID, "Missing State" As Reason
FROM MyTable
WHERE State IS NULL

(I'll assume you named the query qryValidate)

Create a form that uses that query as its RecordSource (I'll assume you
named the form frmValidate)

Add a button to your existing form. For the button's OnClick event, have
code like:

Private Sub MyButton_Click()

If DCount("*", "qryValidate") = 0 Then
MsgBox "Everything's great"
Else
DoCmd.OpenForm "frmValidate"
End If

End Sub

 

[Ryan]

You could create a query that filtered out all the null values. In the
Division field of the query you would add a criteria of Is Null. Then you
would create a "Continous Form" based on that query. This would not produce
a pop up, but it would produce a summary of all the records with null values
in the Division field and if there are no null values then there will be no
records shown.

 

[Jeff Garrison]

Allen

Thanks for the reply. Let me clarify a couple of things...

- The data that is going to be validated comes from a couple of different
tables and is written to a "temp" table before all of the data is being
written to a SQL table.
- As it stands now, a form opens and the user clicks a button to create the
"temp" table.
- I'd like to have the validation take place after the data has been written
to the temp table.
- I have about 20 fields I need to validate before the final data is written
to the SQL table. I'd like to do the validation as part of the process when
the user runs the final write to the SQL table.
- If any of those 20 fields come back having a Null, I'd like to show the
user what record has the null value(s).

Thanks.

Jeff

 

[Jeff Garrison]

Doug -

After some minor tweaking...I think this does what I need it to...

Thanks!

Jeff