Pass result of query to VBA as boolean

[JC]

I could use some direction:
I need to validate some form data before modifying several tables. To
validate the data I have to:
Select records based on form data
Perform some simple math
See if the result is negative
I have a query that does this and would like to pass the result back to VBA
as a Boolean. Then VBA will check the Boolean and if the data does not
validate use MsgBox to tell the user how to handle it.

Everything works but passing the result.

Suggestions?

 

[Lord Kelvan]

you could dao

dim db as database
dim rst as dao.recordset

set db = currentdb()
set rst = db.openrecordset("querycontainingnegtivevalue",
dbOpenSnapshot)

if rst!fieldcontainingvaluetocheck < 0 then
'execute code here
else
msgbox "have user handle it"
end if

 

[Ken Sheridan]

Presumably the query returns one row only. In which case you can use the
DLookup function:

Dim blnIsNegative as Boolean

blnIsNegative = (DLookup("YourField","YourQuery") < 0)

If you were to use a recordset, as it sounds like the query references
controls on the form as parameters, you'd need to evaluate the parameters
like so:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim blnIsNegative as Boolean

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("YourQuery")

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset

blnIsNegative = (rst.Fields("YourField") < 0)

However, calling the DLookup function is far simpler.

Ken Sheridan
Stafford, England

 

[JC]

Thanks for the suggestions. Perhaps I should explain further because I
believe the answer is not complex but continues to escape me:

The form data is used to pass data to a query. I have a command button that
is clicked to initiate this process. The query selects a group of records,
multiplies a number from the form times a field in each record, then compares
it to another field in that record. If all the results are positive or zero
(one result for entire group of records) I simply want to pass that result
back to VBA where I can use it to decide if I can continue with the rest of
the process which involves three more queries. Everything in the query does
what it should except the go/nogo result.

Any suggestions would be appreciated

 

[Ken Sheridan]

I'm not sure I completely follow. Are you saying that you need to determine
if the expression (FieldA *[Parameter]) – FieldB evaluates to a negative
value for ANY row returned by the query? If so then you can still use the
DLookup function but in a slightly different way, by testing for a Not Null
return value, e.g.

Dim blnIsNegative as Boolean

blnIsNegative = Not IsNull(DLookup("YourField","YourQuery", "YourField
< 0"))

where YourField is a computed column in the query based on the above
expression. The blnIsNegative variable will be True if at least one row
returned by the query has a negative value in the YourField column, False
otherwise.

Ken Sheridan
Stafford, England

 

[JC]

Ken,
Correct. I'll look into DLookup to see if it will do the job for me.
Thanks!