Solved

Fetch value returned from query in vba

Posted on 2004-03-29
14
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10702500
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
ID: 10702515
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
ID: 10702516
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
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!

 
LVL 65

Expert Comment

by:rockiroads
ID: 10702520
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
ID: 10702522
myValue is the value from your query

Best Regards

Ben
0
 

Author Comment

by:SOOPERDAD
ID: 10708911
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
ID: 10708993
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
 

Author Comment

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

Author Comment

by:SOOPERDAD
ID: 10709090
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
ID: 10709101
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
ID: 10709161
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
ID: 10709208
Always glad to see all experts are appreciated, thanks :-)

Nic;o)
0
 

Author Comment

by:SOOPERDAD
ID: 10709348
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
ID: 10709354
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 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