[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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
0
SOOPERDAD
Asked:
SOOPERDAD
  • 6
  • 4
  • 2
  • +1
1 Solution
 
rockiroadsCommented:
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)
0
 
Benjamin_LukCommented:
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
0
 
nico5038Commented:
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)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rockiroadsCommented:
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



0
 
Benjamin_LukCommented:
myValue is the value from your query

Best Regards

Ben
0
 
SOOPERDADAuthor Commented:
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


0
 
nico5038Commented:
When you don't want to change the table, then using a VBA function will be necessary to find the field needed.
Probably a "hardcoded" test can work, but will create a hassle when the fieldname is changed!

Just use something like:

Function fncGetScore(intRepetitions as integer, intAge as integer) as integer

dim rs as dao.recordset

set rs = currentdb.openrecordset("select * from tblRepetitions  where Repetitions=" & intRepetitions )
if rs.eof and rs.bof then
   ' no Repetitions found action
else
   select case intAge
   case between 18 and 22
       fncGetScore = rs![age 18-22]
   etc.
   end select

end function

Nic;o)
0
 
SOOPERDADAuthor Commented:
There has got to be a better way to learn this stuff,, my brain hurts. :-P
0
 
SOOPERDADAuthor Commented:
Nic,
Could i get your opinion on this....
http://www.experts-exchange.com/Databases/MS_Access/Q_20936576.html

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

Nic;o)
0
 
SOOPERDADAuthor Commented:
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
0
 
nico5038Commented:
Always glad to see all experts are appreciated, thanks :-)

Nic;o)
0
 
SOOPERDADAuthor Commented:
rocki,
i think you have to post here, yes?
http://www.experts-exchange.com/Databases/MS_Access/Q_20936604.html
then i can give points?

Chris
0
 
SOOPERDADAuthor Commented:
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now