How do I refer to a specific record in code? 3

[DCBBB]

Here's an easy one for you....How do I refer to a specific record in a table when in VB? For instance, I want to set a variable in code equal to the top seller in a product line (which can change daily). So, let's say I have a sales table (tblSales), I want my variable (TopSeller) to equal the product with the highest sales. I want to do something like this (which isn't working):

Set TopSeller = "SELECT TOP 1 tblSales.Product FROM tblSales ORDER BY tblSales.Sales DESC;"

I am a VB beginner, but I can usually hack my way through and am having trouble with this. Thanks!
D

 

[jasonp45]

You can't set a variable equal to a record; the SQL query will return a recordset.

If you want to get the entire *record* of the top seller, you can do it sort of the way you were trying, but if you want a *value* (like the name of the salesperson or whatever) then you could change your SQL around so that only that field gets returned...

Simplest way to approach this is with some code. Lets assume I have a table called 'MyTable'...

Dim rs as ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.Source = "Select Salesperson From [MyTable] Where SalesAmount = (Select Max(SalesAmount) From [MyTable])"
.Open
sTopSeller = .Fields("Salesperson&quot.Value
.Close
End With
Set rs = Nothing

Note that "Select Top 1" that you had in your SQL will return the record that is first *positionally* in the logical ordering of your data, and has nothing to do with values (unless of course you've sorted your table according to value already).

 

[930driver]

You need to add to your query the field that holds the numbers that you want the 'top' of. All you have is the product. You'll also need the sales person.

Also, you don't need an order by clause when you are only returning one record.

I assume you're using the sql string as the record source for a recordset in code. Once you have returned your one top record you refer to the record by setting your variable to the value of the sales person which you added as I suggested above.

I don't know what datatype sales person is, whether you are storing the actual name or a long integer id or what, so you will have to watch out for that.

dim rs as recordset
dim sql as string

sql = "SELECT TOP 1 Volume, SalesPerson FROM tblSales"
'replace volume and salesperson with the actual field
'names

set rs = currentdb.openrecordset sql
'make sure a record is returned
if rs.recordcount > 0 then 'we have a record
topseller = rs!SalesPerson
end if
rs.close
set rs = nothing "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[jebry]

Hi!

Try this code:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT TOP 1 tblSales.Product FROM tblSales ORDER
BY tblSales.Sales DESC;&quot

TopSeller = rst!Product

hth
Jeff Bridgham

 

[DCBBB]

Thanks all! Thanks to your help, I got my code working as perfectly. This is an Access lesson that will be a big help with many future projects. This forum rules!

D