Pass result of query to VBA as boolean

  • Thread starter Thread starter JC
  • Start date Start date
J

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?
 
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
 
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
 
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
 
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
 
Back
Top