Give me recordsets PLEASE!

[djh]

Hey folks.

I'm so new to VBA, that I'm in need of some simple - I'm 5 years old - type instructions.

I do know ASP, and so I'm familiar with how things should work, just not the syntax.

Anyway, I have a table from which I'd like to generate a recordset.

How do I create a recordset in VB and refer to the instantiated fields?

I took a crack:

Dim RS As recordset
Dim newPrice

Set RS = CurrentDb.openrecordset("temp")

Do While Not RS.EOF

    If CLng(RS(price)) > 999999 Then
    
        newPrice = (CLng(Trim(RS(price)))) / 1000000
        newPrice = CStr(newPrice) & "M"
        
        RS(price) = newPrice
        
    End If

Loop

 

[DALeffler]

How 'bout an example I think'll work for you?

Dim RS As recordset
Dim newPrice
Set RS = CurrentDb.openrecordset("temp")
Do While Not RS.EOF
    If CLng(RS!price) > 999999 Then
        newPrice = (CLng(Trim(RS!price))) / 1000000
        newPrice = CStr(newPrice) & "M"
       With RS
        .Edit
        !price = newprice
        .update
       End With
    End If
RS.MoveNext
Loop

There's a pointer for each record of the record set created that lets code get to each field in the record. Then the buffer for each record has to be edited & updated to change individual fields for that record. Moving the pointer sequentially through the record set will eventually cause the .EOF property of the record set to be true.

hth,

Doug.

[This message has been edited by DALeffler (edited 06-22-2001).]

 

[djh]

Great, thank you Doug. It's just like ADO, I see.

I have this code:

Dim db As Database
Dim RS as recordset
Dim strSQL as String

set db=currentdb
strSQL="SELECT price FROM temp;"
set RS=db.openRecordset(strSQL, dbOpenDynaset)

and I keep getting the error: "User defined type not defined" on the "Dim db as Database" line.. aaargh!

What's the proper way to call a table within the same database, and to create a recordset of all records?

Thanks again Doug...

[This message has been edited by djh (edited 06-25-2001).]

 

[Talismanic]

If this is Access 2000 and you are using DAO, you will want to declare thar in your dim statement like this:

Dim RS As DAO.Recordset

Did that work for you or did I misunderstand the problem you are having, post back to let me know.

 

[DALeffler]

Depends on what you're working with (A2K or '97) - I'm not at all familiar with ADO stuff - and I suspect you're working with A2K if you're getting that error on that dim statement...

Try this post for more help -
http://www.access-programmers.co.uk/ubb/Forum7/HTML/001619.html

Doug.

[This message has been edited by DALeffler (edited 06-25-2001).]

 

[djh]

Ok, I did:

"Dim rst As ADODB.recordset" and it worked!!

Thanks for the help everyone!