open form if no records in query

[MiaAccess]

When leaving a form as part of the exit button code, I want to test if there are records and a particular field is empty.



I created a query and if there are records, a form created from the query should open and show the missing items; however, if the query has no records, it should just continue on. This is for right now my easy way out until I can figure out why my record validation doesn't work.

[pkstormy]

You could also write a simple function to return a true or false if there are records in the table.

ex (using ADO and passing a value to the function):

Function retHasRecords(SomeValue as variant) as boolean
retHasRecords = false
dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "Select * from MyTableName where SomeFieldName = '" & SomeValue & "'"
rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
if rs.eof and rs.bof then
retHasRecords = false
else
retHasRecords = true
end if
rs.close
set rs = nothing
end Function


Save this function in a module to call it from any form. You could also write a simple function to test your validation and return true or false depending on what the function checks. This is much better than putting validation into the table design if that is how you setup your validation.