asked on

Fetch value returned from query in vba

I have a query that only returns one field, one record. The record returned is a numeric score from a static table which acts as a scoresheet. All I need to know how to do is to get the returned record from the query using vba so I can then paste this number into a field in one of my tables. Without getting into too much detail, what happens is this, I have a table with number of situps and age groups as fields. I supply the number of situps and the age of the person, and the query shows me a score. I need to take that score and update another table with it. I want to do all of this using vba. Below is a little more description

I have a table that stores scores for sit up repetitions based on age.. here is what it looks like....

Repetitions age 18-22 age 23-26 age 27-32
30 10 9 7
31 10 10 7 <-----this number is the score for repetitions done
32 11 10 8
33 12 11 9

When I type in 31, I want the vba to grab the person's age (i know how to do this part), and put thier score into a field of another table. If I knew how to make a string using query results I could do this on my own, but I have no idea how to access query results using vba.

please excuse my sloppiness, i'm finishing up this question right before I pass out for the night ;)
Thanks,
Chris

rockiroads

You can create a recordset (does this query have a join, otherwise just use DLookup) then get that value and create some SQL which will update your tabe

i.e.

iGetValue = .... (from recordset or dlookup depending on your query)

then

sSql = "UPDATE <table> SET <field> = " & iGetValue

DoCmd.RunSQL sSQL

(above sample uses DAO, which you reference within Modules going into Tools/Reference)

Benjamin Lu

You can use recordset to refer the result.

Dim rs as recordset, myValue
set rs=currentdb.OpenRecordset("Select * From mySQL")
myvalue=rs.Fields(0)
set rs=nothing

Best Regards

Ben

nico5038

Hi Chris,

Your score table isn't "normalized", thus making it hard to get the data from the field with the Age.
Can you change the table to hold:
Repetitions
AgeFrom
AgeTo
Score

This will allow you to extract the Score with a query or DLOOKUP like:
=DLOOKUP("Score","tblScore","[Agefrom]<=" & Me.Age & " AND [AgeTo]>=" & Me.Age & " AND Repetitions=" & Me.Repetitions)

This assumes that you have an age field and a repetitions field on the form, but I guess you get the idea.

Nic;o)

rockiroads

Further explanation:

If your query is fetching data from one table only then use DLookup

e.g.
sValue = DLookup("field","table","where clause minus the WHERE word")

In fact, you can actually build your update based on this query since its just one table

if multiple tables then use a join

Dim qdQryDef As QueryDef 'Querydef used to create recordset
Dim rsRecSet As DAO.Recordset 'Recordset used to read data

sSql = "SELECT .... "
Set qdQryDef = CurrentDb.CreateQueryDef("", sSql)
Set rsRecSet = qdQryDef.OpenRecordset()

'If not EOF
if rsRecSet.EOF = False then
dValue = rsRecSet.Fields("fieldname")
sSql = "UPDATE myTable SET myField = " & dValue
DoCmd.RunSQL sSql
endif

rsRecSet.Close
qdQryDef.Close

Benjamin Lu

myValue is the value from your query

Best Regards

Ben

SOOPERDAD

ASKER

Nic,
I'd see what you mean, but I have two issues with changing the tables (there are other tables set up this way with age groups), first, I'd need to be able to paste new data into the tables when they are updated, and second, I need to learn how to decide which field to look in, based on the person's age, which will be in another table. I am going to post that question right now.

Once I figure this thing out I'll accept an answer and assign points, sorry this is taking me longer than usual, but I have never seen the DLookup() function before, nor have I ever needed to do anything like what I'm doing here. Thank you all for your help so far.

Chris

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOOPERDAD

ASKER

There has got to be a better way to learn this stuff,, my brain hurts. :-P

SOOPERDAD

ASKER

Nic,
Could i get your opinion on this....
https://www.experts-exchange.com/questions/20936576/Declare-an-array-Help-with-VBA-code.html

Thanks
Chris

nico5038

OK, I'll take this solution to your new question so we can combine the comments from all experts :-)

Nic;o)

SOOPERDAD

ASKER

Im going to accept Nic's answer, but I hate to be limited to giving him the 500 points, so i'm going to post point for rockiroads and Benjamin Luk for the help both of you provided.
Although I still don't fully grasp all of this, I will study it until I do. (I think taking a break from the database to play Morrowind has turned my brain into jelly...)

Thank you all,

Chris

nico5038

Always glad to see all experts are appreciated, thanks :-)

Nic;o)

SOOPERDAD

ASKER

rocki,
i think you have to post here, yes?
https://www.experts-exchange.com/questions/20936604/Points-for-rockiroads.html
then i can give points?

Chris

SOOPERDAD

ASKER

and one for you Benjamin,
https://www.experts-exchange.com/questions/20936605/Points-for-Benjamin-Luk.html
thanks

Chris