Getting a value from a query into Visual Basic code

[Jamil Afza]

I am trying to take a value from a query (which is a calculation of averaging
four numbers) and be able to use it for another more complex calculation in
Visual Basic. I am making a module for the calculation and intend to embed it
into the form where the result of the query is already displayed.

The VB code will be a loop which will contain an equation that is iterated
until it generates a value smaller than the critical value. Upon each
iteration value will be added to by one until the critical value is reached,
the value of N, which results in a value less than the critical value, will
be displayed. "p" is the value which will be to be pulled from the query.

This is the equation
((1-p)^N))*p < .000943



A little background on the database, in case it helps. The main table is a
list of workers, the database is meant to keep track of how many orders the
workers picked per week and how many errors they made (in a separate table).
The query takes the most recent four weeks of data and calculates the average
errors per order. This is the value which is needed for the more complex
calculation. The user of the database will pick the workers name from a drop
down box and a form will then come up with the result of the query (error per
order) and the result of the VB code (the N value).

 

[xRoachx]

Great post but do you have a specific question or are you looking for someone
to write the module?

For example, have you started your code and have run into an error or are do
you need help getting started?

 

[Jamil Afza]

I am not looking for someone to write the module for me, I can do the VB
code, I just want to know how I can pull the value from the query and
incorporate it into the VB code. Thanks.

 

[Douglas J. Steele]

You can use DLookup, or you can open a recordset and retrieve the value that
way.

The code to do the latter using DAO would be something like:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim lngNumericValue As Long

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyQuery")
Set rsCurr = qdfCurr.OpenRecordset
If rsCurr.EOF = False Then
lngNumericValue = rsCurr!MyNumericField
End If
rsCurr.Close
Set rsCurr = Nothing
Set qdfCurr = Nothing
Set dbCurr = Nothing


This assumes that your query is named MyQuery, and that you want the value
of a field named MyNumericField in that query. Change as appropriate.