Solved

Fetch value returned from query in vba

Posted on 2004-03-29
14
428 Views
Last Modified: 2011-09-20
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
Comment
Question by:SOOPERDAD
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 8

Expert Comment

by:Benjamin_Luk
Comment Utility
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
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 8

Expert Comment

by:Benjamin_Luk
Comment Utility
myValue is the value from your query

Best Regards

Ben
0
 

Author Comment

by:SOOPERDAD
Comment Utility
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:SOOPERDAD
Comment Utility
There has got to be a better way to learn this stuff,, my brain hurts. :-P
0
 

Author Comment

by:SOOPERDAD
Comment Utility
Nic,
Could i get your opinion on this....
http://www.experts-exchange.com/Databases/MS_Access/Q_20936576.html

Thanks
Chris
0
 
LVL 54

Expert Comment

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

Nic;o)
0
 

Author Comment

by:SOOPERDAD
Comment Utility
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
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Always glad to see all experts are appreciated, thanks :-)

Nic;o)
0
 

Author Comment

by:SOOPERDAD
Comment Utility
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
 

Author Comment

by:SOOPERDAD
Comment Utility
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now