Access 97 DB, RESOLVED

[Ricky1]

Ok i need to get the value for a field called "Total Profit" the last one entered into the database tho.

Access 97 DB
Using Data Control

My first time using Data Contorl so simplest way possible please.

[Ricky1]

So i need the value of Total Profit from the last row entered into the database.

[Ricky1]

i have this

With Data1.Recordset
    Data1.Recordset.MoveLast
    txtTotalProfit.Text = Data1.Recordset.Fields("Total Profit").Value
End With

but it gives me this error

object variable not set or with block variable not set

[Ricky1]

Some one must now this, im sure its simple.

Access DB using Data Control called Data1
VB 6.0

I need to navigate to the last row in the database, and then take the value of the field call "Total Profit"

[salvelinus]

What is the record source of the Data1? Records in a table aren't guaranteed to be in any specific order, so the last record in the table won't necessarily be the last one entered. The table would need a log field to record when entered, or you could try an autonumber field, and you'd have to sort on that.

[Ricky1]

if my case its a clean database so the last record entered into the table wil be the one i want. All i want to do it take the value of a field called "Total Profit" from the last row on the table.



my table does have an autonumber.

[Ricky1]

anything to point me in the right direction, i expected this to be simple

[salvelinus]

No, you can't assume the last record entered will be the last record in the table. It may be, but it may not. Records aren't automatically returned in any order. Doesn't matter how "clean" your database is. Unless it's got only one record, if you don't sort it somehow, you don't know for sure which record will come up.
Last Record, MoveLast, etc, has no meaning for your purposes as I understand them. You can get the last record in the table or recordset, but again, that's no guarantee it's the last record entered.
I'm sorry if this isn't clear. Try this. You enter data for record1, then record2, then record3.
Retrieving the records could give you record2, record1, record3.
Also, I don't like data binding things like DataControls, but that's a separate issue.

[Ricky1]

but all my records are entered in an Autonumber order, and the database keeps them in that order, so surely i must me able to arrange them in autonumber order therefore it would be the last record entered.

ignore the fact how i do it then, can you tell me how i retrieve data from my database ???

so txtTotalProfit displays a figure from the field called "Total Profit" then i can carry on from that.

Or anyone have any tuturials on Data Control all i can find is ADO and that looks more complicated.

[si_the_geek]

Believe it or not, your records are not in order.. Access just makes it look as if they are. The concept of a database is awkward difference to most programmers to start with - there is no idea of ordering at all within the data, there is just a "set" of data, which can be returned in any order the database engine feels like at the time (unless you use something like an Order By clause in an SQL statement).


ADO isn't much more complicated than data controls - and it makes things much easier as soon as you try to do anything even slightly 'complex' (like you are getting into now).

The ADO tutorial link in my signature is a good guide - it contains enough to get you going, and many of us can answer almost any questions you have.


As far as data controls go, the only "tutorials" I have seen are in beginners books (but presumably there are a few around the internet), and I have yet to find anyone who regularly answers questions about them on the forums.