Lookup last record and its contents in a particula (2000)

**Lstclair55** · 2006-03-20 13:30

|Is there a way to look at the last record in a table too sere the contents of a particular field. i will then use this to perform an action is they are not the same.

**HansV** · 2006-03-20 13:33

Do you mean the *last* record or the *previous* record?

**Lstclair55** · 2006-03-20 13:38

The lst record where StPropertyId = PropertyID

**Lstclair55** · 2006-03-20 13:39

The last record where StPropertyID = PropertyID

I want to be able to check if the boardstatus = stnewboardstatus on that record and if boardtag = stnewboardtag

**HansV** · 2006-03-20 13:42

And how is "last" defined? Can we use a date/time field for this, or an autonumber field?

**Lstclair55** · 2006-03-20 13:46

autonumber field.

**HansV** · 2006-03-20 13:47

What is the name of the AutoNumber field?

**Lstclair55** · 2006-03-20 13:50

STID

**HansV** · 2006-03-20 14:36

One way is to use DMax and DLookup. If I interpret your info correctly:

Dim maxSTID
Dim oldBoardStatus
Dim oldBoardTag
maxSTID = DMax("STID", "Name of table", "PropertyID = " & stPropertyID)
oldBoardStatus = DLookup("BoardStatus", "Name of table", "STID = " & maxSTID)
oldBoardTag = DLookup("BoardTag", "Name of table", "STID = " & maxSTID)

**Lstclair55** · 2006-03-20 16:41

How do I do the following:

If BoardStatus = oldBoardStatus & BoardTag <> oldBoardTag Then

I want to be able to say:

If the Boardtstaus box on the form is the same as and the boardtag field is not the same as oldboard tag then to create a new record.

However the current method still creates a new record regardless.

**HansV** · 2006-03-20 16:49

Please provide more information. Where do you have this code? In the On Click event procedure of a command button, or in an event procedure of the form, or ...?

**Lstclair55** · 2006-03-20 16:53

It is in the onclick event on a form frmProperty

**HansV** · 2006-03-20 17:08

Do you really mean the On Click event of the form itself? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

**Lstclair55** · 2006-03-21 07:34

the onclick event of a command button, sorry

**HansV** · 2006-03-21 07:43

Something like this?

Private Sub cmdWhatever_Click()
Dim maxSTID
Dim oldBoardStatus
Dim oldBoardTag
maxSTID = DMax("STID", "Name of table", "PropertyID = " & stPropertyID)
oldBoardStatus = DLookup("BoardStatus", "Name of table", "STID = " & maxSTID)
oldBoardTag = DLookup("BoardTag", "Name of table", "STID = " & maxSTID)
If Me.BoardStatus = oldBoardStatus and Me.BoardTag <> oldBoardTag Then
RunCommand acCmdRecordsGoToNew
End If
End Sub

Thread: Lookup last record and its contents in a particula (2000)

Thread Tools