How to use cursor in select statement ?

[Mac]

I have following statement.

SELECT inv.PARTNO, inv.DESC from INV where inv.partno = me.partno.value

I want to save this part no and desc so I can use it while inserting to the
table.

In SQL, I use INTO CURSOR XYZ and I can retreive my part number like this:

SELECT inv.PARTNO, inv.DESC from INV where inv.partno = me.partno.value
INTO CURSOR XYZ

And xyz.partno usual give me part number.

How do I do this in Access, VB or VB.NET ?

Thanks

 

[Douglas J. Steele]

You create a recordset, and loop through the recordset.

In Access, my recommendation is to use DAO:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT inv.PARTNO, inv.DESC from INV where inv.partno = " &
me.partno

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
If rsCurr.EOF = False Then
' You'd refer to rsCurr!PARTNO and rsCurr!DESC
' (note the use of !, not .)
End If
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

Actually, in this case you don't even need a recordset if all you're trying
to do is get the description where the partno is the given value. The
DLookup aggregate function should be sufficient:

strDesc = Nz(DLookup("DESC", "INV", "inv.partno = " & me.partno), "Not
found")


Note that I've assume that partno is a numeric field. If it's text, you need
quotes around the value you're looking up:

strSQL = "SELECT inv.PARTNO, inv.DESC from INV where inv.partno = '" &
me.partno & "'"