running SQL from VB

[kmo]

How can I run a sql select query from VB. where do i store the results and how can i access or display only the first value of the select query result??

i want to do something like this.

function getPoints(stuActivity as String) As Integer

​

Dim Points As Integer

​

Points = Docmd Runsql "SELECT Points FROM tblActivity WHERE Activity=stuActivity"

​

getPoints=Points

end function

Please do le me know the correct code to do the above.

 

[richary]

You can use the DLookup function if you just need to return a single value.

Based on your select query, the equiv DLookup is

Points = Dlookup("[Points]","tblActivity","Activity = '" & stuActivity  & "'")

Note (and I could be wrong in this, I haven't had a chance to look), but this returns the value of Points in first record return (which is what you say that you want). But Access does not guarentee which record will be the first returned when more than one record matches a SELECT query. Usually it is the first entered, but it is not 100% certain that will always occur under all circumstances.

If this is important to you, I would add additional crietria or perhaps look at the DMIN function and base it on an ascending field (perhaps the PK or a date field)