Field Value From The Recordset

[trystanhuwwilliams]

Hi i wonder if anyone would know this? I want to get a field value from the recordset. I've got a date field in the table xflag which is tested for the current date at the opening of the switchboard. If the test doesn't find today's date as the last record then emails are sent out otherwise no action is taken.

I realise the best way to do this is using the recordset, but how do you get a variable to return the value of the last record entered??

This is what I've got so far..

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("XFLAG")
With RS
.MoveFirst
.MoveLast
End With
[\code]

Thank you,

T

 

[JeremyNYC]

you'll have to dim a variable first, to the correct type. If you're field is text, do this:
dim strWhatever as string
at the top of the routine (with the other dim statements (placement is only about readable code, not a requirement for it to run)).

Then you can refer to the value in the recordset a number of ways, but probably the clearest is this:
rs("FieldName&quot.

Putting that together with what you've got, and getting rid of the with structure, just cause right now there's no real need for it, here's what your code might look like:

Dim RS As DAO.Recordset
Dim DB As DAO.Database
dim strFieldName as string

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("XFLAG&quot
rs.movelast
strfieldname = rs("FieldName&quot

Then you'll be able to do whatever you want with that value. I didn't capitalize anything except for the declaration of the variable name. That way, when you paste this into your module, you can see if the spelling is correct, because if it is, Access will capitalize everything. Oh, and I capitalized the bit in quotes, as Access won't muss with stuff in quotes.

Jeremy
=============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[trystanhuwwilliams]

Thank you kindly for your very quick & excellent response, hope you have a pleasant weekend,

Cheers T

 

[sshowers]

Couldn't you just test:

If RS![DateField] <> Date() Then

End If

or did I miss something?

Hope this helps,
sshowers

 

[JeremyNYC]

Hmm. I certainly didn't read the original post very closely. A few things here:
-If you only need one field from the table, you should not grab the whole table.
-Records in a table are not stored in any particular order, so &quot;the last record in the table&quot; doesn't really mean anything.

Here's how I'd rewrite this

Dim DB as dao.database
dim rst as dao.recordset
dim strSql as string

set db = currentdb
strsql = &quot;SELECT Max(DateField) as MaxDate FROM XFlag&quot;
set rst = db.openrecordset(strsql, dbopensnapshot)
if rst.recordcount > 0 then
if rst(&quot;MaxDate&quot > Date() then
Call SendTheEmail
end if
end if


rst.close
set rst = nothing
db.close
set db = nothing

===========

SendTheEmail, of course, is just a name I made up for a sub or function that send the email.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.