Populating Forms with previous record info

[Ice Rhino]

I am designing a form that is used daily to check the status of various different servers. This kind of info includes hard disk usage, connectivity etc etc.

The form is text & check box based. I have 15 fields that the user is to type in the values of the current hard disk space remaining on various drives.

For the purposes of this question, assume that one of the servers is called Atlantis. So three of the fields to be filled in would be Atlantis C, Atlantis D and Atlantis E

What I want to do is this. When the user opens the form, I want it to display the last record entered into the database next to the empty (Today's) value.

For Example

Drive Name Today Previous
Atlantis C 0.00 18.4
Atlantis D 0.00 6.4
Atlantis E 0.00 17.6


The 'Previous' Value will be displayed in a text box that I can format with colour and font etc

As much help with this as possible would be great.

Many Thanks in anticipation

Toni Chaffin

 

[Ice Rhino]

I have just noticed that even though I spaced the example correctly in the message when created, it has trashed the spacing completely.

Basically there are three columns, Drive Name, Todays value and Previous Value

All the Best

Toni

 

[everblue]

Use a little VB code in the Form_load()

Ice Rhino,

Put three lables naming A, B, C in front of the Today's Text Boxes and write the following Code in the Form_load() event.

Private Sub Form_Load()

dim db as DAO.Database
dim rs as DAO.recordset

set db=currentdb()

set rs=db.openrecordset("SELECT Last(TableName.First_Field) AS Atlantis A, Last(TableName.Second_Field) AS Atlantis B, Last(TableName.Third_Field) AS Atlantis C FROM TableName;")

me.A.SetFocus
me.A.Caption=rs.Fields(0).value

me.B.SetFocus
me.B.Caption=rs.Fields(1).value

me.C.SetFocus
me.C.Caption=rs.Fields(2).value

End Sub


One thing you need to do is, while you are in the VB code editor, Click on Tools -> Refrences
Scroll Down to find (Microsoft DAO 3.6 Object Libaray) and check the check box and click OK.

Remember to replace the <TableName> in the Set rs=... statment to your table name and <First_Field>...and so on to the Names of the columns in that Tables.

Thanx

 

[Ice Rhino]

Thanks Everblue, I will try that now

All the Best

Toni

 

[Rich]

Look up the PrevRecVal function

 

[Ice Rhino]

With a few changes and deletions from your example it works great.

Thanks

Toni